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!

Detecting Remote Code Executions With the Splunk Threat Research Team

REGISTER NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If ...

Observability | Use Synthetic Monitoring for Website Metadata Verification

If you are on Splunk Observability Cloud, you may already have Synthetic Monitoringin your observability ...

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...