Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Do the SWQL column macro's support the use of a string

Hi All,

I'm trying to list all the volumes for a group of nodes for a business unit on a single row, With my script I get a new row per volume which takes up valuable real estate in the dashboard.

If the column macro END AS [Volume], [_linkfor_Volume] and [_IconFor_Volume] supported the use of a string I think it would do what I'm after which is create a new column for each volume, e.g. END AS [CONCAT(substring(n.Volumes.Caption, 1, 1)]

I have looked but have been unable to find documentation to determine if this is supported and I just have an incorrect syntax.

Does anyone know if this is possible or an allowed method of getting the layout I'm after.

select c.container.Name as [Group]

,c.container.DetailsUrl as [_linkfor_Group]

, n.Caption as Node

, c.detailsurl as [_linkfor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]

,CASE

WHEN n.Volumes.Caption like CONCAT(substring(n.Volumes.Caption, 1, 1),'%') then CONCAT(substring(n.Volumes.Caption, 1, 1),': ', round(n.Volumes.VolumePercentUsed,0),'% of ',round(n.Volumes.volumeSize/1073741824,0),' GB total')

END AS [Volume]

,concat('/Orion/NetPerfMon/VolumeDetails.aspx&NetObject=V:',n.Volumes.VolumeID) as [_linkfor_Volume]

,CASE

WHEN n.Volumes.VolumePercentUsed>= 95 THEN '/Orion/images/StatusIcons/Small-Down.gif'

WHEN n.Volumes.VolumePercentUsed< 95 and n.Volumes.VolumePercentUsed >= 85 THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN n.Volumes.VolumePercentUsed< 85 THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Volume]

from orion.nodes n

join orion.nodescustomproperties cp on cp.nodeid=n.nodeid

join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid

left join orion.apm.application a on a.nodeid=n.nodeid

where MemberEntityType like 'orion.nodes'

and c.container.Name like 'WBU Production Servers'

order by n.caption

Thanks

Craig Boyce

Find more posts tagged with

Accepted answers

tdanner

In general, there's no way to combine a set of rows into a single row.

Also, I think you may be misreading the "CASE ... END" syntax. In SWQL it works the same way as in SQL.

All comments

tdanner

In general, there's no way to combine a set of rows into a single row.

Also, I think you may be misreading the "CASE ... END" syntax. In SWQL it works the same way as in SQL.

mesverrum

That SWQL syntax looks remarkably familiar to me

In any case, I wrote a SWQL report that uses UNIONS to get around the duplication that you see when you join your nodes to volumes.

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_ ],
    '0' as [_linkfor_order]
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],
    i.IPAddress.IPAddress,  
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_ ],
    '1' as [_linkfor_order]




FROM Orion.NPM.Interfaces I  
--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_ ],
    '2' as [_linkfor_order]


FROM Orion.Volumes V    
--Where v.node.caption like '%${SEARCH_STRING}%'
)    


UNION ALL (  
SELECT     
    (' - '+A.Name) as Application,    
    '/orion/apm/images/statusicons/Small-App-Blank.gif' AS [_IconFor_Application],   
    a.DetailsUrl as [_LinkFor_Volume],
    NULL as IP_Address,     
    NULL as Polling_Method,     
    a.StatusDescription as Status,   
    '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Status], 
    '' as [ ],
    a.nodeid as [_linkfor_ ],
    '3' as [_linkfor_order]


FROM Orion.APM.Application a    
--Where A.node.caption like '%${SEARCH_STRING}%'
)    
  


  
ORDER BY [_linkfor_ ], [_linkfor_order], node

cboyce

Thanks Tdanner,

I was hoping for functionality that does not exist, I'm new to SQL query's so I'm still learning my way around.

Craig

cboyce

Thanks mesverrum,

I borrowed a few bits and pieces of code from scripts in thwack, Obviously some was from one of your posts.

Thanks for the information about using the Union operator, I'll look into how that can help me.

Craig