4 Replies Latest reply on Jul 10, 2015 2:07 PM by mvattem

Database Query to link AlertLog table contents with the AuditingEvents table content

chetz Dec 3, 2014 7:38 AM

Hi All,

I am trying to build a custom report for our NOC team to extract the alert information(mostly Historical) for any given Node in Orion from the ALertLog table (which contents the alert info that is not available to see on the console). Currently I am looking for linking the "Alert Notes" data which resides in the AuditingEvents table for each alert in the AlertLog table. Im not sure if there any key or ID that links each alert to its alert notes data (in AuditingEvents field)

Any help is appreciated.

Thanks,

Chethan

Re: Database Query to link AlertLog table contents with the AuditingEvents table content

mharvey Dec 3, 2014 7:55 AM (in response to chetz)

I had done something similar a while back. It's a pretty involved query, but you have to be able to search the auditing events for the alertdefID and use the node ID as well in order to find any auditing events tied to a specific alert. I unfortunately no longer have the exact query I used the last time I set that up. I'll do some digging to see if I can find any remnants of it.
Like (0)

Actions
- Re: Database Query to link AlertLog table contents with the AuditingEvents table content
  
  chetz Dec 3, 2014 1:02 PM (in response to mharvey)
  
  Thanks! mharvey , I m trying to understand the DB schema as well. Is there any DB schema document available that I can refer to?
  
  thanks!
  Chethan
  
  Like (0)
  
  Actions
  - Re: Database Query to link AlertLog table contents with the AuditingEvents table content
    
    mharvey Dec 3, 2014 1:38 PM (in response to chetz)
    
    Nothing that's been published by SolarWinds. If you run the diagnostics tool though, the database schema is provided in that .zip file.
    
    Like (0)
    
    Actions
Re: Database Query to link AlertLog table contents with the AuditingEvents table content

mvattem Jul 10, 2015 2:07 PM (in response to chetz)

Here is what I came up with:
SELECT DISTINCT ae.[AuditEventID]
,DATEADD(hh, -8, ae.[TimeLoggedUtc]) TimeLogged
,ae.[AccountID]
,ae.[ActionTypeID]
,ae.[AuditEventMessage]
,n.NodeID
,n.SysName, n.IP_Address
,e.Message EventMessage
,DATEADD(hh, -8, n.UnManageFrom)
,DATEADD(hh, -8, n.UnmanageUntil)
,a.Message AlertMessage
FROM [SolarWindsOrion].[dbo].[AuditingEvents] ae WITH (NOLOCK)
INNER JOIN [SolarWindsOrion].[dbo].[Nodes] n WITH (NOLOCK) ON ae.NetworkNode = n.NodeID
INNER JOIN [SolarWindsOrion].[dbo].[Events] e WITH (NOLOCK) ON e.NetObjectID = ae.NetObjectID
INNER JOIN [SolarWindsOrion].[dbo].[AlertLog] a WITH (NOLOCK) ON a.ObjectID=n.NodeID
ORDER BY DATEADD(hh, -8, ae.[TimeLoggedUtc]) DESC

you can tweak as you wish.

Mahidhar Vattem
Like (0)

Actions

Go to original post