5 Replies Latest reply on Apr 7, 2016 11:06 AM by xtraspecialj

    NetObjectTypes mapping in the SQL Database

    xtraspecialj

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

        • Re: NetObjectTypes mapping in the SQL Database
          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

          • Re: NetObjectTypes mapping in the SQL Database
            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

              • Re: NetObjectTypes mapping in the SQL Database
                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.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...