I'm using the Splunk App for Exchange and I want to get a report of databases where the "LastFullBackup" is older than 24 hours.
Here's the basic search:
eventtype=msexchange-database-stats | stats latest(LastFullBackup) as LastFullBackup,latest(LastIncrementalBackup) as LastIncrementalBackup by Database | rename host as "Mailbox Store",LastFullBackup as "Last Full",LastIncrementalBackup as "Last Incremental"
And here's an event listing. I keep trying to convert the "LastFullBackup" time to seconds from the epoch, then subtracting that from now. Then I'm looking to get results older than 24 hours. I keep getting no results.
2015-08-17T09:45:59-04:00 Database="DB-020" Active="Active" MasterType="DatabaseAvailabilityGroup" Status="Healthy,Healthy" PublicFolderDatabase="Public Folders" IsMailboxDatabase="True" IsPublicFolderDatabase="False" LogFolderPath="C:\ExchangeDBMountPoints\DB-020-Logs" LogPercFree=24.594 LogSize=4506779648 FilePath="C:\ExchangeDBMountPoints\DB-020\DB-020.edb" MainPercFree=68.665 FileSize=487488290816 LocalCopy="False" CopyFilePath="" CopyPercFree=0 CopyFileSize=0 CopyStatus=Disabled SnapshotLastFullBackup="True" SnapshotLastIncrementalBackup="True" SnapshotLastDifferentialBackup="" SnapshotLastCopyBackup="" LastFullBackup="08/17/2015 00:30:20" LastIncrementalBackup="08/17/2015 06:15:09" LastDifferentialBackup="" LastCopyBackup=""
Is this what you are trying to achieve?
eventtype=msexchange-database-stats| eval epoch_last_full_backup=strptime(LastFullBackup, "%m/%d/%Y %H:%M:%S")
| eval epoch1dayago=relative_time(now(), "-1d@d" ) | where epoch1dayago>=epoch_last_full_backup | stats latest(_time) by Database LastFullBackup LastIncrementalBackup| rename LastFullBackup as "Last Full" LastIncrementalBackup as "Last Incremental" | table Database "Last Full" "Last Incremental"
That was really close. In this format, it was returning successful backups older than the 'epoch1datago'. It gave me the jumping off point I needed. Here's what I changed it to:
eventtype=msexchange-database-stats LastFullBackup |
stats latest(LastFullBackup) as LastFullBackup,latest(LastIncrementalBackup) as LastIncrementalBackup by Database |
eval epoch_last_full_backup=strptime(LastFullBackup, "%m/%d/%Y %H:%M:%S") |
eval epoch1dayago=relative_time(now(), "-1d@d" ) |
where epoch1dayago>=epoch_last_full_backup |
rename host as "Mailbox Store",LastFullBackup as "Last Full",LastIncrementalBackup as "Last Incremental"
Is this what you are trying to achieve?
eventtype=msexchange-database-stats| eval epoch_last_full_backup=strptime(LastFullBackup, "%m/%d/%Y %H:%M:%S")
| eval epoch1dayago=relative_time(now(), "-1d@d" ) | where epoch1dayago>=epoch_last_full_backup | stats latest(_time) by Database LastFullBackup LastIncrementalBackup| rename LastFullBackup as "Last Full" LastIncrementalBackup as "Last Incremental" | table Database "Last Full" "Last Incremental"