Hi,
Cross posting as didn't get a response in NPM area:
Custom SWQL resource
Thanks
Stuart
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.
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.
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 KindFROM orion.nodes nUNION 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 KindFROM 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 KindFROM Orion.Volumes V)ORDER Y NodeID, Kind
tdannerstuartwhyte
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
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).
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?
/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.
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
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.
Can you post the query you are working on?
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
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),())
