Skip to main content

Create, edit, or remove an auditing query

For more information, see Auditing queries.

Create an auditing query​
  1. Go to Output > Auditing Queries.
  2. Click Add.
  3. Enter a Query Name.
  4. Click Create.
  5. Enter your SQLite query in the SQL Query field. Use the Tables and Columns dropdowns to view and insert available tables and column names from the audit database. For example: sqlite SELECT strftime('%Y-%m-%d %H:%M:%S',om.DateTime) AS 'Creation date time', hr2ad.employee_id AS 'ID', hr2ad.first_name AS 'First name', hr2ad.last_name AS 'Last name', au.sAMAccountName, au.accountPassword FROM Objects AS o INNER JOIN ObjectMutations AS om ON om.ObjectID=o.ID INNER JOIN f_hr2ad AS hr2ad ON o.KeyValue=hr2ad.objectGUID INNER JOIN ( SELECT au.ObjectMutationID, MAX(CASE WHEN AttributeName = 'sAMAccountName' THEN ValueNew END) AS [sAMAccountName], MAX(CASE WHEN AttributeName = 'accountPassword' THEN ValueNew END) AS [accountPassword] FROM AttributeUpdates AS au GROUP BY au.ObjectMutationID ) AS au ON om.ID = au.ObjectMutationID WHERE datetime(om.DateTime) > datetime (@StartOfMonth) ### Tip Hold the Control key and click the Insert button next to the Tables dropdown to display possible values for the selected table in the Result pane. (This is equivalent to running a select * from TableName query.)
  6. Optional: If your query uses one or more parameters, go to the Parameters tab.
  7. Click Add.
  8. Enter a parameter Name. For example, StartOfMonth.
  9. For the Type, select either Input or Variable.
  10. If you select Input, specify a Data Type and enter a constant string, number, or Boolean for the Default Value.
  11. If you select Variable, select one of the available Variables for the Variable Name.
  12. Click Query to test your query. The results are returned in the Results pane of the Data tab.
  13. Click Save.
Edit an auditing query​
  1. Go to Output > Auditing Queries.
  2. Click Edit Auditing Query for the relevant auditing query.
  3. Continue by following the steps in Create an auditing query.
Remove an auditing query​
  1. Go to Output > Auditing Queries.
  2. Click Remove Auditing Query for the relevant auditing query.
  3. Click Yes.