SWQL format

makecrazy

Hi 

Mentioned SWQL query is giving output like

SELECT
     m.Media_Type

    ,COUNT(CASE WHEN m.Status = 2 THEN 1 END) AS [Down]

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 'ptcl%' OR n.CustomProperties.SP2 LIKE 'ptcl%' OR n.CustomProperties.SP3 LIKE 'ptcl%' ))  AS m

GROUP BY m.Media_Type
ORDER BY [Down] DESC ;

need yours help to get output in mentioned format 

Media_Type	Down	Total Down
DSL	46	74
Fiber	15
VSAT	10
DRS	2
LTE	1

OR

it will be great if i can get result in this format 

Service Provider	Media	# of Links DOWN	Total
PTCL	Fiber	5	43
	DSL	30
	DRS	3
	LTE	0
	3G/4G
	VSAT	5
SuperNet	VSAT	17	33
	VSAT Combo	7
	LTE	9
JAZZ	Fiber	7	26
	PMP	11
	3G/4G	7
	VSAT	1
WATEEN	Fiber	14	19
	Wimax	0
	3G/4G	0
	DSL	5
CYBERNET	Fiber	2	17
	Wimax	0
	Radio	6
	3G/4G	7
	LTE	2
Multinet	Fiber	5	6
	VSAT	1
NayaTel	Fiber	2	2
Grand Total			146

cnorborg

You probably can, although without your custom properties it would be difficult for me to test.   Just add in your service provider as one column and group by it.   The big caveat is if you're doing this as a web resource I'm pretty sure it will put the service provider on each line.   But, if you do this in report writer, I think it will look like what you're looking for.   Note: if you do it in report writer, you can do the grouping via its GUI rather than putting it in the query.