How to generate a daily report that shows the number of backups kicked off for each user and how many were successful and unsuccessful in a table format?

[drbruhn]

I'm a total Splunk query noob here, so pardon the basic nature of my question. We have our backup logs forwarded to Splunk in the following format:

[Thu May  10 12:00:00 EDT 2018] user=johndoe computername=computer101 comment="Backup completed (un)successfully"

We'd like to generate a daily report that tells us how many backups have kicked off for each user, how many were successful, and how many were unsuccessful in a table format.

How might I go about that?

Thanks in advance!

[somesoni2 · Accepted solution]

Give this a try

your base search e.g. index=foo sourcetype=bar to select backup logs
| stats count by user comment
| eval comment=if(match(comment,"unsuccessfully"),"Unsuccessful","Successful")
| chart sum(count) over user by comment
| eval Total_Backups=Unsuccessful + Successful
| table user Total_Backups Successful Unsuccessful

[drbruhn]

This one is REALLY close. For some reason, if all backups are successful, I don't see a total. I only get a total if there are unsuccessful backups.

[somesoni2]

Well, try this version (this will cover if all backups failed OR passed)

your base search e.g. index=foo sourcetype=bar to select backup logs
 | stats count by user comment
 | eval comment=if(match(comment,"unsuccessfully"),"Unsuccessful","Successful")
 | chart sum(count) over user by comment | fillnull value=0
 | eval Total_Backups=Unsuccessful + Successful
 | table user Total_Backups Successful Unsuccessful

[drbruhn]

Perfection! Thanks!

[davey1985]

rex = "\[(?<date>[A-z]{3}\s[A-z]{3}\s+\d+)\s\d+:\d+:\d+\d.*user=(?<username>[A-z.0-9-]*)\s+computername=(?<computername>[A-z.0-9-]*)\scomment=\"(?<comment>.*)\""
| stats count(comment) by comment

Thats how many successfull vs unsuccessfull

| stats count(username) by computername,comment

Thats each job kicked off by a user per computer and if it was successful or not

[drbruhn]

That's really close to what I'm looking for. I apologize for not specifying this beforehand, but what I'd like is to see the following:

Username | Successful Backups | Unsuccessful Backups | Total
jdoe | 3 | 1 | 4
asmith | 5 | 0 | 5

Make sense?

[jodyfsu]

Hello drbruhn, something like this should work:

user="*" computername="*" comment="Backup*"
stats count(comment) by comment, user