Skip to main content

Audit identity changes with queries

Identity audit trail

Query recorded target-system changes for audit reports, notifications, and operational review.

NIM records target-system operations in its SQLite audit database at C:\ProgramData\Tools4ever\NIM\sysdata\nimDb.db. You can use audit-query results in notification templates or explore the database with DB Browser for SQLite.

What NIM recordsDirect link to What NIM records

NIM records target changes made by jobs, scheduled tasks, and applications: timestamps, initiating activity, affected systems and objects, attribute old/new values, and group-membership changes.

info

Mapping actions launched manually from a mapping’s Run tab are not written to the audit database or back to Vault data.

To include source-system data in a query, create a filter that joins source and target data. NIM exposes filters as SQL tables prefixed with f_.

Use audit results outside the query editor

Use a multi-export task to export audit-query data, or build a NIM App that displays and lets users explore the results. You can also include a focused query result in a notification template. Keep email results small: a mail server or email filter may block a message containing a large dataset.

Create an auditing queryDirect link to Create an auditing query

Select the audit dataDirect link to Select the audit data

  1. Go to Output > Auditing Queries, select Add, enter a query name, and select Create.
  2. Write SQLite in SQL Query. Use the Tables and Columns selectors to discover available audit fields.
  3. Hold Ctrl while selecting Insert beside a table to preview its values in the result pane.
Step 1 of 3

Find the tables you needDirect link to Find the tables you need

Choose the event you want to report. Each card links to matching examples in the query catalog below. The audit database schema shows the full relationship diagram and the columns in all 26 core tables.

Target attributesWhat changed on an object?

Start at ObjectMutations, then join AttributeUpdates, Objects, and Operations.

Browse attribute queries →Group membershipWho was added or removed?

Start at Memberships. Join Objects twice: once for the group and once for the member.

Browse membership queries →App useWhich app activity should I review?

Start at AppSessions, then join Sessions, Apps, and optionally AppResults.

Browse app queries →NIM resourcesWho changed a resource?

Start at ResourceUpdates, then join Sessions, Users, ResourceTypes, and Operations.

Browse resource queries →

Use the schema relationship map when a query needs more than one path. The query library below groups examples by outcome; each example starts collapsed and can be expanded for SQL and parameters.

Example QueriesDirect link to Example Queries

Each example starts collapsed; expand it to view its SQL and parameter names, types, and defaults.

The supplied examples are grouped by topic. Query SQL and parameter definitions are shown when you expand an example.

Overview and lookup queries (5)

Actors SummaryNo parameters

No parameters.

WITH AttributeUpdateCounts AS (
    SELECT 
        AC.UserID,
        COUNT(*) AS AttributeUpdateCount
    FROM ObjectMutations OM
    LEFT JOIN AttributeUpdates AU ON OM.ID = AU.ObjectMutationID
    INNER JOIN Activities AC ON AC.ID = OM.ActivityID
    GROUP BY AC.UserID
),
MembershipUpdateCounts AS (
    SELECT 
        AC.UserID,
        COUNT(*) AS MembershipUpdateCount
    FROM Memberships M
    INNER JOIN Activities AC ON AC.ID = M.ActivityID
    GROUP BY AC.UserID
),
SessionCounts AS (
  SELECT 
    UserID,
    COUNT(*) AS SessionCount
    FROM `Sessions`
    GROUP BY UserID
),
AppUsageCounts AS (
    SELECT  
      usr.ID AS 'UserID',
      COUNT(DISTINCT aps.ID) AS 'AppCount'
  FROM AppSessions AS aps
  INNER JOIN Sessions AS ses ON aps.SessionID = ses.ID
  INNER JOIN Users AS usr ON ses.UserID = usr.ID
  INNER JOIN Apps ON aps.AppID = apps.ID
  GROUP BY usr.ID
),
ResourceUsageCounts AS (
    SELECT  
      usr.ID AS 'UserID',
      COUNT(DISTINCT ru.ResourceName) AS 'ResourceCount'
FROM
	ResourceUpdates AS ru
	INNER JOIN ResourceTypes AS rt ON ru.ResourceTypeID = rt.ID
	INNER JOIN Sessions AS ses ON ru.SessionID = ses.ID
	INNER JOIN Users AS usr ON ses.UserID = usr.ID
	INNER JOIN Operations AS op ON ru.OperationID = op.ID
    GROUP BY ses.UserID
)
SELECT 
    U.ID AS 'ID',
    U.Name AS 'Actor',
    COALESCE(AUC.AttributeUpdateCount, 0) AS 'Attribute Activity',
    COALESCE(MUC.MembershipUpdateCount, 0) AS 'Membership Activity',
    COALESCE(SUC.SessionCount, 0) AS 'Session Activity',
    COALESCE(AUUC.AppCount, 0) AS 'Apps Activity',
    COALESCE(RUC.ResourceCount, 0) AS 'Resource Activity'
FROM Users U
LEFT JOIN AttributeUpdateCounts AUC ON AUC.UserID = U.ID
LEFT JOIN MembershipUpdateCounts MUC ON MUC.UserID = U.ID
LEFT JOIN SessionCounts SUC ON SUC.UserID = U.ID
LEFT JOIN AppUsageCounts AUUC ON AUUC.UserID = U.ID
LEFT JOIN ResourceUsageCounts RUC ON RUC.UserID = U.ID
ORDER BY U.Name ASC
Object SummaryNo parameters

No parameters.

WITH AttributeUpdateCounts AS (
    SELECT OM.ObjectID, COUNT(*) AS AttributeUpdateCount
    FROM ObjectMutations OM
    JOIN AttributeUpdates AU ON AU.ObjectMutationID = OM.ID
    GROUP BY OM.ObjectID
),
MembershipUpdateCounts AS (
    SELECT M.MemberID, COUNT(*) AS MembershipUpdateCount
    FROM Memberships M
    INNER JOIN Operations Op ON Op.ID = M.OperationID
    INNER JOIN Activities AC ON AC.ID = M.ActivityID
    INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
    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
    GROUP BY M.MemberID
)
SELECT 
    O.ID,
    O.KeyValue AS 'Object Key Value',
    O.DisplayName AS 'Object Display Name',
    ST.SystemName AS 'System Name',
    COALESCE(AU.AttributeUpdateCount, 0) AS 'Attribute Update Count',
    COALESCE(MU.MembershipUpdateCount, 0) AS 'Membership Update Count'
FROM Objects O
JOIN SystemTables ST ON ST.ID = O.SystemTableID
LEFT JOIN AttributeUpdateCounts AU ON AU.ObjectID = O.ID
LEFT JOIN MembershipUpdateCounts MU ON MU.MemberID = O.ID
ORDER BY O.DisplayName ASC;
Operations Summary2 parameters
Parameters
  • $daysInPast (input · string · default: 30)
  • $system (input · string · default: *)
SELECT
      OP.Name AS 'Operation',
      COUNT(*) AS 'Operation Count'
FROM ObjectMutations OM
LEFT JOIN Operations OP ON OP.ID = OM.OperationID
LEFT JOIN Objects O ON O.ID = OM.ObjectID
LEFT JOIN SystemTables ST ON ST.ID = O.SystemTableID
WHERE 1=1
  AND datetime(OM.DateTime, 'localtime') > DATE(
        datetime(CURRENT_TIMESTAMP, 'localtime'),
        CONCAT('-',$daysInPast, ' days')
      )
  AND (
        $system = '*'
        OR ST.SystemName = $system
      )
GROUP BY
      OP.Name
ORDER BY
      COUNT(*) DESC;
Operations2 parameters
Parameters
  • $daysInPast (input · string · default: 1)
  • $system (input · string · default: *)
SELECT
      OP.Name AS 'Operation'
FROM ObjectMutations OM
LEFT JOIN Operations OP ON OP.ID = OM.OperationID
LEFT JOIN Objects O ON O.ID = OM.ObjectID
LEFT JOIN SystemTables ST ON ST.ID = O.SystemTableID
WHERE 1=1
  AND datetime(OM.DateTime, 'localtime') > DATE(
        datetime(CURRENT_TIMESTAMP, 'localtime'),
        CONCAT('-',$daysInPast, ' days')
      )
  AND (
        $system = '*'
        OR ST.SystemName = $system
      )
SystemsNo parameters

No parameters.

SELECT DISTINCT SystemName FROM `SystemTables`

Attribute changes (6)

Attribute SummaryNo parameters

No parameters.

WITH AttributeUpdateCounts AS (
    SELECT 
        ST.SystemName, 
        AU.AttributeName, 
        COUNT(*) AS AttributeUpdateCount
    FROM ObjectMutations OM
    LEFT JOIN AttributeUpdates AU ON OM.ID = AU.ObjectMutationID
    INNER JOIN Objects O ON O.ID = OM.ObjectID
    INNER JOIN SystemTables ST ON ST.ID = O.SystemTableID
    GROUP BY ST.SystemName, AU.AttributeName
)
SELECT 
    AUC.SystemName AS 'System',
    AUC.AttributeName AS 'Attribute',
    AUC.AttributeUpdateCount AS 'Attribute Update Count'
FROM AttributeUpdateCounts AUC
ORDER BY AUC.SystemName ASC, AUC.AttributeName ASC;
Attributes Changes for Actor1 parameter
Parameters
  • $Key1 (input · string)
SELECT  
    datetime(OM.DateTime, 'localtime') AS 'Operation DT',
    ST.SystemName AS 'System',
    OP.Name AS 'Operation',
    AT.Name AS 'Process',
    O.KeyValue AS 'Object Key Value',
    O.DisplayName AS 'Object Display Name',
    AU.AttributeName AS 'Attribute',
    AU.ValueOld AS 'Old',
    AU.ValueNew AS 'New'
FROM (
    SELECT ID, ActivityTypeID FROM Activities WHERE UserID = $Key1
) AC
INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
LEFT JOIN ObjectMutations OM ON OM.ActivityID = AC.ID
LEFT JOIN AttributeUpdates AU ON AU.ObjectMutationID = OM.ID
INNER JOIN Operations OP ON OP.ID = OM.OperationID
INNER JOIN Objects O ON O.ID = OM.ObjectID
INNER JOIN SystemTables ST ON ST.ID = O.SystemTableID
ORDER BY OM.DateTime DESC
Attributes Changes for Attribute2 parameters
Parameters
  • $Attribute (input · string)
  • $System (input · string)
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'
        , AT.Name 'Process'
        , U.Name 'Actor'
FROM ObjectMutations OM
LEFT JOIN AttributeUpdates AU 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
LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE 1=1
AND AU.AttributeName = $Attribute
AND ST.SystemName = $System
ORDER BY OM.DateTime DESC
Attributes Changes for Object3 parameters
Parameters
  • $objectID (input · string)
  • $dateRange (input · string · default: -365)
  • $operation (input · string · default: all)
SELECT  
	      datetime(OM.DateTime, 'localtime') 'Operation DT'	  
        , ST.SystemName 'System'
        , OP.Name 'Operation'
        , AT.Name 'Process'
        , U.Name 'Actor'
		, AU.AttributeName 'Attribute'
		, AU.ValueOld 'Old'
        , AU.ValueNew 'New'
FROM ObjectMutations OM
LEFT JOIN AttributeUpdates AU ON OM.ID = AU.ObjectMutationID
LEFT JOIN Operations OP ON OP.ID = OM.OperationID
LEFT JOIN Activities AC ON AC.ID = OM.ActivityID
LEFT JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
LEFT JOIN Objects O ON O.ID = OM.ObjectID
LEFT JOIN SystemTables ST ON ST.ID = O.SystemTableID
LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE 1=1
  AND O.ID = $objectID
  AND datetime(OM.DateTime, 'localtime') > DATE(datetime(CURRENT_TIMESTAMP, 'localtime'), CONCAT($dateRange, ' days'))
  AND ($operation = 'all' OR OP.Name = $operation)
ORDER BY OM.DateTime DESC
Recent attribute changes by operation and date range2 parameters
Parameters
  • $operation (input · string · default: update)
  • $dateRange (input · string · default: -7)
SELECT DISTINCT 
          datetime(OM.DateTime, 'localtime') 'Operation DT'
        , ST.SystemName 'System'
        , OP.Name 'Operation'
        , O.DisplayName 'Name'
        , O.KeyValue 'Key' 
        , O.ID 'ID'
        , AT.Name 'Process'
        , U.Name 'Actor'
FROM ObjectMutations OM
LEFT JOIN AttributeUpdates AU 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
LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE 1=1
  AND datetime(OM.DateTime, 'localtime') > DATE(datetime(CURRENT_TIMESTAMP, 'localtime'), CONCAT($dateRange, ' days'))
  AND OP.Name = $operation
ORDER BY OM.DateTime DESC
Attributes Recent Changes2 parameters
Parameters
  • $dateRange (input · string · default: -1)
  • $operation (input · string · default: all)
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' 
  , AT.Name 'Process'
  , U.Name 'Actor'
FROM ObjectMutations OM
LEFT JOIN AttributeUpdates AU 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
LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE 1=1
  AND datetime(OM.DateTime, 'localtime') > DATE(datetime(CURRENT_TIMESTAMP, 'localtime'), CONCAT($dateRange, ' days'))
  AND ($operation = 'all' OR OP.Name = $operation)
ORDER BY OM.DateTime DESC

Membership changes (4)

Memberships Changes for Actor1 parameter
Parameters
  • $Key1 (input · string)
SELECT
    datetime(M.DateTime, 'localtime') AS 'Operation DT',
    ST.SystemName,
    OP.Name AS 'Operation',
    AT.Name AS 'Process',
    U.Name AS 'Actor',
    GrpObj.KeyValue AS 'ResourceKey',
    GrpObj.DisplayName AS 'ResourceDisplayName',
    MemObj.KeyValue AS 'ObjectKey',
    MemObj.DisplayName AS 'ObjectDisplayName'
FROM (
    SELECT ID, ActivityTypeID FROM Activities WHERE UserID = $Key1
) AC
INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
INNER JOIN Memberships M ON M.ActivityID = AC.ID
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
LEFT JOIN Users U ON U.ID = AC.ID
ORDER BY datetime(M.DateTime, 'localtime') DESC
Memberships Changes for App Group1 parameter
Parameters
  • $AppName (input · string · default: Dashboard)
SELECT
	  datetime(M.DateTime, 'localtime') AS 'Operation DT'
    , st.SystemName
    , Op.Name 'Operation'
    , AT.Name 'Process'
    , U.Name 'Actor'
    , MemObj.KeyValue 'MembershipKey',
      MemObj.DisplayName 'MembershipDisplayName'
FROM
	Memberships M
    INNER JOIN Operations Op ON Op.ID = M.OperationID
    INNER JOIN Activities AC ON AC.ID = M.ActivityID
    INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
    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
    LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE GrpObj.`DisplayName` = ('nga_' || $AppName)
AND st.`SystemName` = 'internal'
ORDER BY datetime(M.DateTime, 'localtime') DESC
Memberships Changes for Object1 parameter
Parameters
  • $Key1 (input · string)
SELECT
	  datetime(M.DateTime, 'localtime') AS 'Operation DT'
    , st.SystemName
    , Op.Name 'Operation'
    , AT.Name 'Process'
    , U.Name 'Actor'
    , GrpObj.KeyValue 'MembershipKey',
      GrpObj.DisplayName 'MembershipDisplayName'
FROM
	Memberships M
    INNER JOIN Operations Op ON Op.ID = M.OperationID
    INNER JOIN Activities AC ON AC.ID = M.ActivityID
    INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
    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
    LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE MemObj.ID = $Key1
ORDER BY datetime(M.DateTime, 'localtime') DESC
Memberships Recent Changes1 parameter
Parameters
  • $dateRange (input · string · default: -1)
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',
    AT.Name 'Process',
    U.Name 'Actor'
FROM
	Memberships M
    INNER JOIN Operations Op ON Op.ID = M.OperationID
    INNER JOIN Activities AC ON AC.ID = M.ActivityID
    INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
    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
    LEFT JOIN Users AS U ON U.ID = AC.UserID
WHERE 1=1
    AND datetime(M.DateTime, 'localtime') > DATE(datetime(CURRENT_TIMESTAMP, 'localtime'), CONCAT($dateRange, ' days'))
ORDER BY datetime(M.DateTime, 'localtime') DESC

App activity and sessions (6)

Apps Activity Failures for App1 parameter
Parameters
  • $AppID (input · string)
-- Swiss-army knife for all app failures
SELECT
  STRFTIME('%F %T', COALESCE(apaac.StartDateTime, apa.StartDateTime, aps.StartDateTime) , 'localtime') 'DateTime',
  aps.ID 'AppSessionID',
  usr.Name 'UserName',
  ses.IpAddress,
  ses.UserAgent,
  app.Name 'AppName',
  apa.ID 'AppActivityID',
  afu.FormName,
  afu.UnitID,
  afu.UnitDescription,
  apaa.ActionFsRef,
  apaa.ActionSeqNr,
  apat.Name 'ActionType',
  apac.Name 'ActionName',
  apaac.CallSeqNr,
  apaac.CallDescription,
  COALESCE(apaac_apec.Error, apa_apec.Error, aps_apec.Error) 'Error',
  COALESCE(apaac_apr.Message, apa_apr.Message, aps_apr.Message) 'Message'
FROM
  AppSessions AS aps
  INNER JOIN Sessions AS ses ON aps.SessionID = ses.ID
  INNER JOIN Users AS usr ON ses.UserID = usr.ID
  INNER JOIN Apps AS app ON aps.AppID = app.ID
  LEFT JOIN AppActivity AS apa ON aps.ID = apa.AppSessionID
  LEFT JOIN AppFormUnits AS afu ON apa.AppFormUnitID = afu.ID
  LEFT JOIN AppActivityActions AS apaa ON apa.ID = apaa.AppActivityID
  LEFT JOIN AppActions AS apac ON apaa.AppActionID = apac.ID
  LEFT JOIN AppActionTypes AS apat ON apac.AppActionTypeID = apat.ID
  LEFT JOIN AppActivityActionCalls AS apaac ON apaa.ID = apaac.AppActivityActionID
  LEFT JOIN AppResults AS apaac_apr ON apaac.AppResultID = apaac_apr.ID
  LEFT JOIN AppErrorCodes AS apaac_apec ON apaac_apr.AppErrorCodeID = apaac_apec.ID
  LEFT JOIN AppResults AS apa_apr ON (apa.AppResultID = apa_apr.ID AND apaac.AppActivityActionID IS NULL)
  LEFT JOIN AppErrorCodes AS apa_apec ON apa_apr.AppErrorCodeID = apa_apec.ID
  LEFT JOIN AppResults AS aps_apr ON (aps.AppResultID = aps_apr.ID AND apa.AppSessionID IS NULL)
  LEFT JOIN AppErrorCodes AS aps_apec ON aps_apr.AppErrorCodeID = aps_apec.ID
WHERE
  aps.`AppID` = $AppID
  AND COALESCE(apaac_apec.Error, apa_apec.Error, aps_apec.Error) <> 'success'
Apps Activity for App1 parameter
Parameters
  • $AppID (input · string · default: 14)
SELECT 
    apa.Name AS 'App Action',
    aac.`CallDescription`,
    u.Name AS 'Actor',
    ass.IpAddress AS 'IP Address',
    STRFTIME('%F %T', aaa.`StartDateTime`, 'localtime') 'Start DateTime',
    STRFTIME('%F %T', aaa.EndDateTime, 'localtime') AS 'End DateTime',
    COALESCE(AAVOM.ObjectMutationID, AAVM.MembershipID)  'ObjectMutationID'
FROM AppActivityActionCalls aac
INNER JOIN AppActivityActions aaa ON aaa.ID = aac.AppActivityActionID
INNER JOIN AppActions apa ON apa.ID = aaa.AppActionID
INNER JOIN AppActivity aa ON aa.ID = aaa.AppActivityID
INNER JOIN AppSessions aps ON aps.ID = aa.AppSessionID
INNER JOIN Sessions ass ON ass.ID = aps.SessionID
INNER JOIN Users u ON u.ID = ass.UserID
LEFT JOIN AppActivityVsObjectMutations AAVOM ON AAVOM.AppActivityID = aa.ID
LEFT JOIN AppActivityVsMemberships AAVM ON AAVM.AppActivityID = aa.ID
WHERE aps.`AppID` = $AppID AND COALESCE(AAVOM.ObjectMutationID, AAVM.MembershipID) IS NOT NULL
ORDER BY aaa.StartDateTime DESC
Apps Activity for User1 parameter
Parameters
  • $UserID (input · string)
SELECT  
    `Apps`.`Name`,
    STRFTIME('%F %T', aps.`StartDateTime`, 'localtime') 'StartDateTime',
    STRFTIME('%F %T', aps.`EndDateTime`, 'localtime') 'EndDateTime',
    aps_apr.`Message`
FROM AppSessions AS aps
INNER JOIN Sessions AS ses ON aps.SessionID = ses.ID
INNER JOIN Apps ON aps.AppID = apps.ID
LEFT  JOIN AppResults AS aps_apr ON aps.AppResultID = aps_apr.ID
WHERE ses.UserID = $UserID
ORDER BY aps.`StartDateTime` DESC
Apps SummaryNo parameters

No parameters.

WITH AppSessionCounts AS (
  SELECT  
      aps.AppID,
      COUNT(DISTINCT aps.SessionID) AS 'SessionCount'
  FROM AppSessions AS aps
  INNER JOIN Sessions AS ses ON aps.SessionID = ses.ID
  GROUP BY aps.AppID
),
ActivityCounts AS (
    SELECT 
        aps.`AppID`,
    	COUNT(*) 'AppActivityCount'
    FROM AppActivityActionCalls aac
    INNER JOIN `AppActivityActions` aaa ON aaa.ID = aac.`AppActivityActionID`
    INNER JOIN `AppActivity` aa ON aa.`ID` = aaa.`AppActivityID`
    INNER JOIN `AppSessions` aps ON aps.ID = aa.`AppSessionID`
    LEFT JOIN AppActivityVsObjectMutations AAVOM ON AAVOM.AppActivityID = aa.ID
    LEFT JOIN AppActivityVsMemberships AAVM ON AAVM.AppActivityID = aa.ID
    WHERE COALESCE(AAVOM.ObjectMutationID, AAVM.MembershipID) IS NOT NULL
    GROUP BY aps.`AppID`
),
ErrorCounts AS (
SELECT
  app.ID,
  COUNT(*) 'AppErrorCount'
FROM
  AppSessions AS aps
  INNER JOIN Sessions AS ses ON aps.SessionID = ses.ID
  INNER JOIN Users AS usr ON ses.UserID = usr.ID
  INNER JOIN Apps AS app ON aps.AppID = app.ID
  LEFT JOIN AppActivity AS apa ON aps.ID = apa.AppSessionID
  LEFT JOIN AppFormUnits AS afu ON apa.AppFormUnitID = afu.ID
  LEFT JOIN AppActivityActions AS apaa ON apa.ID = apaa.AppActivityID
  LEFT JOIN AppActions AS apac ON apaa.AppActionID = apac.ID
  LEFT JOIN AppActionTypes AS apat ON apac.AppActionTypeID = apat.ID
  LEFT JOIN AppActivityActionCalls AS apaac ON apaa.ID = apaac.AppActivityActionID
  LEFT JOIN AppResults AS apaac_apr ON apaac.AppResultID = apaac_apr.ID
  LEFT JOIN AppErrorCodes AS apaac_apec ON apaac_apr.AppErrorCodeID = apaac_apec.ID
  LEFT JOIN AppResults AS apa_apr ON (apa.AppResultID = apa_apr.ID AND apaac.AppActivityActionID IS NULL)
  LEFT JOIN AppErrorCodes AS apa_apec ON apa_apr.AppErrorCodeID = apa_apec.ID
  LEFT JOIN AppResults AS aps_apr ON (aps.AppResultID = aps_apr.ID AND apa.AppSessionID IS NULL)
  LEFT JOIN AppErrorCodes AS aps_apec ON aps_apr.AppErrorCodeID = aps_apec.ID
WHERE
  COALESCE(apaac_apec.Error, apa_apec.Error, aps_apec.Error) <> 'success'
  GROUP BY aps.AppID
),
PermissionCounts AS (
  SELECT
       GrpObj.`DisplayName` 'GroupName',
       COUNT(*) 'PermissionActivityCount'
  FROM
  	Memberships M
      INNER JOIN Operations Op ON Op.ID = M.OperationID
      INNER JOIN Activities AC ON AC.ID = M.ActivityID
      INNER JOIN ActivityTypes AT ON AT.ID = AC.ActivityTypeID
      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
      LEFT JOIN Users AS U ON U.ID = AC.UserID
  WHERE st.`SystemName` = 'internal'
  GROUP BY GrpObj.`DisplayName`
)
SELECT 
    Apps.`ID`
  , Apps.`Name`
  , COALESCE(SC.SessionCount, 0) AS 'Session Activity'
  , COALESCE(AC.AppActivityCount, 0) AS 'Action Activity'
  , COALESCE(ER.AppErrorCount, 0) AS 'Error Activity'
  , COALESCE(PC.PermissionActivityCount, 0) AS 'Permission Activity'
FROM Apps
LEFT JOIN AppSessionCounts SC ON SC.AppID = Apps.ID
LEFT JOIN ActivityCounts AC ON AC.AppID = Apps.ID
LEFT JOIN ErrorCounts ER ON ER.ID = Apps.ID
LEFT JOIN PermissionCounts PC ON PC.GroupName = ('nga_' || Apps.`Name`)
ORDER BY Apps.`Name`
Sessions Activity for App1 parameter
Parameters
  • $AppID (input · string · default: 2)
SELECT 
        STRFTIME('%F %T', aps.`StartDateTime`, 'localtime') 'Start Date-Time'
      , STRFTIME('%F %T', aps.`EndDateTime`, 'localtime') 'End Date-Time'
      , su.`Name`
      , ses.`IpAddress`
      , ses.`UserAgent`
  FROM AppSessions AS aps
  INNER JOIN Sessions AS ses ON aps.SessionID = ses.ID
  INNER JOIN `Users` AS su ON ses.UserID = su.ID
  WHERE aps.AppID = $AppID
Sessions Activity for User1 parameter
Parameters
  • $UserID (input · string · default: 2)
SELECT 
      STRFTIME('%F %T', `Sessions`.`LoginDateTime`, 'localtime') 'Login Date-Time'
    , STRFTIME('%F %T', `Sessions`.`LogoutDateTime`, 'localtime') 'Logout Date-Time'
    , `Sessions`.`IpAddress`
    , `Sessions`.`UserAgent`
FROM `Sessions`
WHERE `Sessions`.`UserID` = $UserID
ORDER BY `Sessions`.`LoginDateTime` DESC

Resource changes (8)

Resources Changes for Actor1 parameter
Parameters
  • $Key1 (input · string)
SELECT
  datetime(ru.DateTime, 'localtime') 'Operation DT',
  usr.Name 'Actor',
  rt.Name 'Type',
  ru.ResourceName 'Name',
  op.Name 'Action'
FROM ResourceUpdates AS ru
INNER JOIN ResourceTypes AS rt ON ru.ResourceTypeID = rt.ID
INNER JOIN Sessions AS ses ON ru.SessionID = ses.ID
INNER JOIN Users AS usr ON ses.UserID = usr.ID AND ses.UserID = $Key1
INNER JOIN Operations AS op ON ru.OperationID = op.ID
ORDER BY ru.DateTime DESC
Resources Changes for Resource Name1 parameter
Parameters
  • $Key1 (input · string)
SELECT
  datetime(ru.DateTime, 'localtime') 'Operation DT',
  usr.Name 'Actor',
  rt.Name 'Type',
  ru.ResourceName 'Name',
  op.Name 'Action'
FROM ResourceUpdates AS ru
INNER JOIN ResourceTypes AS rt ON ru.ResourceTypeID = rt.ID
INNER JOIN Sessions AS ses ON ru.SessionID = ses.ID
INNER JOIN Users AS usr ON ses.UserID = usr.ID
INNER JOIN Operations AS op ON ru.OperationID = op.ID
  WHERE ru.`ResourceName` = $Key1
ORDER BY ru.DateTime DESC
Resources Changes for Resource Type1 parameter
Parameters
  • $Key1 (input · string)
SELECT
  datetime(ru.DateTime, 'localtime') 'Operation DT',
  usr.Name 'Actor',
  rt.Name 'Type',
  ru.ResourceName 'Name',
  op.Name 'Action'
FROM ResourceUpdates AS ru
INNER JOIN ResourceTypes AS rt ON ru.ResourceTypeID = rt.ID AND ru.`ResourceTypeID` = $Key1
INNER JOIN Sessions AS ses ON ru.SessionID = ses.ID
INNER JOIN Users AS usr ON ses.UserID = usr.ID
INNER JOIN Operations AS op ON ru.OperationID = op.ID
ORDER BY ru.DateTime DESC
Resources Changes for Type Actor2 parameters
Parameters
  • $Type (input · string)
  • $Actor (input · string)
SELECT
  datetime(ru.DateTime, 'localtime') 'Operation DT',
  ru.ResourceName 'Name',
  op.Name 'Action'
FROM ResourceUpdates AS ru
INNER JOIN ResourceTypes AS rt ON ru.ResourceTypeID = rt.ID AND rt.ID = $Type
INNER JOIN Sessions AS ses ON ru.SessionID = ses.ID
INNER JOIN Users AS usr ON ses.UserID = usr.ID AND usr.ID = $Actor
INNER JOIN Operations AS op ON ru.OperationID = op.ID
ORDER BY ru.DateTime DESC
Resources Recent ChangesNo parameters

No parameters.

SELECT
	datetime(ru.DateTime, 'localtime') 'Operation DT',
	usr.Name 'Actor',
	rt.Name 'Type',
	ru.ResourceName 'Name',
	op.Name 'Action'
FROM
	ResourceUpdates AS ru
	INNER JOIN ResourceTypes AS rt ON ru.ResourceTypeID = rt.ID
	INNER JOIN Sessions AS ses ON ru.SessionID = ses.ID
	INNER JOIN Users AS usr ON ses.UserID = usr.ID
	INNER JOIN Operations AS op ON ru.OperationID = op.ID
Resources SummaryNo parameters

No parameters.

SELECT
    rt.ID AS ResourceTypeID,
    rt.Name AS ResourceType,
    COUNT(*) AS UpdatesCount,
    COUNT(DISTINCT ru.SessionID) AS SessionCount,
    COUNT(DISTINCT ru.ResourceName) AS UniqueResourcesTouched
FROM ResourceUpdates AS ru
INNER JOIN ResourceTypes AS rt
    ON ru.ResourceTypeID = rt.ID
GROUP BY
    rt.ID,
    rt.Name
ORDER BY
    rt.ID;
Resources Summary for Actors1 parameter
Parameters
  • $Key1 (input · string)
SELECT
    usr.ID AS UserID,
    usr.Name AS Actor,
    COUNT(*) AS UpdateCount,
    COUNT(DISTINCT ru.ResourceName) AS ResourceCount
FROM ResourceUpdates AS ru
INNER JOIN Sessions AS ses
    ON ru.SessionID = ses.ID
INNER JOIN Users AS usr
    ON ses.UserID = usr.ID
WHERE ru.ResourceTypeID = $Key1
GROUP BY
    usr.ID,
    usr.Name
ORDER BY
    UpdateCount DESC;
Resources Summary for Resource1 parameter
Parameters
  • $Key1 (input · string)
SELECT
    ru.ResourceName AS ResourceName,
    COUNT(*) AS UpdateCount,
    COUNT(DISTINCT ru.SessionID) AS SessionCount
FROM ResourceUpdates AS ru
  WHERE ru.`ResourceTypeID` = $Key1
GROUP BY
    ru.ResourceName
ORDER BY
    UpdateCount DESC;

Audit database referenceDirect link to Audit database reference

Application actions include the initiating user, application, timestamp, description, target objects, and any relevant attribute or membership changes. Set meaningful action descriptions in apps so an auditor can understand what occurred without reconstructing the workflow.

Edit, copy, rename, or remove a queryDirect link to Edit, copy, rename, or remove a query

Select Edit Auditing Query to update SQL or parameters, Copy Object to create a numbered copy, Rename Object to change its name, or Remove Auditing Query and confirm to delete an unused query.

Last updated on