topic Re: How do you search for the nearest file within 7 days ago of another file? in Splunk Search

How do you search for the nearest file within 7 days ago of another file?

jenny_life — Tue, 30 Oct 2018 08:24:35 GMT

Hello everyone,

I'd like to create a bottleneck graph.

Basically, I'd like to use two files. One of the files is a recent file and the other is 7 days before the recent file.

e.g.

If I have 3files as below in one index,
1. 20181022.csv
2. 20181025.csv
3. 20181030.csv

When I display the data on 2018/10/30...
2018/10/23 is 7 days before 2018/10/30. But, there isn't a file for that day. In that case, I'd like to use the file that is the nearest to 2018/10/23 within 7 days. So, I'd like to use the data of 20181030.csv file and 20181025.csv.
When I display the data on 2018/10/25
It's the same way as above. 2018/10/18 is 7 days before 2018/10/25. But, there is no data for that date.
So, I'd like to use the file 20181022.csv. This file is the nearest to 2018/10/18 within 7 days.

How can I search for the file that is the nearest within 7 days before the recent file?

The nearest file has to be within 7days.

Please let me know how to do.

I need search query.

Thank you in advance.

Re: How do you search for the nearest file within 7 days ago of another file?

iamarkaprabha — Tue, 30 Oct 2018 08:29:15 GMT

Hi ,

This happened maybe the file got indexed at that particular day,
You can add tz=UTC in props.conf for this one

It will be like this

[source::\\\\SERVERNAME\\prod-iislogs\...\...\\C*.log]
TZ = GMT

Re: How do you search for the nearest file within 7 days ago of another file?

jenny_life — Tue, 30 Oct 2018 08:48:16 GMT

Thank you for your answer.
But, those three files are already in the index.
(I imported those files to index .)
I'd like to know how to search the nearest file within 7days .
(What within 7days mean is , It depends on the date.
for example,
If standard date is 2018/10/30, 'within 7days' is from 2018/10/23 to 2018/10/30.
in that case , what I'd like to find the file is '20181025.csv'.)

↓ I need a search query like this style.

index="bottleneck"  source="*bottleneck*"
| eval date = 'occurrenceDate'
| eval before5Days = strftime(relative_time(strptime(date, "%Y/%m/%d"), "-7d@d"),"%Y/%m/%d")

Re: How do you search for the nearest file within 7 days ago of another file?

iamarkaprabha — Tue, 30 Oct 2018 10:48:12 GMT

Hi,

Can you try it like this

 your search here
 | rex field=source "/[^/]+(?<date>\d{8})[^/]+$"

It will extract the date out of your file_name
then you can compare

Re: How do you search for the nearest file within 7 days ago of another file?

jenny_life — Tue, 30 Oct 2018 11:22:13 GMT

Thank you for your attention.

I'm sorry that i didn't explain well..
what I exactly want is,,
I'd like to know how to compare,,
I can extract date from source file name.
after that, how should i do?

please refer to my query.

   index="bottleneck"  source="*.csv*"
   | eval temp = split('source',".")
   | eval date = mvindex(temp,0)
   | sort date
   | eval before7Days = strftime(relative_time(strptime(date, "%Y%m%d"), "-7d@d"),"%Y%m%d")
   | dedup date
   | table date before7Days

If i execute this query , the result is as below.

date      before7Days
20181022    20181015
20181025    20181018
20181030    20181023

but, Actually what i need is as below.

date      before7Days
20181022    20181015 (There is no file(20181015) in the index. So it doesn't care whatever)
20181025    20181022 (actual 7 days before = 20181018, but the nearest file = 20181022.)
20181030    20181025  (actual 7days before is 20181023, but there is no file so I'd like to use the 20181025.csv That is the nearest file to 2018/10/23. (between 2018/10/30 and 2018/10/23))

So i'd like know the search or comparison query in order to display the result as above .