Correlate data from two diffrent splunk logs and evaluate the diffrence of time fields in both logs.

[venky10]

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?

[venky10]

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.

[gcusello]

Hi @venky10,

my last hist should be the correct one, please, try it.

Ciao.

Giuseppe

[venky10]

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. 

[gcusello]

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:

• convert dates in epochtime,
• use stats BY the common key,
• use earliest and latest functions in stats.

Ciao.

Giuseppe

[venky10]

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.

[gcusello]

Hi @venky10,

there are many not correct things in your search:

• you cannot put an eval commandin a search string,
• pipes have the meaning of pipe in linux: the left part is the input for the right part, so you cannot put a pipe in parenthesis,
• after stats command you have only the fields of stats command, the other fields are loose, so in your example there's a1 and epoch_createdOn aren't still available, to have them you have to insert them in the stats (e.g. using earliest or values),
• the search string isn't correct because you cannot use quotes inside a search string witout escaping them.

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

[venky10]

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]

Hi @venky10,

in this way you can have a common key to correlate.

Ciao.

Giuseppe

[venky10]

@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

[venky10]

@gcusello 

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

[venky10]

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.

[venky10]

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.

[gcusello]

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