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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...