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
Hi snehalk,
it's not so clear your requirement:
where the date field "mydate" is different
, do you mean different by what?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
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".
about the last point: from first or last event of "xyz"?
Bye.
Giuseppe
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
Is there a field to join the two searches?
Bye.
Giuseppe
Hello Giuseppe,
ya, its has the id_num which can use for join
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
Hello Giuseppe,
Thank you for search query. Could you please explain what is mean by 36720000? and why we are using here?
Hi snehalk,
you asked to have events in the last year + 2 months, so 36720000 is
60x60x24x(365 + 60)
Bye.
Giuseppe
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?
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
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
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
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??
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.
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?