-
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.
-
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
-
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.
-
-
-
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