SQL Query to Pull Alert Report from SCOM Operations Database

Question

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?

Answer 1

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

To verify this, we may run the following query:

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

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

Answer 2

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.
   

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