Hi,
i am relatively newer to SPL, i have a usecase to evaluate time difference bwn two fields in two different logs with common data field in both query1 and query 2
sample log looks like this.
log1 - "field1:: value1 createdOn1:: "9/30/20 10:14 AM", commonfield:: "abds"
log2 - "field:: value createdOn2::"2020-09-30 23:30:00" commonfield::"abds"
i have to correlate both of them by commonfieldValue and get difference of createdOn2-createdOn1 in seconds.
Experts, could you help me with this?
Hi @gcusello thanks for the response.
i got the gist of what you suggest.
I need to pick latest createdOn value from log2/3/4 related to log1 and then find the difference of createdOn bwn log1 and log2/3/4 final value. Let me try the query, still figuring out the how to join by commonfield as very new to SPL.
Hi @gcusello thanks for the response.
i just realised that for log1 there are mulutiple related logs
sample log looks like this.
log1 - "field1:: value1 createdOn:: "9/30/20 10:14 AM", commonfield:: "abds, field2 ::valu2"
related logs-
log2 - "field:: value createdOn::"2020-09-30 23:30:00" commonfield::"abds, field2 ::valu2"
log3 - "field:: value createdOn::"2020-09-30 23:31:00" commonfield::"abds, field2 ::valu2"
log4 - "field:: value createdOn::"2020-09-30 23:32:00" commonfield::"abds, field2 ::valu2"
i need to take earliest createdOn among log2/3/4 based on commonfield value from log1 and print the diff of time bwn createdOn.
Hi @venky10,
I didn't understand if log1 is in the range of logs where to find earliest and latest or not,
if it's in the range, you can simplify my previous search:
your_search
| eval epoch_createdOn=strptime(createdOn,"%m/%d/%y %H:%M")
| stats earliest(epoch_createdOn) AS earliest latest(epoch_createdOn) AS latest BY commonfield
| eval diff=latest-earliest
if instead it's outside the range It's not clear for me how it enters in the search.
Anyway, from my example you can take the approach to solve the problem:
Ciao.
Giuseppe
i think, you got me wrong. @gcusello
this is what i am trying to. I am getting lost here 😞
(index=* host=* " "field1:: value1 createdOn:: "9/30/20 10:14 AM", commonfield1::"abds", field2 ::valu2" | rex "commonfield1::(?<a1>[^,]+)," | eval epoch_createdOn=strptime(createdOn,"%m/%d/%y %H:%M")
) OR (index=* "field:: value createdOn::"2020-09-30 23:30:00" commonfield2::"abds, field2 ::valu2") | rex "commonfield2::(?<a2>[^,]+)," | eval epoch_createdOn=strptime(createdOn,""%m/%d/%y %H:%M"")
| stats earliest(epoch_createdOn) AS earliest BY a2 | where a1==a2
| eval earliest- epoch_createdOn
query1= "index=* host=* " "field1:: value1 createdOn:: "9/30/20 10:14 AM", commonfield1::"abds", field2 ::valu2"
query2 = index=* "field:: value createdOn::"2020-09-30 23:30:00" commonfield2::"abds, field2 ::valu2" gives multiple results.
Please help me here.
Hi @venky10,
there are many not correct things in your search:
So, probably this isn't what you want, but, as I said, see the approach.
index=* host=* ("field1:: value1 createdOn:: \"9/30/20 10:14 AM\", commonfield1::\"abds\" field2 ::valu2" OR "field:: value createdOn::\"2020-09-30 23:30:00\" commonfield2::\"abds, field2 ::valu2")
| rex "commonfield1::(?<aa>[^,]+),"
| rex "commonfield2::(?<aa>[^,]+),"
| eval epoch_createdOn=strptime(createdOn,""%m/%d/%y %H:%M""
| stats earliest(epoch_createdOn) AS earliest latest(epoch_createdOn) AS latest BY aa
| eval earliest-latest
Ciao.
Giuseppe
will this part of your query
| rex "commonfield1::(?<aa>[^,]+)," | rex "commonfield2::(?<aa>[^,]+),"
take care of values in both commonfield1 and commonfield2 to be same?
@gcusello looks like its not working.
(query1) OR (query2) | rex "CommonField1::(?<id>[^,]+)," | rex "CommonField2::(?<id>[^,]+)," | dedup id | table id
Samples:
query1= "index=* host=* " "field1:: value1 createdOn:: "9/30/20 10:14 AM", commonfield1::"abds", field2 ::valu2"
query2 = "field:: value createdOn::"2020-09-30 23:31:00" commonfield2::"abds, field2 ::valu21"
one more search result possible for query2 can be "field:: value createdOn::"2020-09-30 23:31:00" commonfield2::"Cbds, field2 ::valu21"
This itself is not giving the common results by abds
the query you suggested works for correlating but i am unable to print difference bwn createdOn value from query1 with earliest values calculated after grouping.
i.e, diff below is coming as empty. i think we are losing the epoch_CreatedOn after grouping.
index=* host=* ("field1:: value1 createdOn:: \"9/30/20 10:14 AM\", commonfield1::\"abds\" field2 ::valu2" OR "field:: value createdOn::\"2020-09-30 23:30:00\" commonfield2::\"abds, field2 ::valu2")
| rex "commonfield1::(?<aa>[^,]+),"
| rex "commonfield2::(?<aa>[^,]+),"
| eval epoch_createdOn=strptime(createdOn,""%m/%d/%y %H:%M""
| stats earliest(epoch_createdOn) AS earliest BY aa
| eval diff = epoch_createdOn-earliest | table diff
index=* host=* ("field1:: value1 createdOn1:: \"9/30/20 10:14 AM\", commonfield1::\"abds\" field2 ::valu2" OR "field:: value createdOn::\"2020-09-30 23:30:00\" commonfield2::\"abds, field2 ::valu2")
| rex "commonfield1::(?<aa>[^,]+),"
| rex "commonfield2::(?<aa>[^,]+),"
| eval epoch_createdOn1=strptime(createdOn,""%m/%d/%y %H:%M""
| stats min(epoch_createdOn -epoch_createdOn1) AS time_diff BY aa
| table time_diff
this also does not work.
here the expected value for below example is (2020-09-30 20:30:00 - 2020-09-30 23:31:00)
query1= "index=* host=* " "field1:: value1 createdOn:: "9/30/20 10:14 AM", commonfield1::"abds", field2 ::valu2"
<<<<related 3 logs for above string is >>>>
field:: value createdOn::"2020-09-30 23:31:00" commonfield2::"abds, field2 ::valu21
field:: value createdOn::"2020-09-30 23:32:00" commonfield2::"abds, field2 ::valu22
field:: value createdOn::"2020-09-30 23:33:00" commonfield2::"abds, field2 ::valu24
@woodcock pls help.
Hi @venky10,
you have to transform the dates in epochtime, then you can correlate them, something like this:
your_search
| eval epoch_createdOn=coalesce(strptime(createdOn1,"%m/%d/%y %H:%M"),strptime(createdOn2,"%m/%d/%y %H:%M")
| stats earliest(epoch_createdOn) AS earliest latest(epoch_createdOn) AS latest BY commonfield
| eval diff=latest-earliest
Ciao.
Giuseppe