Geneos - Password reset reminder for database accounts

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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" "User@domain.com"
    userdata "SUBJECT" $(subject)
    userdata "LONG_COMMENT" $(comment1)
    severity critical
    run "EmailAlert"
  else
    severity ok
  endif