question

DeepakUppund-8412 avatar image
0 Votes"
DeepakUppund-8412 asked RogerXue-3369 edited

SQL Query to Pull Alert Report from SCOM Operations Database

Hello,

I am trying to run the query to pull the report as shown in the screenshot. The problem i am facing here is, Whenever i am seeing AlertCount greater than 1, Ticket ID will show as Null instead of listing all the ticket numbers.
Only when the AlertCount is 1, it is pulling the ticket number.


Can someone help me to customize this query so i can see all ticket numbers generated?

125664-scomsqlquery.png


msc-operations-manager
scomsqlquery.png (218.0 KiB)
5 |1600 characters needed characters left characters exceeded

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

RogerXue-3369 avatar image
0 Votes"
RogerXue-3369 answered RogerXue-3369 edited

It is because your SQL statement grouping ticketid.
1) Besides, by default the ticketid is null when a new alert is generated and need manually update its value.
128224-pict001.jpg


2) If you want count(*) is more than one and tickid is not null when you manually create same ticketid for same alert name, name and string path This means that you should assign same ticketid 'T12345' to 2 or alert which has alert name "ConfigMgr Server component issue', name "Microsoft.systemcenter2012. configurationmanager" and Monitoring object Path "ServerName.com"

Roger



pict001.jpg (13.1 KiB)
5 |1600 characters needed characters left characters exceeded

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

AlexZhu-MSFT avatar image
0 Votes"
AlexZhu-MSFT answered AlexZhu-MSFT commented

Hi,

I'm afraid the actual value of ticketid is null and this is not because of the alertcout > 1.

Here's a quick test in my lab
125768-scom-query-03.png


To verify this, we may run the following query:

 select count(*) from alertview where alertstringname = 'ConfigMgr Server Component Issue' and ticketid is not null

125659-scom-query-02.png


Alex
If the response is helpful, please click "Accept Answer" and upvote it.



scom-query-03.png (33.1 KiB)
scom-query-02.png (16.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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

Hi,

It seems there is no update for a couple of days. May we know the current status of the problem? Or is there any other assistance we can provide?

Regards,

Alex
If the response is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·