CODEWITHSUNDEEP
databasereminderschange-passwordgeneos
Ks Swetha
Ks Swetha

Reputation: 11

Geneos - Password reset reminder for database accounts

Is there a way to send a reminder (email alert) in Geneos which will remind a user 10/15 days before the expiry to avoid a password expiry issue.

I need reminder sent 10 days before the password expires. I configured the DB account to expire after every 60 days.

Thanks.

Upvotes: 1

Views: 366

Answers (3)

HeXDeMoN
HeXDeMoN

Reputation: 58

First thing you need to do is you need to create a sampler that can query your DB to get account information you want to monitor. You should just use the built-in SQL-Toolkit. Please see the following site for more information on how to set it up. https://resources.itrsgroup.com/Netprobe/database/sql-toolkit.html

Example below will work with SQL Server

  • It will list every account
  • Days until that account expires
  • if the password is currently expired
  • if the account is currently locked
  • When the account was locked out if it was

  • Date the password was last set

    select name, isnull(loginproperty(name,'DaysUntilExpiration'),'NA') DaysUntilExpiration,
isnull(loginproperty(name,'IsExpired'),'NA') IsExpired,
isnull(loginproperty(name,'IsLocked'),'NA') IsLocked,
isnull(loginproperty(name,'LockoutTime'),'NA') LockoutTime,
isnull(loginproperty(name,'PasswordLastSetTime'),'NA') PasswordLastSetTime 
from sys.server_principals
where type='S'
union 
select name, isnull(loginproperty(name,'DaysUntilExpiration'),'NA') DaysUntilExpiration,
isnull(loginproperty(name,'IsExpired'),'NA') IsExpired,
isnull(loginproperty(name,'IsLocked'),'NA') IsLocked,
isnull(loginproperty(name,'LockoutTime'),'NA') LockoutTime,
isnull(loginproperty(name,'PasswordLastSetTime'),'NA') PasswordLastSetTime 
from sys.database_principals
where type='S'

Now that you got the information you need (Days until Password is Expired), you now need to setup a rule that will kick off an email when its 10 days until it expires. For more information on rules please see: https://resources.itrsgroup.com/none/geneos/Documentation/Gateway2/reference_guide/index.html#gw2-refguide-section-11

An example rule you could use is the following:

  • This will create a custom subject line that lists the account and has a comment on what action to take to resolve.

    set $(subject) concat("SQL Account - ", target "rowName", ": Is set to expire in 10 days")
set $(comment1) "Please set a new password"
if value < 11 then
  userdata "EMAILS" "[email protected]"
  userdata "SUBJECT" $(subject)
  userdata "LONG_COMMENT" $(comment1)
  severity critical
  run "EmailAlert"
else
  severity ok
endif

Upvotes: 0

nobody
nobody

Reputation: 23

You can use a sql-toolkit sampler and query the next query to retrieve the account status of all users: (you can filter users using WHERE profile = 'USER'; or something like that)

select username, account_status, lock_date, expiry_date from dba_users;

After that, you can create a simple checking rule for the expiry_date column in your Rules folder.

Upvotes: 0

Mohammad Nadeem
Mohammad Nadeem

Reputation: 9392

I don't think there is any plug-in availalable geneos to do this. An alternate would be to write a powershell script to populate user information. You can follow below steps:

  • Write a powershell script using Get-ADUser cmdlet to fetch password expiry information. The script should produce csv format output.
  • Add a sampler in Geneos and use toolkit plug-in to call the powershell script created in first step.
  • Add rule in Geneos on Password expiry column.

Upvotes: 0

Related Questions