NIM

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.

    2021-07-13_13-00-21.png
  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:

    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.)

    2021-08-06_14-58-33.png
  6. Optional: If your query uses one or more parameters, go to the Parameters tab.

    1. Click Add.

    2. Enter a parameter Name.

      For example, StartOfMonth.

    3. For the Type, select either Input or Variable.

      1. If you select Input, specify a Data Type and enter a constant string, number, or Boolean for the Default Value.

      2. If you select Variable, select one of the available Variables for the Variable Name.

    2021-07-13_13-21-23.png
  7. Click Query to test your query. The results are returned in the Results pane of the Data tab.

    2021-07-13_13-19-59.png
  8. Click Save.

  1. Go to Output > Auditing Queries.

  2. Click edit-auditing-query.png Edit Auditing Query for the relevant auditing query.

    2021-07-19_14-00-02.png
  3. Continue by following the steps in Create an auditing query.

  1. Go to Output > Auditing Queries.

  2. Click remove-auditing-query.png Remove Auditing Query for the relevant auditing query.

    2021-07-19_14-00-02.png
  3. Click Yes.