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

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?

Find more posts tagged with

Accepted answers

planglois

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

stuartd

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.

All comments

planglois

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

stuartd

shyde

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!

stuartd

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.