topic Re: search query to get specific date range events from different field in Splunk Search

search query to get specific date range events from different field

snehalk — Wed, 23 Nov 2016 09:49:38 GMT

Hello All,

I have requirement where need to compare the two different date's and condition is that date would be one year and two months from respective field.

eg: consider two sourcetype followed by sourcetype="xyz" and sourcetype="abc" and each sourcetype contain date field called "mydate". So requirement is need to fetch the events from "abc" where the date field "mydate" is different and that date should be within one year and two months from "xyz" sourcetype date field "mydate".

Can any one guide me on this?

Thanks

Re: search query to get specific date range events from different field

gcusello — Wed, 23 Nov 2016 10:55:15 GMT

Hi snehalk,
it's not so clear your requirement:

you have events from two sourcetypes,
field mydate is present in all events of both sourcetypes,
when you say where the date field "mydate" is different, do you mean different by what?
when you say that date should be within one year and two months from "xyz" sourcetype date field "mydate", do you mean from the first, the last or what date of "xyz" sourcetype?

Bye.
Giuseppe

Re: search query to get specific date range events from different field

snehalk — Wed, 23 Nov 2016 12:33:31 GMT

Hello Giuseppe,

Thank you for response, below are my answers for each doubt you have.

you have events from two sourcetypes,
Ans: Yes, i have two different sourcetype

field mydate is present in all events of both sourcetypes,
Ans: Yes. Its present in both the sourcetypes

when you say where the date field "mydate" is different, do you mean different by what?
Ans: different by date

when you say that date should be within one year and two months from "xyz" sourcetype date field "mydate", do you mean from the first, the last or what date of "xyz" sourcetype?
Ans: it means the events(result) from "abc" should be within range of (ie one year two months) from "xyz" sourcetype field "mydate".

Re: search query to get specific date range events from different field

gcusello — Wed, 23 Nov 2016 13:24:59 GMT

about the last point: from first or last event of "xyz"?
Bye.
Giuseppe

Re: search query to get specific date range events from different field

snehalk — Wed, 23 Nov 2016 14:08:50 GMT

that would be comparing each event (date) with other event. eg: sourcetype="xyz" mydate (2013) sourcetype="abc" mydate (2016) then take the "abc" event

if sourcetype="xyz" mydate (2010) sourcetype="abc" mydate (2016) then dont take "abc" event

Re: search query to get specific date range events from different field

gcusello — Wed, 23 Nov 2016 15:30:01 GMT

Is there a field to join the two searches?
Bye.
Giuseppe

Re: search query to get specific date range events from different field

snehalk — Thu, 24 Nov 2016 09:18:16 GMT

Hello Giuseppe,

ya, its has the id_num which can use for join

Re: search query to get specific date range events from different field

gcusello — Tue, 29 Sep 2020 11:55:55 GMT

try something like this:
index=yourindex sourcetype="xyz" | eval mydate1=mydate | join id_num [ search index=yourindex sourcetype="abc" | eval mydate2=mydate ] | eval mydate1=strptime(mydate1,"your_format"), mydate2=strptime(mydate2,"your_format") | eval diff=mydate1-mydate2 | where diff<36720000
Bye.
Giuseppe

Re: search query to get specific date range events from different field

snehalk — Mon, 28 Nov 2016 10:55:06 GMT

Hello Giuseppe,

Thank you for search query. Could you please explain what is mean by 36720000? and why we are using here?

Re: search query to get specific date range events from different field

gcusello — Mon, 28 Nov 2016 11:05:28 GMT

Hi snehalk,
you asked to have events in the last year + 2 months, so 36720000 is
60x60x24x(365 + 60)
Bye.
Giuseppe

Re: search query to get specific date range events from different field

snehalk — Mon, 28 Nov 2016 11:50:41 GMT

Hello Giuseppe,

thank you so much, when i search the query am getting in "diff" column some date with 1970 year value so how to identify the value am getting is correct? because there are many events? is there any ways for that?

Re: search query to get specific date range events from different field

gcusello — Tue, 29 Nov 2016 09:51:09 GMT

Hi snehalk,
You could follow the same method: verify and exclude (or modify) events where data field is lower that 1970
to exclude use:

| where  mydate1> strptime("1979-12-31","%Y-%m-%d")

to modify your field you have to choose and apply an algorithm.

Bye.
Giuseppe

Re: search query to get specific date range events from different field

snehalk — Tue, 29 Nov 2016 10:55:31 GMT

Thank you so much !! for your help. just last question 🙂 is there any way instated of days wise calculation can we use like as below because the month may have 30 days or 31 or 28, so can we use 1y for year and for months 2m something like ?

index=yourindex sourcetype="xyz" | eval mydate1=mydate | join id_num [ search index=yourindex sourcetype="abc" | eval mydate2=mydate ] | eval mydate1=strptime(mydate1,"your_format"), mydate2=strptime(mydate2,"your_format") | eval diff=mydate1-mydate2 | where diff< 9m?

Re: search query to get specific date range events from different field

gcusello — Tue, 29 Nov 2016 11:18:02 GMT

Hi snehalk,
You don't need to use days or seconds, you can use every relative time, the only rule is to use only one of it: so one year and two months is "14mon".
beware to the time unit you are using: "9m" is 9 minutes!
Bye.
Giuseppe
P.S.: if you like this answer please accept it.
Bye.

Re: search query to get specific date range events from different field

snehalk — Tue, 29 Nov 2016 12:07:40 GMT

Thank you so much !! for your help, 🙂 just last question, can we used standard format instate of day calculation? because the month may contains 30 days or 31 days or some time 28 days , so can we use something like 1y for year and 2 months for months?

index=yourindex sourcetype="xyz" | eval mydate1=mydate | join id_num [ search index=yourindex sourcetype="abc" | eval mydate2=mydate ] | eval mydate1=strptime(mydate1,"your_format"), mydate2=strptime(mydate2,"your_format") | eval diff=mydate1-mydate2 | where diff<1y??

Re: search query to get specific date range events from different field

gcusello — Tue, 29 Nov 2016 13:51:02 GMT

Hi Hi snehalk,
I already answered to your last question, but I don't know why my answer isn't in the chain!!!
Every way you can use all the time units you want, the only rule is that you have to use only one of them: so one year and two months are 14mon, you cannot use 1y+2mon.
Bye.
Giuseppe

Re: search query to get specific date range events from different field

gcusello — Tue, 29 Nov 2016 14:59:01 GMT

Hi Hi snehalk,
I don't know why, I cannot find in the chain an answer that I inserted two times!
every way you can use every time unit you want the only rule is to use only one of them, so: for one year and two monts, you can use 14 mon, you cannot use 1y+2mon.
I hope that this time you can see my answer.
Bye.
Giuseppe