This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom SWQL resource

Hi,

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

Custom SWQL resource

Thanks

Stuart

  • 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

  • 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

  • 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

  • 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

  • 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

    pastedImage_0.png

    Custom Query

    pastedImage_6.png

    but in SWQL studio it works fine:

    pastedImage_1.png

    Where is the issue?

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

    /msarkar

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

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

    pastedImage_2.png

    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

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

    pastedImage_1.png

    /Msarkar

  • 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

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

    pastedImage_0.png

    /msarkar