SWQL Query Assist please...?

I have the following query:

SELECT
S1.Node
,S1.DetailsUrl AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/small-' + ToString(S1.StatusIcon) AS [_IconFor_Node]
,S1.IP
,S1.DetailsUrl AS [_LinkFor_IP]
--,S1.Area
,S1.MachineType
,S1.Version
,S1.Serial
,S1.LastBoot
,S1.LoginStatus
,S1.LastRunning
,S2.LastStartup
FROM(
	SELECT
	MAX(ca.DownloadTime) AS LastRunning
	,ca.NodeProperties.CoreNodeID
	,ca.NodeProperties.Nodes.Caption AS Node
	,ca.NodeProperties.Nodes.StatusIcon
	,ca.NodeProperties.Nodes.DetailsUrl
	,ca.NodeProperties.Nodes.IPAddress AS IP
	,ca.NodeProperties.Nodes.MachineType
	--,ca.NodeProperties.Nodes.
	,CASE
		WHEN ca.NodeProperties.LoginStatus LIKE '%Cannot Log into Device : bad password%' THEN 'Error: Bad Password'
		WHEN ca.NodeProperties.LoginStatus LIKE '%Connection Refused by%' THEN 'Connection Refused'
		ELSE ca.NodeProperties.LoginStatus
	END AS LoginStatus
	,ca.NodeProperties.Nodes.IOSVersion AS Version
	,ca.NodeProperties.Nodes.IOSImage AS Serial
	,ca.NodeProperties.Nodes.LastBoot
--	,ca.NodeProperties.Nodes.CustomProperties.Area
	--,ca.NodeProperties.Nodes.CustomProperties.
	FROM NCM.ConfigArchive AS ca
	WHERE ca.ConfigType='Running'
--	AND ca.NodeProperties.Nodes.Vendor LIKE '%Juniper%'
	GROUP BY ca.NodeProperties.CoreNodeID,ca.NodeProperties.LoginStatus,ca.NodeProperties.Nodes.Caption,ca.NodeProperties.Nodes.NodeID,ca.NodeProperties.Nodes.IPAddress,ca.NodeProperties.Nodes.MachineType,ca.NodeProperties.Nodes.IOSVersion,ca.NodeProperties.Nodes.IOSImage,ca.NodeProperties.Nodes.LastBoot,ca.NodeProperties.Nodes.StatusIcon,ca.NodeProperties.Nodes.DetailsUrl
	) AS S1

INNER JOIN(
	SELECT
	MAX(ca.DownloadTime) AS LastStartup
	,ca.NodeProperties.CoreNodeID
	FROM NCM.ConfigArchive AS ca
	WHERE ca.ConfigType='Startup'
--	AND ca.NodeProperties.Nodes.Vendor LIKE '%Juniper%'
	GROUP BY ca.NodeProperties.CoreNodeID
	) AS S2 ON S1.CoreNodeID=S2.CoreNodeID

What I'd really like to do is shrink the report to just devices that don't have a backup in the last 4 weeks based on these two fields: S1.LastRunning and/or S2.LastStartup.

Can anyone help?

Parents
  •  I believe I was able to get that done using the following query:

    SELECT 
        S1.Node,
        S1.DetailsUrl AS [_LinkFor_Node],
        '/Orion/images/StatusIcons/small-' + ToString(S1.StatusIcon) AS [_IconFor_Node],
        S1.IP,
        S1.DetailsUrl AS [_LinkFor_IP],
        --S1.Area,
        S1.MachineType,
        S1.Version,
        S1.Serial,
        S1.LastBoot,
        S1.LoginStatus,
        S1.LastRunning,
        S2.LastStartup
    FROM(
        SELECT MAX(ca.DownloadTime) AS LastRunning,
            ca.NodeProperties.CoreNodeID,
            ca.NodeProperties.Nodes.Caption AS Node,
            ca.NodeProperties.Nodes.StatusIcon,
            ca.NodeProperties.Nodes.DetailsUrl,
            ca.NodeProperties.Nodes.IPAddress AS IP,
            ca.NodeProperties.Nodes.MachineType,
            --ca.NodeProperties.Nodes
            CASE
                WHEN ca.NodeProperties.LoginStatus LIKE '%Cannot Log into Device : bad password%' THEN 'Error: Bad Password'
                WHEN ca.NodeProperties.LoginStatus LIKE '%Connection Refused by%' THEN 'Connection Refused'
                ELSE ca.NodeProperties.LoginStatus
            END AS LoginStatus,
            ca.NodeProperties.Nodes.IOSVersion AS Version,
            ca.NodeProperties.Nodes.IOSImage AS Serial,
            ca.NodeProperties.Nodes.LastBoot 
            -- ca.NodeProperties.Nodes.CustomProperties.Area,
            -- ca.NodeProperties.Nodes.CustomProperties
        FROM NCM.ConfigArchive AS ca
        WHERE ca.ConfigType = 'Running' 
        AND ca.DownloadTime <=ADDWEEK(-4,GETDATE()) 
    -- AND ca.NodeProperties.Nodes.IPAddress = '10.254.16.34'
        --	AND ca.NodeProperties.Nodes.Vendor LIKE '%Juniper%'
        GROUP BY ca.NodeProperties.CoreNodeID,
            ca.NodeProperties.LoginStatus,
            ca.NodeProperties.Nodes.Caption,
            ca.NodeProperties.Nodes.NodeID,
            ca.NodeProperties.Nodes.IPAddress,
            ca.NodeProperties.Nodes.MachineType,
            ca.NodeProperties.Nodes.IOSVersion,
            ca.NodeProperties.Nodes.IOSImage,
            ca.NodeProperties.Nodes.LastBoot,
            ca.NodeProperties.Nodes.StatusIcon,
            ca.NodeProperties.Nodes.DetailsUrl
        ) AS S1
        INNER JOIN(
            SELECT 
                MAX(ca.DownloadTime) AS LastStartup,
                ca.NodeProperties.CoreNodeID
            FROM NCM.ConfigArchive AS ca
            WHERE ca.ConfigType = 'Startup' 
           -- AND ca.NodeProperties.Nodes.IPAddress = '10.254.16.34'
           AND ca.DownloadTime <=ADDWEEK(-4,GETDATE()) 
            --	AND ca.NodeProperties.Nodes.Vendor LIKE '%Juniper%'
            GROUP BY ca.NodeProperties.CoreNodeID
        ) AS S2 ON S1.CoreNodeID = S2.CoreNodeID 

    Basically, the only thing added is "AND ca.DownloadTime <=ADDWEEK(-4,GETDATE())" in your two WHERE clauses.

    Originally, I thought I would be able to achieve that by doing a subquery (a SELECT on your whole query), but for some reason, it was not working on columns with Aliases. Instead of removing aliases (to place them in the final query, I went down the lazy path and simply added the two lines. Just keep in mind you would have to modify both if you change your query scope.

    Cheers! :)

  • Thanks Philip - I'd forgotten I'd asked this question :) 

    At this time I was getting so frustrated that I think I probably asked very similar questions several times, and was getting precisely nowhere. However, I have a lovely little script now that eventually I corralled together from various places and people.

    It does precisely what we need, which is to look at our backups and then report on only those devices that are operationally up and reported an error. It then takes a human approx 2 minutes to look at the output and see which devices have genuinely failed based on the date in last running or last startup.

    That code is:

    SELECT
    NCP.Client
    ,S1.Node
    ,N.StatusDescription
    ,N.Status
    --,S1.DetailsUrl AS [_LinkFor_Node]
    --,'/Orion/images/StatusIcons/small-' + ToString(S1.StatusIcon) AS [_IconFor_Node]
    ,S1.IP
    ,S1.DetailsUrl AS [_LinkFor_IP]
    --,S1.Area
    ,S1.MachineType
    ,S1.Version
    ,S1.Serial
    ,S1.LastBoot
    ,S1.LoginStatus
    ,S1.LastRunning
    ,S2.LastStartup
    
    FROM(
    	SELECT
    	MAX(ca.DownloadTime) AS LastRunning
    	,ca.NodeProperties.CoreNodeID
    	,ca.NodeProperties.Nodes.Caption AS Node
    	,ca.NodeProperties.Nodes.StatusIcon
    	,ca.NodeProperties.Nodes.DetailsUrl
    	,ca.NodeProperties.Nodes.IPAddress AS IP
    	,ca.NodeProperties.Nodes.MachineType
    	--,ca.NodeProperties.Nodes.
    	,CASE
    		WHEN ca.NodeProperties.LoginStatus LIKE '%Cannot Log into Device : bad password%' THEN 'Error: Bad Password'
    		WHEN ca.NodeProperties.LoginStatus LIKE '%Connection Refused by%' THEN 'Connection Refused'
    		ELSE ca.NodeProperties.LoginStatus
    	END AS LoginStatus
    	,ca.NodeProperties.Nodes.IOSVersion AS Version
    	,ca.NodeProperties.Nodes.IOSImage AS Serial
    	,ca.NodeProperties.Nodes.LastBoot
        ,ca.NodeID
    
    	FROM NCM.ConfigArchive AS ca
    	WHERE ca.ConfigType='Running'
    	GROUP BY ca.NodeProperties.CoreNodeID,ca.NodeProperties.LoginStatus,ca.NodeProperties.Nodes.Caption,ca.NodeProperties.Nodes.NodeID,ca.NodeProperties.Nodes.IPAddress,ca.NodeProperties.Nodes.MachineType,ca.NodeProperties.Nodes.IOSVersion,ca.NodeProperties.Nodes.IOSImage,ca.NodeProperties.Nodes.LastBoot,ca.NodeProperties.Nodes.StatusIcon,ca.NodeProperties.Nodes.DetailsUrl
    	) AS S1
    INNER JOIN(
    	SELECT
    	MAX(ca.DownloadTime) AS LastStartup
    	,ca.NodeProperties.CoreNodeID
    	FROM NCM.ConfigArchive AS ca
    	WHERE ca.ConfigType='Startup'
    
    AND ca.NodeProperties.LoginStatus NOT LIKE '%Login OK%'
    	GROUP BY ca.NodeProperties.CoreNodeID
    	) AS S2 ON S1.CoreNodeID=S2.CoreNodeID
    INNER JOIN Orion.Nodes AS N ON s1.CoreNodeID=N.NodeID
    LEFT OUTER JOIN Orion.NodesCustomProperties AS NCP ON n.NodeID=NCP.NodeID
    WHERE N.Status NOT IN ('2', '4', '9', '11', '12')


    I will take a look at your version though and see if it provides anything we've missed.

  • I tried the code you provided and it came up as an invalid query till I removed NCP.Client 

    Not sure if this is expected or not. It did seem to work and flagged to my attention one node that I had to fix the AAA configuration on in order to resolve the configuration not being backed up - so nice report and thank you for providing it!

  • Apologies I should have drawn attention to that. That's a call to a custom property so we can easily associate/identify the kit to a specific client.

  • All good thanks for verifying!

Reply Children
No Data