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
Store

Need help in SWQL Query

Can one help me to convert following SQL to SWQL

SELECT
*
FROM (SELECT
SUM(CASE
WHEN NodesCustomProperties.ATM_Branches = 'ATM Branches' THEN 1
ELSE 0
END) AS Total_ATM_BRanches
,SUM(CASE
WHEN NodesCustomProperties.BRANCH_TYPE = 'NON-ATM' THEN 1
ELSE 0
END) AS Total_NON_ATM_BRanches
,SUM(CASE
WHEN NodesCustomProperties.BRANCH_TYPE = 'NON-ATM' OR
NodesCustomProperties.ATM_Branches = 'ATM Branches' THEN 1
ELSE 0
END) AS Total_Branches
,NodesCustomProperties.City
FROM dbo.NodesCustomProperties
GROUP BY NodesCustomProperties.City) datata
WHERE datata.Total_ATM_BRanches <> 0
AND datata.Total_NON_ATM_BRanches <> 0
ORDER BY datata.City

Result of SQL query:

Find more posts tagged with

Accepted answers

Seashore

Hi @makecrazy

I added the first "down count". The next is for you to add, see it as practice and "homework" :-)

SELECT
    datata.Total_ATM_BRanches AS [Total ATM Branches]
    ,datata.Total_ATM_BRanches_Unmanaged AS [Unmanaged Count ATM Branches]
    ,datata.Total_ATM_BRanches_Down AS [Down Count ATM Branches]
    ,datata.Total_NON_ATM_BRanches AS [Total Non-ATM Branches]
    ,datata.Total_NON_ATM_BRanches_Unmanaged AS [Total Count Non-ATM Branches]
    ,datata.Total_branches AS [Total Branches]
    ,datata.City AS [City]
FROM (
    SELECT
        SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches
        ,SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' AND C.Node.UnManaged=TRUE   THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches_Unmanaged
        ,SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' AND C.Node.StatusDescription='Down'   THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches_Down
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' AND C.Node.UnManaged=TRUE THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches_Unmanaged
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' OR C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_Branches
        ,C.City
        FROM Orion.NodesCustomProperties AS C
    GROUP BY C.City) AS datata

WHERE 
    datata.Total_ATM_BRanches <> 0
    AND datata.Total_NON_ATM_BRanches <> 0
ORDER BY datata.City

All comments

Seashore

Hi, as I don't have your custom properties I couldn't test this but I think it should work.

SELECT
    datata.Total_ATM_BRanches AS [Total ATM Branches]
    ,datata.Total_NON_ATM_BRanches AS [Total Non-ATM Branches]
    ,datata.Total_branches AS [Total Branches]
    ,datata.City AS [City]
FROM (
    SELECT
        SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' OR C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_Branches
        ,C.City
        FROM Orion.NodesCustomProperties AS C
    GROUP BY C.City) AS datata

WHERE 
    datata.Total_ATM_BRanches <> 0
    AND datata.Total_NON_ATM_BRanches <> 0
ORDER BY datata.City

makecrazy

Dear Seashore

thanks it works perfectly.

can u add also unmanage count per city in front of " Total ATM Branhes " and " Non-ATM Branches"

Total ATM Branches	Unmanaged Cout ATM Branches	Total Non-ATM Branches	Unmanaged Cout Non-ATM Branches	Total_Branches	city
58	5	6	8	64	Bahawalpur
72	4	27	3	99	Faisalabad
68	11	6	7	74	Gujranwala

Seashore

@makecrazy, try this:

SELECT
    datata.Total_ATM_BRanches AS [Total ATM Branches]
    ,datata.Total_ATM_BRanches_Unmanaged AS [Unmanaged Count ATM Branches]
    ,datata.Total_NON_ATM_BRanches AS [Total Non-ATM Branches]
    ,datata.Total_NON_ATM_BRanches_Unmanaged AS [Total Count Non-ATM Branches]
    ,datata.Total_branches AS [Total Branches]
    ,datata.City AS [City]
FROM (
    SELECT
        SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches
        ,SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' AND C.Node.UnManaged=TRUE   THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches_Unmanaged
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' AND C.Node.UnManaged=TRUE THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches_Unmanaged
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' OR C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_Branches
        ,C.City
        FROM Orion.NodesCustomProperties AS C
    GROUP BY C.City) AS datata

WHERE 
    datata.Total_ATM_BRanches <> 0
    AND datata.Total_NON_ATM_BRanches <> 0
ORDER BY datata.City

Edit: I had missed one "," - try again.

makecrazy

Seashore

getting this error

KMSigma.SWI

Does this work for you?

SELECT SUM(CASE 
               WHEN [Nodes].CustomProperties.ATM_Branches = 'ATM Branches'
                    THEN 1
               ELSE 0
               END) AS Total_ATM_BRanches
     , SUM(CASE 
               WHEN [Nodes].CustomProperties.Branch_Type = 'NON-ATM'
                    THEN 1
               ELSE 0
               END) AS Total_NON_ATM_BRanches
     , SUM(CASE 
               WHEN [Nodes].CustomProperties.Branch_Type = 'NON-ATM'
                    OR [Nodes].CustomProperties.Branch_Type = 'ATM Branches'
                    THEN 1
               ELSE 0
               END) AS Total_Branches
     , [Nodes].CustomProperties.City
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].CustomProperties.City
HAVING (
          SUM(CASE 
                    WHEN [Nodes].CustomProperties.ATM_Branches = 'ATM Branches'
                         THEN 1
                    ELSE 0
                    END)
          ) <> 0
     AND (
          SUM(CASE 
                    WHEN [Nodes].CustomProperties.Branch_Type = 'NON-ATM'
                         THEN 1
                    ELSE 0
                    END)
          ) <> 0

makecrazy

its not showing right total count.

actual count:

and urs provided script showing

also need to have unmanage count per city in front of " Total ATM Branhes " and " Non-ATM Branches"

like:

Seashore

Did you try this one?

SELECT
    datata.Total_ATM_BRanches AS [Total ATM Branches]
    ,datata.Total_ATM_BRanches_Unmanaged AS [Unmanaged Count ATM Branches]
    ,datata.Total_NON_ATM_BRanches AS [Total Non-ATM Branches]
    ,datata.Total_NON_ATM_BRanches_Unmanaged AS [Total Count Non-ATM Branches]
    ,datata.Total_branches AS [Total Branches]
    ,datata.City AS [City]
FROM (
    SELECT
        SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches
        ,SUM(CASE
            WHEN C.ATM_Branches = 'ATM Branches' AND C.Node.UnManaged=TRUE   THEN 1
            ELSE 0
            END) AS Total_ATM_BRanches_Unmanaged
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' AND C.Node.UnManaged=TRUE THEN 1
            ELSE 0
            END) AS Total_NON_ATM_BRanches_Unmanaged
        ,SUM(CASE
            WHEN C.BRANCH_TYPE = 'NON-ATM' OR C.ATM_Branches = 'ATM Branches' THEN 1
            ELSE 0
            END) AS Total_Branches
        ,C.City
        FROM Orion.NodesCustomProperties AS C
    GROUP BY C.City) AS datata

WHERE 
    datata.Total_ATM_BRanches <> 0
    AND datata.Total_NON_ATM_BRanches <> 0
ORDER BY datata.City

makecrazy

Seashore

yes its works correctly.

one more and last thing can u add per city down count in it , like

Total ATM Branches	Unmanaged Cout ATM Branches	Down Count ATM Branches	Total Non-ATM Branches	Unmanaged Cout Non-ATM Branches	Down Cout Non-ATM Branches	Total_Branches	city
58	5	3	6	8	4	64	Bahawalpur
72	4	1	27	3	6	99	Faisalabad
68	11	8	6	7	11	74	Gujranwala