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

Backup (Config) Reports - again..... so sue me :)

TLDR: Looking for a report that shows all backups that have a failure error message AND that occurred in the last 7 days.

I've been asking (and asking), both here in THWACK** AND support AND with our TAM (sadly we have now lost direct access to him) for help with creating a report that will show failures in our backups. Anyway ....

The scenario is:

Currently we backup approx 2,200 nodes in rolling jobs on a weekly basis, and we need to add another 2,500 in the very near future but we want to be sure everything in place is working as expected before adding to what we have.

What we want (would like):

Ideally we want to move to a state where we can alert on every node that fails to backup - but for now, we'd settle on running a report sent once a week that shows what didn't backup in the prior 7 days.

The problem:

I can't find, nor have I been pointed at anything, nor have I been able to utter the right incantation to force SWQL to throw out what we need. Most of the problem with the last bit is that I'm a complete n00b on SQL/SWQL. Most recently I was pointed at the out of the box report "config transfer audit" *** which, when first run seems to fit the bill. On a cursory inspection it only shows approx a quarter of our currently configured backup estate and it appears to me, to only show devices that have had a manually invoked backup, and not those run from a job. Secondly it shows error messages that happened months ago, and seems to ignore the fact that there are more recent successful backups, but seemingly assigns those errors to more recent dates!

So ... does anyone out there already have a similar report (we only use NPM, NCM, Netflow and IPAM) that would do what we need?

Partial/start of an answer is that I did manage to cobble together this report: but I am being asked to take it a step further. It has two issues (for me anyway)

I have no idea how to get it to show me only devices that failed in the last X days - my SQL Fu is not strong enough
I can only filter on LoginStatus not like '%Login OK%' but I'm not convinced that covers off all failure types

** some threads on backups I've created, interacted on or even just read:
https://thwack.solarwinds.com/product-forums/the-orion-platform/f/forum/94060/alert-me-when-there-is-backup-failure-of-a-node-during-nightly-config-backup

https://thwack.solarwinds.com/product-forums/network-configuration-manager-ncm/f/forum/91109/how-can-i-alert-on-a-backup-failure-of-a-node

*** I was looking for reports with the word 'backup' in it - doh!

Find more posts tagged with

Accepted answers

All comments

sum_giais

EDIT: The queries below only look at manual transfer results and NOT results run from a job.

Try giving this query a shot. I'd recommend building a custom SWQL alert for this condition. Hope this help sir!

Below are 2 similar queries. The 1st is the query in a format that you could leverage within SWQL studio and/or scripting etc.. where it has most the fields in the select. The 2nd is the query in a format that you could utilize within a "Custom SWQL Alert (Advanced)" alert. This is where you essentially define manually the data structure and conditions within SWQL to trigger the alerts as opposed to letting the UI handle it for you.

When setting up the alert, I'd probably set this up to check every 15 minutes, and only have the alert enabled during a 31m span of time every day (say maybe 8am-8:30am). Minimize the performance impact of new alerts and limits the check to a specific timeframe every day.... while also ensuring it gets a chance to run at least twice (checked every 15m and enabled for 31m it should get a chance to run twice no matter what time it starts.)

Important: Don't copy/paste the SELECT line from the 2nd query (Query2) into the Custom SWQL Alert query window. Below is a screenshot of what it should look like as well.

Note that I didn't test this thoroughly (other than the logic appears sound and query returns proper) so you may want to test this and put it through its paces and ensure that it truly works when your backups are failing.

A couple more pieces to note on the query:

Source data is the core node (Orion.Nodes) -- I do this because the custom SWQL alert wants a core node anyway
I have a where statement requiring the nodes be licensed in NCM (e.g.
n.NCMLicenseStatus.LicensedByNCM = 'Yes')
(otherwise you may muddy the waters somehow...)
Condition to actually trigger an issue would be if the NCM.LoginStatus is anything but 'Login OK'.... OR the last transfer result is > 7 days ago (I prefer setting this with hours to be more specific)... OR the last transfer result is NULL (implying some other job failure may have occurred or misconfiguration of the job.)

Query1 (for SWQL Studio):

-- Query in a usable form for usage in SWQL Studio etc.
--
--
SELECT 
     n.NodeID
    ,n.IPAddress
    ,n.Caption
    ,n.DNS
    ,n.MachineType
    ,n.Vendor
    ,n.NCMLicenseStatus.LicensedByNCM
    ,nc.LoginStatus
    ,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 n.NCMLicenseStatus.LicensedByNCM = 'Yes'
AND ( nc.LoginStatus != 'Login OK' OR HOURDIFF(sub.DateTime, GETDATE()) > 168 OR sub.DateTime IS NULL )

Query2 (for Custom SWQL Alert):

-- Query in alert form for usage in a custom SWQL alert trigger condition (for a Node)
--
-- NOTE: Don't copy the SELECT line into the query window for the custom SWQL alert
--       you'll notice it's already created and you just insert the conditions afterwards
--
SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
LEFT JOIN NCM.Nodes AS nc ON nc.CoreNodeID = Nodes.NodeID AND Nodes.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 Nodes.NCMLicenseStatus.LicensedByNCM = 'Yes'
WHERE Nodes.NCMLicenseStatus.LicensedByNCM = 'Yes'
AND ( nc.LoginStatus != 'Login OK' OR HOURDIFF(TOLOCAL(sub.DateTime), GETDATE()) > 168 OR sub.DateTime IS NULL )

sum_giais

Now I know I didn't touch on the report. The 1st SWQL query could be used as a datasource for a report table. I'd add some other fields myself though in that to include the details URL and maybe other custom properties.

Also the query is joining (LEFT JOIN) the max datetime result from transfer results. So with some readjustments of the query you could show the last 7 days of results. Would need to dig into that to see what would need to change specifically but it sounds good on paper.

stuartd

Thank you very much.

I will give it a go as a report first before considering it as an alert - and when I do that, we will run it in or lab first to see what happens..

stuartd

OK so I've run it as a report with zero edits and it returns results of all 2.2k nodes.

If I edit out the OR HOURDIFF section it returns the results I expect. Now as I still haven't wrapped my head around date/time selection in SQL then I can't begin to see the why that makes it fail. Though having had a look at NCM.TransferResults we have less than 1K results in there, so a whole bunch of our nodes don't appear there to be able to be used in the query you created.

My GUT feeling is that NCMTransferResults only shows results from manually initiated backups, rather than those via a job.

sum_giais

Good point you're probably right. I'll do a bit more digging with the jobs and see..

sum_giais

So I took a look at the Cirrus.Audit table you noted in another thread, there's no way to tie that, at least in which I see to a node. The audits don't have a record of which node ID has the error. Best it has is an IP address in the details sometimes, but not always so that's not a reliable way to join the data. You could still review the errors from the Cirrus.Audit table -- but it's desparate from the other data specific to the node unfortunately. Query below for last 7 days of failures. It's semi-useful?

SELECT
     ca.Username AS [JobName]
    ,ca.ModuleName AS [Function]
    ,ca.Action
    ,ca.Type AS [Result]
    ,ca.Details AS [ResultDetails]
    ,ca.DateTime
FROM Cirrus.Audit AS ca
WHERE ( ca.ModuleName = 'JobsAudit' AND ca.Type != 'Successful' )
AND DAYDIFF(TOLOCAL(ca.DateTime), GETDATE()) < 7
ORDER BY ca.DateTime DESC

I'm thinking that it may be possible to look at the config archives and determine if there's missing configs or no configs for the last 7 days. But the errors would be a bit of a miss where the only field you have available is the 'LoginStatus' (which is a current/last status only). Looking at the config archives I'm also not sure if in the case the config doesn't change and you have the job set to download only if the config has changed -- what happens then...?

Certainly more digging to be done...