19 Replies Latest reply on Feb 12, 2018 2:38 PM by mesverrum

    Custom SWQL resource

    stuartwhyte

      Hi,

       

      Cross posting as didn't get a response in NPM area:

       

      Custom SWQL resource

       

      Thanks

       

       

      Stuart

        • Re: Custom SWQL resource
          Jan Pelousek

          Hello Stuart, if I understand you well, your need from Custom Query resource to create expandable tree, or just displaying of the parent entity only once. AFAIK this is at other places in Node Management, etc. it's done by the JavaScript. The Custom query resource everytime gives you the table of results defined by the query. I'd wonder if something like this was even possible in SQL. However there should be possible to achieve it by using the Old Report Writer, make report for the info you need (similar principle to e.g. canned report All Disk Volumes) and add it to your website inside the "Report from Report Writer" resource. In upcoming release there should be also possibility to add the "Custom Table", as known from the Web-based reporting, to the view, which also should do the trick for you.

          Regards,

           

          Honza

            • Re: Custom SWQL resource
              stuartwhyte

              Hi Honza,

               

              Thanks for the response.

               

              I am not really looking for the list to me expandable as in (like you say) the Node Management view, but to group by node name as the parent, with the interface entity's under.  An SQL query would accomplish this with the "group by" clause, however would not display as cleanly as this resource could.

               

              Stuart

                • Re: Re: Custom SWQL resource
                  tdanner

                  Here's a query that returns the data you want. The formatting isn't ideal since Custom Query Resource doesn't give you a way to control the column span of the table cells.

                   

                  SELECT 
                      n.caption as Node, 
                      '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node], 
                      n.ipaddress as IP_Address, 
                      n.ObjectSubType as Polling_Method, 
                      n.StatusDescription as Status,
                      n.NodeID,
                      1 AS Kind
                  FROM orion.nodes n
                  UNION ALL (
                  SELECT 
                      I.Caption as Interface,
                      '/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface], 
                      NULL as IP_Address, 
                      NULL as Polling_Method, 
                      NULL as Status,
                      I.NodeID,
                      2 AS Kind
                  FROM Orion.NPM.Interfaces I 
                  ) UNION ALL (
                  SELECT 
                      V.Caption as Interface,
                      '/Orion/images/StatusIcons/Small-' + V.StatusLED AS [_IconFor_Interface], 
                      NULL as IP_Address, 
                      NULL as Polling_Method, 
                      NULL as Status,
                      V.NodeID,
                      3 AS Kind
                  FROM Orion.Volumes V
                  )
                  ORDER Y NodeID, Kind
                  
                  
                  1 of 1 people found this helpful
                    • Re: Re: Re: Custom SWQL resource
                      stuartwhyte

                      Hi tdanner

                       

                      Thanks for the pointers.  This has pretty much achieved what I was looking for.  Like you mentioned, it's a shame about the formatting.

                       

                      I made a few changes - VolumeType Icon for volume status icon and corrected the typo on your line 31 (my line 38 below)

                       

                      SELECT   
                          n.caption as Node,   
                          '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node],   
                          n.ipaddress as IP_Address,   
                          n.ObjectSubType as Polling_Method,   
                          n.StatusDescription as Status,  
                          n.NodeID,  
                          1 AS Kind  
                      FROM orion.nodes n  
                      
                      
                      UNION ALL (  
                      SELECT   
                          
                      I.Caption as Interface,  
                          '/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface],    
                      NULL as IP_Address,    
                      NULL as Polling_Method,   
                          NULL as Status,  
                          I.NodeID,  
                          2 AS Kind  
                      FROM Orion.NPM.Interfaces I   )
                      
                      
                      UNION ALL (  
                      SELECT   
                          V.Caption as Interface,  
                          '/NetPerfMon/images/Volumes/' + V.VolumeTypeIcon AS [_IconFor_Interface],   
                          NULL as IP_Address,   
                          NULL as Polling_Method,   
                          NULL as Status,  
                          V.NodeID,  
                          3 AS Kind  
                      FROM Orion.Volumes V  
                      )  
                      
                      
                      ORDER bY NodeID, Kind 
                      
                      
                      
                      
                      
                      
                      
                      

                       

                      This will be perfect for now.  Thanks again.

                       

                      Stuart

                        • Re: Custom SWQL resource
                          msarkar

                          tdanner or @

                          what happened to my case, i just run a simple below query fro example in Custom table resource or custom query resource in Orion it throws me error as follows:

                           

                          select Nodes.NodeiD as Name,Nodes.Status from orion.nodes  Nodes where nodes.nodeid=550

                          union

                          (select Nodes.NodeiD as Name,Nodes.Status from orion.nodes  Nodes where nodes.nodeid=163)

                          custom table

                           

                          Custom Query

                          but in SWQL studio it works fine:

                          Where is the issue?

                          Another Question: Does html tag works in SWQL when run union/union all ?

                          /msarkar

                            • Re: Custom SWQL resource
                              tdanner

                              There's a bug (internal number CORE-6278) in the report builder that causes it to mangle custom SWQL queries with UNION clauses. If the Custom Query Resource can meet your needs, you could use that instead. It does not suffer from this issue.

                               

                              Custom Query Resource does not support embedding HTML markup in the query results.

                              1 of 1 people found this helpful
                                • Re: Custom SWQL resource
                                  mesverrum

                                  I loved the trick you used here with the unions, but since I can never leave well enough alone I built it up some more. Pulled in the relationships between interfaces and their individual IP addresses when that info is available, tried to make it a bit easier to spot when we are looking at parent vs child objects, expanded on the status info, made your sorting mechanism invisible, and added a search (in case you guys haven't noticed this yet, EVERYTHING in Orion always needs to be searchable).

                                   

                                   

                                  SELECT     
                                      n.caption as Node,     
                                      '/netperfmon/images/vendors/' + n.VendorIcon as [_IconFor_Node],
                                      n.DetailsUrl as [_LinkFor_Node],
                                      n.ipaddress as IP_Address,     
                                      n.ObjectSubType as Polling_Method,     
                                      n.StatusDescription as Status,    
                                      '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Status],   
                                      '' as [ ],
                                      n.nodeid as [_linkfor_ ]
                                  FROM orion.nodes n    
                                  --Where n.caption like '%${SEARCH_STRING}%'
                                    
                                  UNION ALL (    
                                  SELECT     
                                        
                                      (' -   '+ I.Caption) as Interface,    
                                      '/netperfmon/images/interfaces/' + i.InterfaceIcon AS [_IconFor_Interface],      
                                      i.DetailsUrl as [_LinkFor_Interface],
                                      CASE WHEN i.IPAddress.IPAddress is not null then i.IPAddress.IPAddress
                                      ELSE ip.IPAddress
                                      END AS IP_Address,      
                                  NULL as Polling_Method,     
                                  concat(tostring(InPercentUtil),'% RX Utilization, ',tostring(OutPercentUtil),'% TX Utilization') as Status,  
                                      '/Orion/images/StatusIcons/Small-' + i.Statusicon AS [_IconFor_Status],  
                                      '' as [ ],
                                      i.nodeid as [_linkfor_ ]
                                  
                                  
                                  FROM Orion.NPM.Interfaces I  
                                  left join orion.NodeIPAddresses ip on ip.NodeID=i.NodeID and ip.IPAddressType='IPv4' and ip.interfaceindex is null
                                  --Where i.node.caption like '%${SEARCH_STRING}%'
                                  )  
                                    
                                    
                                  UNION ALL (    
                                  SELECT     
                                      (' -      '+V.Caption) as Volume,    
                                      '/NetPerfMon/images/Volumes/' + V.VolumeTypeIcon AS [_IconFor_Volume],   
                                      v.DetailsUrl as [_LinkFor_Volume],
                                      NULL as IP_Address,     
                                      NULL as Polling_Method,     
                                      concat(round(v.VolumePercentUsed,0),'% of ',round(v.volumespaceavailable/1073741824,0),' GB total') as Status,   
                                      '/Orion/images/StatusIcons/Small-' + v.StatusLED AS [_IconFor_Status], 
                                      '' as [ ],
                                      v.nodeid as [_linkfor_ ]
                                  
                                  
                                  FROM Orion.Volumes V    
                                  --Where v.node.caption like '%${SEARCH_STRING}%'
                                  )    
                                    
                                    
                                  ORDER bY [_linkfor_ ], node desc

                                   

                                   

                                  -Marc Netterfield

                                      Loop1 Systems: SolarWinds Training and Professional Services

                                  2 of 2 people found this helpful
                                    • Re: Custom SWQL resource
                                      msarkar

                                      mesverrum Is it possible to get value in scale bar like below?

                                      /Msarkar

                                        • Re: Custom SWQL resource
                                          mesverrum

                                          Not with SWQL, it only supports displaying plain text, icons, and making the text into a clickable link.  If you were reallllllly dedicated you could create an icon of a bar chart for each percentage, so something like bar-1.gif through bar-100.gif and use them in the iconfor section but it feels overkill

                                          2 of 2 people found this helpful
                                            • Re: Custom SWQL resource
                                              msarkar

                                              Hi

                                              How can i get sum two different rows in SQL or SWQL like following? I need a query where row1 value will be added with row2 value and generate a new row with different name but same date and keep other Node data as well.

                                              /msarkar

                                                • Re: Custom SWQL resource
                                                  tdanner

                                                  SWQL supports the "GROUP BY GROUPING SETS" feature for doing these kinds of subtotal/grand total rows. Here's an explanation of it from a SQL perspective - the SWQL version works the same way. GROUPING SETS in SQL Server 2008 – Craig Freedman's SQL Server Blog

                                                    • Re: Custom SWQL resource
                                                      msarkar

                                                      Thanks.

                                                       

                                                      But It is partially solve the issue, it only gives the aggregation of two different rows but other two fields come with 'Null' where i like to have DateTime and custom name. Here it is what i have got.

                                                       

                                                       

                                                      Also, for multiple data values on same date is not working, it gives a total at the end. The query does not differentiate different times.

                                                       

                                                       

                                                      /msarkar

                                                        • Re: Custom SWQL resource
                                                          tdanner

                                                          Can you post the query you are working on?

                                                            • Re: Custom SWQL resource
                                                              msarkar

                                                              For frist one, i ran the following:

                                                               

                                                              select cps.DateTime as DateTime,n.caption as NodeName,sum(cps.status+0) as Value

                                                              from Nodes n inner join custompollerassignment cpa on cpa.nodeid=n.nodeid

                                                              inner join custompollerstatus cps on cps.custompollerassignmentid=cpa.custompollerassignmentid

                                                              where cpa.custompollerassignmentid='d7872865-e056-43b5-b9c3-76350f7f5a97'

                                                              or cpa.custompollerassignmentid='759dd958-9b08-462e-b87f-e911a4b9af40'

                                                              group by grouping sets((n.caption,cps.datetime),())

                                                               

                                                              Whereas for second one, i ran the following, just change the table:

                                                               

                                                              select cpd.DateTime as DateTime,n.caption as NodeName,sum(cpd.status+0) as Value

                                                              from Nodes n inner join custompollerassignment cpa on cpa.nodeid=n.nodeid

                                                              inner join custompollerstatistics_detail cpd on cpd.custompollerassignmentid=cpa.custompollerassignmentid

                                                              where (cpa.custompollerassignmentid='d7872865-e056-43b5-b9c3-76350f7f5a97'

                                                              or cpa.custompollerassignmentid='759dd958-9b08-462e-b87f-e911a4b9af40') and cpd.datetime > DateAdd(mi,-10,getutcdate())

                                                              group by grouping sets((n.caption,cpd.datetime),())

                                                               

                                                              /msarkar

                                                                • Re: Custom SWQL resource
                                                                  tdanner

                                                                  If you want a subtotal per datetime, you should change the GROUP BY clause like this:

                                                                   

                                                                  GROUP BY GROUPING SETS ( (n.caption, cpd.datetime), (cpd.datetime) )

                                                                   

                                                                  To have the NodeName column show "Total" instead of NULL on the subtotal row, replace "n.caption as NodeName" in the SELECT clause with this:

                                                                   

                                                                  ISNULL(n.caption, 'Total') AS NodeName

                                                                  1 of 1 people found this helpful
                                                                    • Re: Custom SWQL resource
                                                                      msarkar

                                                                      First one work, i got Total, but is there any way to get datetime as well.

                                                                       

                                                                      Second one, now query extracts data individually. Here is the output:

                                                                       

                                                                       

                                                                      here is query

                                                                       

                                                                      select

                                                                      cpd.DateTime as DateTime,n.caption as NodeName,sum(cpd.status+0) as Value

                                                                       

                                                                      from Nodes n inner join custompollerassignment cpa on cpa.nodeid=n.nodeid

                                                                      inner join custompollerstatistics_detail cpd on cpd.custompollerassignmentid=cpa.custompollerassignmentid

                                                                      where (cpa.custompollerassignmentid='d7872865-e056-43b5-b9c3-76350f7f5a97'

                                                                      or cpa.custompollerassignmentid='759dd958-9b08-462e-b87f-e911a4b9af40') and cpd.datetime > DateAdd(mi,-10,getutcdate())

                                                                      group by grouping sets((n.caption,cpd.datetime),(cpd.datetime))

                                                                        • Re: Custom SWQL resource
                                                                          tdanner

                                                                          I think what you are seeing is that NodeA and NodeB are not polled at exactly the same time. The date, hour, and minute are the same, but somewhere in the seconds or fraction of a second the times are different so they don't actually group together. We can fix that by actually truncating cpd.datetime to the minute. In SQL you can do that by replacing cpd.datetime with

                                                                           

                                                                          dateadd(mi, datediff(mi, 0, cpd.datetime), 0)

                                                                           

                                                                          You would do this in both the SELECT and GROUP BY clauses.

                                                                          1 of 1 people found this helpful
                                                                        • Re: Custom SWQL resource
                                                                          mesverrum

                                                                          It's bits like this thread that remind me I am just a SWQL hack compared to you guys.