topic Useful SQL Queries on Audit Logs in Product Forum

Useful SQL Queries on Audit Logs

vivek.kumar.goel — Thu, 06 Feb 2020 04:46:00 GMT

This thread is created to enable the community members play around with AuditLog entries to get some meaningful data.

Each post will contain one such query and it's explanation.

------------------------------
Vivek Goel
CoE Lead Architect
Asia/Singapore
"If you like this post, please press the "Recommend" Button.
------------------------------

RE: Useful SQL Queries on Audit Logs

vivek.kumar.goel — Thu, 06 Feb 2020 04:50:00 GMT

Version: BP 6.5 and above
Query Usage: To track changes to environment variable.
Description: This query can be used to know who , when, which ,from what , to what --Changes on Environment variable
Disclaimer: This may not be an optimized query. It's given just for knowledge sharing purpose.

SELECT [eventdatetime]
	   ,SUBSTRING([sNarrative],11,CHARINDEX('modified',[sNarrative],1)-13) as username
	   ,SUBSTRING([sNarrative],CHARINDEX('environment variable',[sNarrative],1)+22,(Len([sNarrative])-CHARINDEX('environment variable',[sNarrative],1)-22)) as env_var_name
       ,[sNarrative]
	   ,SUBSTRING([comments],CHARINDEX('Old Value: "',[comments],1)+12,CHARINDEX('", New Value:',[comments],1)-CHARINDEX('Old Value: "',[comments],1)-12) as old_value
	   ,SUBSTRING([comments],CHARINDEX('New Value: "',[comments],1)+12,LEN([comments])-CHARINDEX('New Value: "',[comments],1)-12-2) as new_value
	   ,[comments]
FROM [dbo].[BPAAuditEvents] 
where [sNarrative] like '%modified%environment variable%'
order by eventdatetime desc

Explanation: This query basically uses text manipulation (Sub Str and Len) functions to find out what was modified on the environment variable.
It picks up datetime, username & environment variable name from the sNarrative column , old value and new value from the comment column within Auditlog table.

Example Output:

Hope it helps.

------------------------------
Vivek Goel
CoE Lead Architect
Asia/Singapore
"If you like this post, please press the "Recommend" Button.
------------------------------

RE: Useful SQL Queries on Audit Logs

vivek.kumar.goel — Thu, 06 Feb 2020 04:54:00 GMT

Version: BP 5 and above
Query Usage: To track changes to schedule.
Description: This query can be used to know who retired which schedule and when
Disclaimer: This may not be an optimized query. It's given just for knowledge sharing purpose.

SELECT [eventdatetime] 
      ,SUBSTRING([sNarrative],11,CHARINDEX('retired',[sNarrative],1)-13) as username
      ,SUBSTRING([sNarrative],CHARINDEX('schedule',[sNarrative],1)+10,Len([sNarrative])-CHARINDEX('schedule',[sNarrative],1)-10) as scheduleName
	  ,[sNarrative]
	  ,[comments]
FROM [dbo].[BPAAuditEvents] where sNarrative like '%retired%' order by [eventdatetime] desc

Example Output:

------------------------------
Vivek Goel
CoE Lead Architect
Asia/Singapore
"If you like this post, please press the "Recommend" Button.
------------------------------