Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

[HOWTO] Run a SQL Report for Different Password Reset Operations

Privileged Access Service ,  

30 March,20 at 05:45 PM

Question: How can I utilize basic SQL scripting in PAS for different password reset scenarios?

Answer: You can utilize certain reports in the "Report" Section in PAS for different Password Reset Scenario; This article outlines 4 different SQL scripts with 4 different real life auditing scenarios.

To start:
  1. login to the PAS portal
  2. Select "Report" on the left hand side
  3. Give a name for the report to track
  4. Select "Edit Script
  5. Enter the SQL code that best fits the situation for the situation
This is where the selection is

 1) List of accounts failed to reset its password during last 30 days 

SELECT
    Event.AccountName,
    Event.ComputerName,
    Event.WhenOccurred
FROM
   Event
WHERE
    Event.EventType = "Cloud.Server.LocalAccount.AdministrativeResetAccountPasswordFailure"
AND
    Event.WhenOccurred > Datefunc('now', -30)

 2) Number of accounts failed to reset its password during last 30 days

SELECT
    Count ( * )
FROM
    Event
WHERE
    Event.EventType = "Cloud.Server.LocalAccount.AdministrativeResetAccountPasswordFailure"
AND
   Event.WhenOccurred > Datefunc('now', -30)

3) List of Successful password resets/unlock during last 30 days 

SELECT
    Event.AccountName,
    Event.ComputerName,
    Event.EventType,
   Event.WhenOccurred
FROM
   Event
WHERE
  Event.EventType IN ("Cloud.Server.LocalAccount.AdministrativeResetAccountPassword", "Cloud.Server.LocalAccount.AdministrativeManualAccountUnlock", "Cloud.Server.LocalAccount.AdministrativeUnlockAccount")
AND
    Event.WhenOccurred > Datefunc('now', -30)

4) Number of Successful password resets/unlock during last 30 days

SELECT
    Count ( *  )
FROM
    Event
WHERE
    Event.EventType IN ("Cloud.Server.LocalAccount.AdministrativeResetAccountPassword", "Cloud.Server.LocalAccount.AdministrativeManualAccountUnlock", "Cloud.Server.LocalAccount.AdministrativeUnlockAccount")
AND
    Event.WhenOccurred > Datefunc('now', -30)


You can preview the report(s) and see if they fit what you need and then save and export the results. You can also build off of these reports. 
***CENTRIFY WILL NOT SUPPORT CUSTOM SCRIPTS***

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.