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"
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")
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")
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")
Thanks i'll try this now.
Took a bit of fudging with but got the desired outcome. Thanks very much
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.