Finding/displaying date range

[jsmith39]

I have a process running on 50 servers that processes 4 files into a SQL DB and then writes to a log file the name of those 4 files Mon-Fri mornings. I have an additional central server that writes the same 50 sets of data to 50 databases on it's SQL installation and again writes the names of the files to 50 different log files. Each of these 4 files start with a specific 8 character name prefix and then have a unique by date naming convention suffix.

I'm trying to figure out how to make Splunk look through the log files for the last grouping of fileA,fileB,fileC,fileD that took place within 4 hours of each other and display the date that this happened by host.

I've tried defining fields and using DC(fieldname) and I've tried using transactions to group the 4 files types but I'm not making any progress on returning the result set I'm looking for.

Thank You

[martin_mueller · Accepted solution]

Assuming you have the timestamp properly recognized in _time, the file name extracted in file, and the host correctly stored in host, you could do this:

base search yielding only these events | eval day = strftime(_time, "%Y-%m-%d") | stats dc(file) as file_count range(_time) as range by day host | where file_count==4 AND range<14400 | sort - day | dedup host

That will group events by day and host, count the number of distinct files processed and compute the difference between first and last timestamp. Using that, only rows with four files and a range less than four hours will be kept. Sort by day descending, only keep the first (= newest) row per host.

[jsmith39]

That worked perfectly, thank you!

[jsmith39]

In a typical 2 day (work week) period, each of the 51 servers will post to it's work log something similar to

SAT_FILE004H01 was processed: 4/17/2014 4:02:16 AM
APPL_EXT004H01 was processed: 4/17/2014 4:04:42 AM
SVC_SAT_FI4H01 was processed: 4/17/2014 4:04:55 AM
PCP_FILE004H01 was processed: 4/17/2014 4:06:03 AM
SAT_FILE004I01 was processed: 4/18/2014 4:02:32 AM
APPL_EXT004I01 was processed: 4/18/2014 4:05:01 AM
SVC_SAT_FI4I01 was processed: 4/18/2014 4:05:09 AM
PCP_FILE004I01 was processed: 4/18/2014 4:06:20 AM

[martin_mueller]

Do post some sample events.