Anyone got any idea on this? I'm really stumped here and have a meeting today where they need these Event Reports.
What do you want to achieve via SWQL? Put query here and we can look.
Unfortunately there is not easy way how to do that via SQL. Only way is create bunch of select and UNION results or use CASE.
I'm trying to get a report of all of our Events for a certain time period. If I try and run the query in SWQL I have two issues:
SWQL Problem 1:
If I query it in SWQL for more than a few days back then I get an Out of Memory Exception. If I run the query in SQL for the last 2 weeks (which is what I need) then it takes 66 seconds and comes back with 1,027,564 Rows. The problem with using SQL though is like I stated above, I can't map NetObjectTypes to the actual Entity name without doing bunches of CASE Statements...
SWQL Problem 2:
If I JOIN the SWQL Query to the "NetObjectTypes" table so that I can map the NetObjectType field in Orion.Events to an actual Entity type Name, then I get the error "Unknown dataprovider type 'ntext'".
Here is the query (The Query for Problem 1 is just the query below with the ot.Name line commented out of the SELECT statement and the LEFT OUTER JOIN to Orion.NetObjectTypes commented out):
SELECT ot.Name AS ObjectTypeName ,et.Name AS EventTypeName ,e.Message AS EventMessage ,n.Caption AS Node ,TOSTRING(TOLOCAL(e.EventTime)) AS LocalEventTime ,pe.ServerName AS PollingEngine FROM Orion.Events e LEFT OUTER JOIN Orion.EventTypes et ON e.EventType = et.EventType LEFT OUTER JOIN Orion.NetObjectTypes ot ON e.NetObjectType = ot.Prefix LEFT OUTER JOIN Orion.Nodes n ON e.NetworkNode = n.NodeID LEFT OUTER JOIN Orion.Engines pe ON e.EngineID = pe.EngineID WHERE TOLOCAL(e.EventTime) >= AddDate('day', -2, GETDATE())
My temporary fix is that I created my own Schema in the SolarWindsOrion database and created a custom table called NetObjectTypes_Custom. Then, I imported a spreadsheet of Prefixes to NetObjectType Names into it. Now I can write a SQL Query and join the Events tables NetObjectType field to the Prefix field of my custom NetObjectTypes table. This is less than ideal and I would rather do it through SWQL to begin with, but the API just can't seem to handle the size of our Events table...
First You can remove the conversion in where:
e.EventTime >= AddDate('day', -1, GETUTCDATE())
but it will only faster I don§t think it will not throw memory exception.
You can try create support ticket with providing your spec and diagnostic and we can look if we can do something with your problem.
Sure, I could just do it on UTC date, but that could be confusing to the end user. I'd rather allow them to filter on local datetime. But even without that it doesn't matter, it still throws all of the same errors.
Thank you for the response and perhaps I will file a ticket.