Need Capture the Event ID monitoring for SQL Profiler tracer

Question

question

anupvtr asked Mar 29, '22 anupvtr answered Apr 13, '22

Need Capture the Event ID monitoring for SQL Profiler tracer

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"

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

msc-operations-manager

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-03-31T15:06:59.803Z

StoyanChalakov answered Mar 31, '22 StoyanChalakov converted comment to answer Apr 13, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-06T05:27:39.557Z

anupvtr answered Apr 6, '22

@StoyanChalakov

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question