4 Replies Latest reply on Feb 12, 2014 3:49 AM by stuartwhyte

    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