Need Capture the Event ID monitoring for SQL Profiler tracer

Question

Hi All, Thanks in advance,

I have requirement if a SQL Profiler trace is started by a user and its not ended with in 30 minutes i need to receive a SCOM alert.

Event ID 19030 is generated in Application log if a SQL profiler is started.

"SQL Trace ID 5 was started by login "Blow\sqlmps08gtr"

<in between we may receive SQL Trace stopped event ID's(19031) for different trace id's>

Event ID 19031 is generated if SQL profiler is stopped
"SQL Trace stopped. trace ID = '5'. Login Name = 'Blow\sqlmps08gtr"
The condition to correlate the starting event and stopping of the profiler should use the Trace ID also.
Condition Event 19030 + Data[0] should match event 19031 + Data[0] where Data[0] = 'SAL Trace ID"

how can i achieve this.

Thanks,
Anupvtr

Answer 1

Hi Anu,

If I understand correctly you want to recieve an Alert if the SQL Profiler Trace stops, am I right?
You are mentioninjg in your post:

Event ID 19031 is generated if SQL profiler is stopped

My suggestion here would be to configure a simple Alert Generating Event based Rule, which triggers an Alert whenever Event with the ID 19031 from the respective source is logged on the monitored SQL node:

SCOM Event Based Monitoring – Part 2 – Rules
https://anaops.com/2018/04/29/scom-event-based-monitoring-part-2-rules/

i don't see any reasons to make it more complex with event correlation and a monitor. If you want to know when the trace is stopped, just capture the related event. Please share if I got this wrong, will try to help yyou solve this one.

---

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Stoyan Chalakov

Answer 2

@SChalakov

The event ID responsible for SQL profile start and stop are 19030 and 19031. these event ID s are getting generated too frequent by the technical and non technical accounts. It will create an alert flood. We need to capture only if the SQL profile trace is exist more than 30 minutes. Some how this was a bad idea. I implemented this using powershell scripting with SQL commands.

Thanks
Anu