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.
Thanks! mharvey , I m trying to understand the DB schema as well. Is there any DB schema document available that I can refer to?
Nothing that's been published by SolarWinds. If you run the diagnostics tool though, the database schema is provided in that .zip file.
Here is what I came up with:
SELECT DISTINCT ae.[AuditEventID]
,DATEADD(hh, -8, ae.[TimeLoggedUtc]) TimeLogged
,DATEADD(hh, -8, n.UnManageFrom)
,DATEADD(hh, -8, n.UnmanageUntil)
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.