June 20
SQL Reporting - Separate Event Parameters into columns
***The Query below can be copied into query analyzer and run without any changes against your SystemCenterReporting DB.***
SELECT SUBSTRING(Message, PATINDEX('%Member Name:%', Message) + 12, PATINDEX('%Member ID:%', Message) - PATINDEX('%Member Name:%',Message) - 12) AS MName, SUBSTRING(Message, PATINDEX('%Member ID:%', Message) + 11, PATINDEX('%Target Account Name:%', Message) - PATINDEX('%Member ID:%', Message) - 11) AS MemberID, SUBSTRING(Message, PATINDEX('%Target Account Name:%', Message) + 20,PATINDEX('%Target Domain:%', Message) - PATINDEX('%Target Account Name:%', Message) - 20) AS TAName, SUBSTRING(Message,PATINDEX('%Target Domain:%', Message) + 14, PATINDEX('%Target Account ID:%', Message) - PATINDEX('%Target Domain:%', Message) - 14) AS TDomain, NTEventID, UserName AS Admin, TimeGenerated
FROM SDKEventView
WHERE (NTEventID IN (636, 632, 660, 650, 655, 665)) and (SUBSTRING(Message, PATINDEX('%Target Account Name:%', Message) + 20,PATINDEX('%Target Domain:%', Message) - PATINDEX('%Target Account Name:%', Message) - 20) LIKE '%admin%')
ORDER BY TimeGenerated
If you audit your event logs for admin group add's or any audit events for that matter then the above code will surely help you with your reporting. What I found annoying about developing reports for audit events was the description field is all blocked together in the Message column of your SDKEventView in SystemCenterReporting. This makes reporting messy to read as there is plenty of information that you absolutely do not require in the report IMHO. The above code will parse out all the data from the 'Message' column that I find to be unnecessary, and it will break out the remaining parameter information into separate fields in your report thus allowing a great deal more versatility in filtering the data once it's been pulled from the view.
The code is easy to edit if you choose to add or remove parameters. It's using a basic PATINDEX function from sql to define the starting position and length of the strings. Lets use the description for Audit event 632 in this example. Each line is a parameter in the description for this event.
Security Enabled Global Group Member Added:
Member Name: -
Member ID: %{S-I-D-SI-DSIDSIDSID-SIDSIDSIDS-IDSIDSIDS-IDSID}
Target Account Name: None
Target Domain: YOUDOMAIN OR LOCAL MACHINE
Target Account ID: GroupName
Caller User Name: UserName
Caller Domain: Domain
Caller Logon ID: (0x0,0x00000)
Privileges: -
The basic logic is this, if u want to break out Parameter 2 'Member Name:' in your SQL Report then you'll have to use this line of code in your Select Statement to do that:
SUBSTRING(Message, PATINDEX('%Member Name:%', Message) + 12, PATINDEX('%Member ID:%', Message) - PATINDEX('%Member Name:%',Message) - 12) AS MName
In this example, lets say "Member Name:" begins in position 51 (and lets call that A). And lets say "Member ID:" starts in 91 (and lets call that B). Then the PatIndex would need to use params substring (Message,(A,B-A)). See why that works? B=91 A=51 then the LENGTH of the literal string is 40. So the string would start at position 51 and then go out another 40 positions only. You can apply this logic with any of the the above Parameters.
If you have any questions about how to build a Event Report from scratch in Visual Studio, here is a great link for it.
Any questions, feel free to post here.
Regards,
Bryce
Tools 
