Splunk Search

search query to get specific date range events from different field

snehalk
Communicator

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

Tags (4)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

snehalk
Communicator

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".

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

snehalk
Communicator

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

snehalk
Communicator

Hello Giuseppe,

ya, its has the id_num which can use for join

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

snehalk
Communicator

Hello Giuseppe,

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

snehalk
Communicator

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?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

snehalk
Communicator

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??
0 Karma

gcusello
SplunkTrust
SplunkTrust

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.

0 Karma

snehalk
Communicator

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?
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...