This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

chetz over 9 years ago

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

0 mharvey over 9 years ago

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 chetz over 9 years ago in reply 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
Cancel
Vote Up 0 Vote Down

Cancel
0 mharvey over 9 years ago in reply 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.
Cancel
Vote Up 0 Vote Down

Cancel
0 mvattem over 8 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel