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?