4 Replies Latest reply on May 30, 2017 5:26 PM by lsalinas

    SWQL views?

    lsalinas

      I am trying to create a data feed for my CMDB (ServiceNow) from SWIS.   I am combining fields from Orion.Nodes and Orion.NodesCustomProperties and have the SWQL SELECT statement done, just need to provide the data to SNow.

       

      In my previous management tool, I created a custom View in SQL for them to run a  SELECT *   for their feed.  This was to simplify the request on the CMDB side, and we kept the view upto date on our end.

       

      Is there an equivalent to creating Views in SWQL Studio?

       

      thanks

      larry

        • Re: SWQL views?
          tdanner

          No, there is not. You will need to maintain the SWQL query on the client end.

            • Re: SWQL views?
              lsalinas

              Thanks. 

               

              I will have to rework my query anyway, because after looking I noticed that Serial Number is not in the Node tables, so I have to pull it from the NCM entity tables (only place I found it) as well.

               

              Any plans to include a custom View to SWQL studio so we could build queries across multiple products?

               

              Any advice on pulling SWQL data from multiple tables, trying to figure out what to key off of to consolidate data, is appreciated as I am 3 weeks into my first SW deployment. 

                • Re: SWQL views?
                  tdanner

                  We have no current plans for allowing users to define custom views in SWQL. It's an interesting idea though.

                   

                  If you are new to SolarWinds and SWQL, you may not yet be familiar with navigation properties. They are syntactic sugar for JOINs. So you can get to that serial number from nodes like this:

                   

                  SELECT N.NodeID, N.DisplayName, N.NodeProperties.EntityPhysical.Serial

                  FROM Orion.Nodes N

                    • Re: SWQL views?
                      lsalinas

                      thanks, yes, brand new to Solarwinds and SWQL.  Watched the lab you put together on coding CURL, PS, REST, JSON, etc,  Very helpful.

                       

                      I saw you did that trick, but didn't understand how it worked.  I think I got it figured out, just need to validate w/ Network and SNow teams to see if they will accept the output.

                       

                      Here is the initial query, in case you're interested.  Not sure if last filter SWRev is the one I will stick with.

                       

                      SELECT n.NodeID, n.IPAddress, n.Caption, n.NodeDescription, n.Description, n.Vendor, n.Contact, n.IOSImage, n.IOSVersion, n.LastBoot, n.MachineType, n.NodeName, n.CustomProperties.ZL_Location, n.CustomProperties.ZL_Region, n.NodeProperties.EntityPhysical.Serial, n.NodeProperties.EntityPhysical.EntityName, n.NodeProperties.EntityPhysical.EntityDescription

                      FROM Orion.Nodes n WHERE n.NodeProperties.EntityPhysical.Serial <> '' AND n.NodeProperties.EntityPhysical.SoftwareRevision <> ''

                       

                      larry