Splunk App for Microsoft Exchange: How to edit my search to find backups older than 24 hours based on a field?

[fargle]

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="" 

[fargle]

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"

[rphillips_splk · Accepted solution]

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"