NIM

Go to Output > Auditing Queries.

Click Add.

Enter a Query Name.

Click Create.

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

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.

Click Query to test your query. The results are returned in the Results pane of the Data tab.

Click Save.