Create an SWQL query to report last backup status of all configured nodes on NCM?

Hi All,

I need to generate a report on the backup status of all configured nodes on NCM. I am trying to use the built-in 'Backup Status of Running Config' report but it does not give me details on all nodes.

I require the list of all configured devices on NCM, their Display Name, IP address, Location, Model, Chassis ID and Last Backup date/time. If the device is not set up for backup or backup has failed, the device should still be included in the report with no/last backup date listed.

I have very limited SQL skills, so any assistance with building queries will be much appreciated!

Many thanks for considering my request.

Find more posts tagged with

Accepted answers

All comments

stuartd

Hi, and welcome to the glory and pain that is reporting with SQL/SWQL in SolarWinds, and where nothing comes as easy as you hoped or were sold it would!

So, I have been through this pain, and with a lot of blood, sweat and swearing I achieved both of your asks though they are not in a single report. That said, you could create two logic blocks in a report and you could see the 'latest state' on one tab, and then the 'devices not being backed up' in a second.

My only assumption in all this is that you already know how to build a custom report, and I have to warn that my SWQL is not the prettiest, and may not be the most efficient but it works for us. On that note - these are all SWQL reports, not SQL.

Report One - state of backups - you can add or remove fields as you see fit so for e.g. mine doesn't include ChassisID but then a lot of our kit is not Cisco so that is sort of irrelevant.

I've left in a couple of custom properties we use (that you almost certainly won't have) so you can see, but they can equally be removed - they are REMarked out using the --

-- Date: 05/May/2021
-- Version: 1
-- Compatibility: Orion Platform 2021
-- Author: Stuart Dyckhoff (Thwack: @stuartd)

SELECT 
--   ncmnodes.Client
--	,ncmnodes.Node_In_BAU
	,OrionNodes.Caption
	,OrionNodes.DetailsUrl
	,OrionNodes.STATUS
	,OrionNodes.ChildStatus
	,OrionNodes.Vendor
	,OrionVendors.Icon
	,OrionNodes.IP_Address
	,OrionNodes.IPAddressType
	,OrionNodes.MachineType
	,max(NcmConfigArchive.DownloadTime) AS [Latest]
	,NcmConfigArchive.ConfigType

FROM NCM.NodeProperties AS NcmNodeProperties
INNER JOIN Orion.Nodes AS OrionNodes ON NcmNodeProperties.CoreNodeID = OrionNodes.NodeID
INNER JOIN Orion.Vendors AS OrionVendors ON OrionNodes.Vendor = OrionVendors.Name
LEFT JOIN NCM.ConfigArchive AS NcmConfigArchive ON NcmNodeProperties.NodeID = NcmConfigArchive.NodeID
INNER JOIN Orion.NodesCustomProperties AS OrionNodesCustomProperties ON OrionNodes.NodeID = OrionNodesCustomProperties.NodeID
INNER JOIN ncm.nodes AS ncmnodes ON ncmnodes.corenodeid = OrionNodes.NodeID

GROUP BY 
--   ncmnodes.Client
--	,ncmnodes.Node_In_BAU
	,OrionNodes.Caption
	,OrionNodes.DetailsUrl
	,OrionNodes.STATUS
	,OrionNodes.ChildStatus
	,OrionNodes.Vendor
	,OrionVendors.Icon
	,OrionNodes.IP_Address
	,OrionNodes.IPAddressType
	,OrionNodes.MachineType
	,NcmConfigArchive.ConfigType

Which will give you this type of result:

Report Two: Configured for backup Yes / No / Never

-- Date: June/2021
-- Version: 1
-- Compatibility: Orion Platform 2021
-- Author: Stuart Dyckhoff (Thwack: @stuartd)

SELECT 
     s.NodeID
	,s.LicensedByNCM
	,n.Caption
	,p.Client
	,IP_Address
	,n.Vendor
	,n.MachineType

FROM Cirrus.NCMNodeLicenseStatus AS s
JOIN Orion.Nodes AS n ON s.NodeID = n.NodeID
JOIN Orion.NodesCustomProperties AS p ON s.NodeID = p.NodeID

ORDER BY s.LicensedByNCM ASC

Which gives this:

And now a bonus Report: that will show the last backup errors. N.B. This is not one I use often as it can mislead due to the fact that it shows the last error, but that could have been months ago. As in it shows the error even though backups since have been successful.

-- Date: 
-- Version: 1
-- Compatibility: Orion Platform 2021
-- Author: Stuart Dyckhoff (Thwack: @stuartd)

SELECT 
     Nodes.DisplayName
	,ncm.LoginStatus
	,LastInventory

FROM Orion.Nodes AS Nodes
LEFT JOIN NCM.Nodes ncm ON nodes.nodeid = ncm.corenodeID

WHERE 
       ncm.LoginStatus NOT LIKE 'Login Ok'

ORDER BY Nodes.DisplayName

For a result of:

As I say, you could have a super report and have all 3 of these delivered as a single report by just adding more sections in the report and each bit is delivered on its own excel tab, or as a separately headed part of a web viewed report.

Safari0672

Thanks very much, Stuart for your detailed response and I agree that this should really have been simple enough to identify ALL devices on NCM and their backup status in one report.

Unfortunately, the queries you supplied still did not return the results I was after.

The first query does return the backup status of devices for which Backup configs exist, but if the device is 'unmanaged' and there is no backup config present, then that device is not included in the result, which is what we are after to ensure that a device does not get missed out from backups. It's ok for the 'Backup' column to display 'N/A' when there is no Backup config available, but the results need to include all devices, not just the ones that are in Managed state.

Also, the second query for some reason does not return the 'Yes/No/Never' column for me. If I simply copy/paste your query, it throws me an error, so I removed p.Client and s.LincensedbyNCM and then I get the results without 'Yes/No/Never' column though. The output also misses one device that I know is set as 'No' under 'Managed node(s) with NCM'.

Any thoughts on how we can include Managed and Unmanaged devices in the first report?

Thanks again for all your assistance with this.

stuartd

[quote userid="301915" url="~/product-forums/network-configuration-manager-ncm/f/forum/99557/create-an-swql-query-to-report-last-backup-status-of-all-configured-nodes-on-ncm/313051#313051"]Any thoughts on how we can include Managed and Unmanaged devices in the first report?[/quote]

Our report includes all nodes that have been set to YES in the Managed by NCM part. Whether the node is managed, unmanaged or even unreachable and that is already there - it is the Status icon on the right hand side.

You can see it in the select clause as ,OrionNodes.Status

Looks like this:

BUT

I hadn't considered how to make it show devices that don't have a config at all.

To get that you may just have to play a bit with the code. If I get some time I'll try, but again, my coding skills are less than good

stuartd

That said - I have taken a look at our current output and the report "as is" shows we have 3 devices that are configured for backup but have never had a backup.

The status column shows 1, 9, and 9 - so UP, unmanaged and unmanaged.

The "latest" column shows NULL.

So it is all there, but you may need to massage the code to retrieve what you need, and I can't answer why it might miss something out. What I do in such cases is pare back the code to a minimum and check. If all expected nodes are there, then build it out from there. That's where the -- becomes useful as it REM's out any lines, and also why I write my code formatted as it is.