Querying NCM for nodes that have (and or have not) backed up in last x days

So, as per my title really..

I can't find where/how to query the date for when a node last had a successful backup.

My current query looks like this: 

SELECT n.NodeID, n.IPAddress, nc.ClientPrefix, n.Caption, n.MachineType, n.Vendor, n.NCMLicenseStatus.LicensedByNCM, nc.LoginStatus, nc.LastInventory, nc.Node_In_BAU, ToLocal(sub.DateTime) AS LastAction
FROM Orion.Nodes AS n
LEFT JOIN NCM.Nodes AS nc ON nc.CoreNodeID = n.NodeID AND n.NCMLicenseStatus.LicensedByNCM = 'Yes'
LEFT JOIN ( SELECT MAX(tr.DateTime) AS DateTime, tr.NodeID FROM NCM.TransferResults AS tr GROUP BY tr.NodeID ) AS sub ON sub.NodeID = nc.NodeID AND n.NCMLicenseStatus.LicensedByNCM = 'Yes'
WHERE nc.Node_In_BAU = True 

My limited (very) knowledge of SQL and Googling says I need to add something like:

>> AND DateTime > ADDDAY(-7, GETUTCDATE())

- the aim being that it should give me any node matching my query and that backed up in the last X days. In this case x=7 but is flexible.

If I run the above report against a specific subset of nodes by adding in > and Caption like '%CEC-PSc-Libert%' then I get nothing. If I then manually backup one of the nodes it reports back on that node but I want to query against those backups executed via regular job...

So, what am I missing please?

  • I'm not sure I remember who, exactly, I "borrowed" this from, but I'm certain I had asked a similar question to what you are asking now.

    I use this version on a "Device Details Summary" view, to look at different aspects of similar devices. I had added the NCM config backup info onto the end of this so I could view it all together.

    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.CustomProperties.Area,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

    It gives me the following results.

    I also use a version on our home view to show which devices have not been backed up in X number of days.

    SELECT
    --'' AS [NodeStatus]
    --,'/Orion/images/StatusIcons/small-' + ToString(np.Nodes.StatusIcon) AS [_IconFor_NodeStatus]
    np.Nodes.Caption
    ,np.Nodes.DetailsUrl as [_linkfor_Caption]
    ,'/Orion/images/StatusIcons/small-' + ToString(np.Nodes.StatusIcon) AS [_IconFor_Caption]
    --,'/netperfmon/images/vendors/' + np.Nodes.VendorIcon as [_iconfor_Caption]
    
    --,n.DetailsUrl AS [_LinkFor_Node]
    
    --,LoginStatus as [Latest Login Status]
    
    --,CASE
    --    WHEN LoginStatus LIKE '%Connection Refused%' THEN 'ConnRefsd'
    --    WHEN LoginStatus LIKE '%Login OK%' THEN 'Good'
    --    ELSE 'Other'
    --END AS [LoginStatus]
    --,'' AS [LoginStatus]
    --,CASE
    --    WHEN LoginStatus LIKE '%Connection Refused%' THEN '/Orion/images/StatusIcons/Small-Critical.gif'
    --    WHEN LoginStatus LIKE '%Login OK%' THEN '/Orion/images/StatusIcons/Small-Up.gif'
    --    ELSE '/Orion/images/StatusIcons/Small-Warning.gif'
    --END AS [_iconfor_LoginStatus]
    
    
    ,max(downloadtime) as [LastLognSts-BackupTime]
    ,CASE
        WHEN LoginStatus LIKE '%Connection Refused%' THEN '/Orion/images/StatusIcons/Small-Critical.gif'
        WHEN LoginStatus LIKE '%Login OK%' THEN '/Orion/images/StatusIcons/Small-Up.gif'
        ELSE '/Orion/images/StatusIcons/Small-Warning.gif'
    END AS [_IconFor_LastLognSts-BackupTime]
    
    --,ConfigType
    --,case
    --    when max(downloadtime) is null then '/Orion/images/StatusIcons/Small-Down.gif'
    --    when daydiff(max(downloadtime),getdate())>5 THEN '/Orion/images/StatusIcons/Small-Critical.gif'
    --    when daydiff(max(downloadtime),getdate())>1 THEN '/Orion/images/StatusIcons/Small-Warning.gif'
    --    else '/Orion/images/StatusIcons/Small-Up.gif'
    --end as [_IconFor_LastBackup]
    
    --,np.Nodes.Status,np.Nodes.Vendor
    
    FROM NCM.NodeProperties AS np
    left join NCM.ConfigArchive AS ca on ca.NodeID=np.NodeID AND ca.ConfigType='Running'
    --where (np.Nodes.Caption like '%${SEARCH_STRING}%' or configtype like '%${SEARCH_STRING}%' or loginstatus like '%${SEARCH_STRING}%')
    
    WHERE np.Nodes.Status NOT IN ('2','9')
    AND np.Nodes.Vendor NOT IN ('Linux','net-snmp','Valere Power Inc')
    
    group by np.Nodes.Caption, np.Nodes.DetailsUrl, np.Nodes.VendorIcon, LoginStatus,np.Nodes.StatusIcon--,np.Nodes.Status,np.Nodes.Vendor, configtype
    
    HAVING daydiff(max(downloadtime),getdate())>1
    
    order by Caption--, configtype

    Which gives us a widget of results as such.

    Is this what you're looking for?

    Thank you,

    -Will

  • Thank you

    I shall look at this tomorrow but it looks like it will be what I'm after.

  • So, found some time and needed to do  bit of tweaking. The SQL you've pasted is a bit beyond me but I sort of understand what it is doing, just not necessarily the how.

    At first I was getting errors being thrown up as it couldn't find: NodeProperties.Nodes.CustomProperties.Area - managed to locate and edit that out of the Select and Group By sections and now the query ran but it was blank.

    Then I spotted the: .Vendor LIKE '%Juniper%'  - part and commented that out, but it was still blank until I spotted it was in the WHERE and the INNER JOIN. Commented both out and bing, bang, bong.

    Diolch.

  • Yeah, sorry about that. I usually try to comment out/reduce the custom properties and where stuff, but just slipped my mind this time.

    Glad you got it working!

    If you need anything else, we're all here to help.


    Thank you,

    -Will