Splunk Search

Finding/displaying date range

jsmith39
Path Finder

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

Tags (2)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
Path Finder

That worked perfectly, thank you!

0 Karma

jsmith39
Path Finder

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Do post some sample events.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...