NetObjectTypes mapping in the SQL Database

I know there is a SWQL table called "NetObjectTypes" and "NetObjectTypesExt" that has the NetObjectType prefixes mapped to the actual Entity Names, but I can't find something similar in the actual SQL Server Orion Database. Anybody know if this data is in the SQL database somewhere?

The reason why is I need an Event report that SWQL just can't handle unless I limit the EventTime field to less than a week, otherwise I get an Out-Of-Memory Exception, and I need way more than a week for the project I'm working on. Also, for some weird reason in SWQL when I try and JOIN the Orion.Events NetObjectType column to the Orion.NetObjectTypes Prefix column I get the error "Unknown dataprovider type 'ntext'" no matter how many different ways I write the query, so that's even more reason to use SQL. I really don't feel like doing a bunch of CASE statements in SQL to convert the Events NetObjectType column data to an actual entity name, but I will if I have to...

Find more posts tagged with

netobject

Custom Query

netobjecttypes

SQL Server

SWQL Studio

Events

Accepted answers

All comments

xtraspecialj

Bump

Anyone got any idea on this? I'm really stumped here and have a meeting today where they need these Event Reports.

Thank you

LadaVarga

Hello,

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.

Lada

xtraspecialj

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.EventTypeLEFT OUTER JOIN   Orion.NetObjectTypes ot ON e.NetObjectType = ot.PrefixLEFT OUTER JOIN   Orion.Nodes n ON e.NetworkNode = n.NodeIDLEFT OUTER JOIN   Orion.Engines pe ON e.EngineID = pe.EngineIDWHERE  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...

LadaVarga

Hello,

First You can remove the conversion in where:

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.

Lada

xtraspecialj

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.