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

Need help in SWQL

Need help to merge data in following SWQL query

SELECT COUNT(n.DisplayName) as Down_Cybernet_Fiber,
n.CustomProperties.Media_Type_1,
n.CustomProperties.Media_Type_2 ,
n.CustomProperties.Media_Type_3

FROM Orion.Nodes n

WHERE ( n.CustomProperties.SP1 LIKE 'cybernet%' or n.CustomProperties.SP2 LIKE 'cybernet%' or n.CustomProperties.SP3 LIKE 'cybernet%' )

and Status = 2

GROUP by n.CustomProperties.Media_Type_1 , n.CustomProperties.Media_Type_2 , n.CustomProperties.Media_Type_3

its sowing output as

need to get output as

Cybernet	Media	Total	Down
	Fiber	500	20
	3G	200	20
	Radio	900	30
	LTE	50	1

Find more posts tagged with

Accepted answers

sum_giais

Updated the original query to require that Media_Type_1, Media_Type_2, or Media_Type_3 has to not be null. Give it a go.

All comments

sum_giais

I'm not sure if this query will work as I don't have properties similar to this to test it on but I'm hoping this is close to what you're looking for.

Give it a go an let us know the results.

SELECT
     m.Media_Type
    ,COUNT(m.NodeID) AS [Total_Cybernet_Fiber]
    ,COUNT(CASE WHEN m.Status = 2 THEN 1 END) AS [Down_Cybernet_Fiber]
FROM ( 
SELECT
     n.NodeID
    ,CASE WHEN n.CustomProperties.Media_Type_1 IS NOT NULL THEN n.CustomProperties.Media_Type_1
          WHEN n.CustomProperties.Media_Type_2 IS NOT NULL THEN n.CustomProperties.Media_Type_2
          WHEN n.CustomProperties.Media_Type_3 IS NOT NULL THEN n.CustomProperties.Media_Type_3
     END AS [Media_Type]
    ,n.Status
FROM Orion.Nodes AS n
WHERE ( n.CustomProperties.SP1 LIKE 'cybernet%' OR n.CustomProperties.SP2 LIKE 'cybernet%' OR n.CustomProperties.SP3 LIKE 'cybernet%' )
AND ( n.CustomProperties.Media_Type_1 IS NOT NULL OR n.CustomProperties.Media_Type_2 IS NOT NULL OR n.CustomProperties.Media_Type_3 IS NOT NULL ) ) AS m
GROUP BY m.Media_Type

makecrazy

Hi Sum_gails

thanks for urs reply , getting this error while running urs provided query

sum_giais

I had a typo... the very first "MediaType" should have been "Media_Type", corrected my query above.

makecrazy

Dear its works , thanks.

can Null be removed from out put