SWQL Query Assist please...?

 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 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!