7 Replies Latest reply on Jul 18, 2016 5:19 PM by timbolton

    Orion WSQL Short-Comings

    bmcmcm

      Where I see a major problem with WSQL is returning properties for a node, where you don't know what is available. You can use a query like:

       

       

      SELECT Name FROM Metadata.Property where EntityName = 'Orion.Nodes'

       

      to return all possible properties for nodes, but the problem is that the list that is returned appears to contain properties for all nodes type, where any given property may be valid for some nodes and invalid for others. Is there a way to return property names for a specific type like a Windows server node?

       

      If I want to return all valid properties for a node, is there a way to do it concisely instead of:

       

       

      SELECT NodeID,ObjectSubType,IPAddress,IPAddressType,DynamicIP,Caption,NodeDescription,Description,DNS,SysName,Vendor,SysObjectID,

      Location,Contact,VendorIcon,Icon,IOSImage,IOSVersion,GroupStatus,StatusIcon,LastBoot,SystemUpTime,ResponseTime,PercentLoss,AvgResponseTime,

      MinResponseTime,MaxResponseTime,CPULoad,MemoryUsed,MemoryAvailable,PercentMemoryUsed,PercentMemoryAvailable,LastSync,LastSystemUpTimePollUtc,

      MachineType,Severity,ChildStatus,Allow64BitCounters,AgentPort,TotalMemory,CMTS,CustomPollerLastStatisticsPoll,CustomPollerLastStatisticsPollSuccess,SNMPVersion,

      PollInterval,EngineID,RediscoveryInterval,NextPoll,NextRediscovery,StatCollection,External,Community,RWCommunity,IP,IP_Address,IPAddressGUID,NodeName,

      BlockUntil,BufferNoMemThisHour,BufferNoMemToday,BufferSmMissThisHour,BufferSmMissToday,BufferMdMissThisHour,BufferMdMissToday,BufferBgMissThisHour,

      BufferBgMissToday,BufferLgMissThisHour,BufferLgMissToday,BufferHgMissThisHour,BufferHgMissToday,OrionIdPrefix,OrionIdColumn,MinutesSinceLastSync,

      EntityType,DetailsUrl FROM Orion.Nodes order by nodeid"

       

      By the way I did not type that in manually, LOL. I'm trying to use this in Powershell and what I'm trying to get at are things like the assigned application monitors, event list, and/or active alerts for a given node, in addition to current configuration information.

       

       

        • Re: Orion WSQL Short-Comings
          Craig Norborg

          Hmm...  I think the problem here is that all the properties are "valid" for any given node type, they're just probably generally blank for different types of nodes (ie: Cisco vs. Windows).   SWQL like SQL is pretty stupid when it comes to realizing that the field "IOSImage" is probably blank for all nodes where the Vendor is "Windows". 

           

          My guess is that neither SQL or SWQL will ever have this type of intelligence built in.

           

          Of course I could be wrong!! 

            • Re: Orion WSQL Short-Comings
              bmcmcm

              I agree that WSQL will never have the intelligence built in, better PowerShell integration is what is really needed. WSQL should be an option, not a requirement to return node information in PowerShell. A good PS snapin would allow a user to use a command like Get-SWNode -NodeName xxxxxx and return an object with all valid properties.

               

              If you execute SELECT Name FROM Metadata.Property where EntityName = 'Orion.Nodes', you will get a huge list, much larger than the property list I have in my example. About half of the properties had to be cut out of the list because they caused WSQL to throw an error, which looked like an invalid property error for the node I was trying to query. I attribute the error to the node type not having those properties at all, but it could be that the huge list of properties for Orion.Nodes contains properties you just can't use in a WSQL query at all.

                • Re: Orion WSQL Short-Comings
                  Craig Norborg

                  See, I always go into SWQL studio and right-click on the table I want and do a "GENERATE SELECT STATEMENT" myself.  Never used the Metadata tables.

                   

                  I'm sure quite a few people would love it if someone were to generate PowerShell "procedures" that could be used to hide the back-end SWQL from being used directly for simple things.   Not that familiar with Powershell, don't even know if you can do that type of thing!  But guessing it would be popular!!

                  • Re: Orion WSQL Short-Comings
                    tdanner

                    This higher-level API is the idea behind micheal100's PowerOrion PowerShell module. You can get it at OrionSDK/Samples/PowerShell/PowerOrion at master · solarwinds/OrionSDK · GitHub. It's not comprehensive by any means, but what it does have is in the spirit you describe.

                     

                    The reason you are getting errors when you select everything returned "SELECT Name FROM Metadata.Property where EntityName = 'Orion.Nodes'" from Orion.Nodes is that you are getting both the normal properties and the navigation properties. (The error message SWIS gives when you do this could definitely be improved.) This query will give you all of the normal properties of Orion.Nodes:

                     

                    SELECT Name FROM Metadata.Property where EntityName = 'Orion.Nodes' AND IsNavigable=false

                    1 of 1 people found this helpful
                • Re: Orion WSQL Short-Comings
                  Michael Halpin

                  Thanks Tim. Just to follow up on that bmcmcm & cnorborg, the PowerShell module I started to work is aimed at doing the standard things most people want, but there will also be the flexibility to use the more powerful SWQL approach as well (in essence that's what the module is, just pre-packaging some standard functionality).

                   

                  The module is a side-project, based on what I thought would be most useful, and obviously there is a lot of scope for expansion. Of course, if you have feedback on the types of info you want pulled back I can look at incorportating those. (Better yet, all the code is now on Github as well, so feel free to make changes yourself and submit them back in, and we can merge them!)

                    • Re: Orion WSQL Short-Comings
                      timbolton

                      This is helping keep track of who has been added as Individual Users.

                       

                      Would love to see if someone has better method.

                       

                      Get-SwisData $SWIS  "SELECT AccountID,Enabled,AllowAdmin,MenuName,Expires,LastLogin,Uri FROM Orion.Accounts" | Export-CSV C:\Temp\SwisData.csv -NoTypeInformation