EVAL Total Duration in Minutes Across 2 Indexes

[lavster]

ive created a table with monitoring in for our daily checks

However I still need to do an eval to get the Total Duration in Minutes for each service which is (“Test File End” – Test_Start)

In the example below I’ve shown in yellow my attempt to eval this field. It actually works when the fields I am using are not included in the join subsearch. However when I join on the subsearch field the field returns blank

It has been suggested to do this without a join but as its in a seperate index the data comes back blank for the file start and end fields.

index=test| bucket _time span=1d as Day | stats earliest(_time) as TEST_Start latest(_time) as TEST_End by Day
| eval TEST_Start=strftime(TEST_Start,"%H:%M:%S")
| eval TEST_End=strftime(TEST_End,"%H:%M:%S")
| eval Day=strftime(Day,"%d/%m/%Y")
| join Day [search index=test2 State=START Service="Testing" | bucket _time span=1d as Day | stats values(FileTime) as "TEST File Start" by Day | eval Day=strftime(Day,"%d/%m/%Y")]
| join Day [search index=test2 State=END Service="Testing" | bucket _time span=1d as Day | stats values(FileTime) as "Test File 
End" by Day | eval Day=strftime(Day,"%d/%m/%Y")]
| eval st = strptime(Test_Start,"%H:%M:%S") | eval et = strptime("Test File End","%H:%M:%S") | eval diff = et - st | eval "TEST_Total" = tostring(diff, "duration")
| fields Day Test_Start Test_End "Test File Start" "Test File End" "TEST_Total"

[woodcock]

Like this:

| makeresults 
| eval raw="index=test,Day=13/08/2019,Service=TEST,FileTime=08:24:48,State=START:::index=test,Day=13/08/2019,Service=TEST,FileTime=08:39:07,State=END:::index=test2,Day=13/08/2019,Service=TEST,FileTime=08:39:55,State=START:::index=test2,Day=13/08/2019,Service=TEST,FileTime=08:52:07,State=END" 
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 
| kv 
| eval _time = strptime(Day . " " . FileTime, "%d/%m/%Y %H:%M:%S") 
| sort 0 - _time 

| rename COMMENT1of2 AS "Everything above generates smaple event data; evertying below is your solution"
| rename COMMENT2of2 AS "Replace everything above with a simple '(index=test OR index=test2) WITHOUT 'join'"

| eval Day=strptime(Day,"%d/%m/%Y") 
| fieldformat Day=strftime(Day,"%d/%m/%Y") 
| streamstats count(eval(State="END")) AS sessionID BY index Day Service
| stats range(_time) AS duration BY index Day Service sessionID
| eval {index} = duration
| fields - index duration
| stats values(*) AS * BY Day Service sessionID
| fieldformat test=tostring(test, "duration")
| fieldformat test2=tostring(test2, "duration")

[mayurr98 · Accepted solution]

try this ?

index=test OR (index=test2 State=START Service="Testing") OR (index=test2 State=END Service="Testing") 
| bucket _time span=1d as Day 
| stats earliest(eval(case(index="test",_time))) as TEST_Start latest(eval(case(index="test",_time))) as TEST_End values(eval(case(index="test2" AND State="START",FileTime))) as "TEST File Start" values(eval(case(index="test2" AND State="END",FileTime))) as "TEST File End" by Day 
| eval TEST_Start=strftime(TEST_Start,"%H:%M:%S") 
| eval TEST_End=strftime(TEST_End,"%H:%M:%S") 
| eval Day=strftime(Day,"%d/%m/%Y") 
| eval st = strptime("TEST File Start","%H:%M:%S") 
| eval et = strptime("TEST File End","%H:%M:%S") 
| eval diff = et - st 
| eval "TEST_Total" = tostring(diff, "duration") 
| table Day Test_Start Test_End "Test File Start" "Test File End" "TEST_Total"

OR you could change main search to

index=test OR (index=test2 (State=START OR State=END) Service="Testing") 

[lavster]

Thanks i'll try this now.

[lavster]

Took a bit of fudging with but got the desired outcome. Thanks very much

[woodcock]

Do not use join. Show us a few sample events and a mockup of what the final output should be and then we can help.