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