Useful SQL Queries on Audit Logs

[VivekGoel]

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

[VivekGoel]

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

[VivekGoel]

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