Skip to main content

NIM

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')
In this section: