Example audit queries
Below you will find some examples of audit queries that you can use with NIM no matter the systems you have connected.
Recent attribute changes in past 24 hours
SELECT
ST.SystemName 'System'
, OP.Name 'Operation'
, O.DisplayName 'Name'
, AU.AttributeName 'Attribute'
, AU.ValueOld 'Old'
, AU.ValueNew 'New'
, datetime(OM.DateTime, 'localtime') 'Operation DT'
, O.KeyValue 'ID'
FROM AttributeUpdates AU
INNER JOIN ObjectMutations OM ON OM.ID = AU.ObjectMutationID
INNER JOIN Operations OP ON OP.ID = OM.OperationID
INNER JOIN Activities AC ON AC.ID = OM.ActivityID
INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
INNER JOIN Objects O ON O.ID = OM.ObjectID
INNER JOIN SystemTables ST ON ST.ID = O.SystemTableID
WHERE datetime(OM.DateTime, 'localtime') > DATE(datetime(CURRENT_TIMESTAMP, 'localtime'), '-1 days')
ORDER BY OM.DateTime DESC
Search attribute changes by Key Column
<Key Column Value>
should be replaced with a value of a key for a record (e.g. objectGUID for AD, id for Google)
SELECT
ST.SystemName 'System'
, OP.Name 'Operation'
, O.DisplayName 'Name'
, AU.AttributeName 'Attribute'
, AU.ValueOld 'Old'
, AU.ValueNew 'New'
, datetime(OM.DateTime, 'localtime') 'Operation DT'
, O.KeyValue 'ID'
FROM AttributeUpdates AU
INNER JOIN ObjectMutations OM ON OM.ID = AU.ObjectMutationID
INNER JOIN Operations OP ON OP.ID = OM.OperationID
INNER JOIN Activities AC ON AC.ID = OM.ActivityID
INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
INNER JOIN Objects O ON O.ID = OM.ObjectID
INNER JOIN SystemTables ST ON ST.ID = O.SystemTableID
WHERE 1=1
AND O.KeyValue = '<Key Column Value>'
ORDER BY OM.DateTime DESC
Recent membership changes in Past 24 hours
SELECT
st.SystemName,
Op.Name 'Operation',
datetime(M.DateTime, 'localtime') AS 'Creation date time',
GrpObj.DisplayName 'GroupName',
GrpObj.KeyValue 'GroupKey',
MemObj.DisplayName 'MemberName',
MemObj.KeyValue 'MemberKey'
FROM
Memberships M
INNER JOIN Operations Op ON Op.ID = M.OperationID
INNER JOIN Objects GrpObj ON GrpObj.ID = M.GroupID
INNER JOIN SystemTables st ON st.ID = GrpObj.SystemTableID
INNER JOIN Objects MemObj ON MemObj.ID = M.MemberID
WHERE datetime(M.DateTime, 'localtime') > DATE(datetime(CURRENT_TIMESTAMP, 'localtime'), '-1 days')