I have an use case to calculate time difference between events grouped together by transaction command. Example is given below.
{
"timeStamp": "Fri 2020.03.27 01:10:34:1034 AM EDT",
"step": "A"
}
{
"timeStamp": "Fri 2020.03.27 01:10:38:1038 AM EDT",
"step": "B",
}
{
"timeStamp": "Fri 2020.03.27 01:10:39:1039 AM EDT",
"step": "C"
}
{
"timeStamp": "Fri 2020.03.27 01:10:40:1034 AM EDT",
"step": "D"
}
I have two requirements.
Will it be possible to get time difference between consecutive steps ?
STEP B 4 sec
STEP C 1 sec
STEP D 1 sec
If above is possible how can I get average elapsed time between two steps for all the transactions which have Step A, B, C, D ?
| makeresults
| eval _raw="{
\"timeStamp\": \"Fri 2020.03.27 01:10:34:1034 AM EDT\",
\"step\": \"A\"
}
{
\"timeStamp\": \"Fri 2020.03.27 01:10:38:1038 AM EDT\",
\"step\": \"B\",
}
{
\"timeStamp\": \"Fri 2020.03.27 01:10:39:1039 AM EDT\",
\"step\": \"C\"
}
{
\"timeStamp\": \"Fri 2020.03.27 01:10:40:1034 AM EDT\",
\"step\": \"D\"
}"
| rex max_match=0 "\"timeStamp\":\s*\"(?<timeStamp>[^\"]+)\""
| rex max_match=0 "\"step\":\s*\"(?<step>[^\"]+)\""
| eval _counter=mvrange(0,mvcount(step))
| stats values(*) as * by _counter
| foreach *
[ eval <<FIELD>> = mvindex('<<FIELD>>' , _counter) ]
| fields - _*
| eval _time=strptime(timeStamp,"%a %Y.%m.%d %I:%M:%S:%4N %p %Z")
| delta _time as diff
| fillnull diff
| eval session = 1
| stats list(*) as * by session
Convert field timeStamp to epoch and use delta command to find out delta.
| eval timeStamp_epoch=strptime(timeStamp, "%a %Y.%m.%d %I:%M:%S:%4N %p %Z")
| delta timeStamp_epoch p=1 AS diff
| eval diff=round(diff, 0)." sec"
| where isnotnull(diff)
| table step, diff
Sample query:
| makeresults
| eval data="{
\"timeStamp\": \"Fri 2020.03.27 01:10:34:1034 AM EDT\",
\"step\": \"A\"
}
{
\"timeStamp\": \"Fri 2020.03.27 01:10:38:1038 AM EDT\",
\"step\": \"B\",
}
{
\"timeStamp\": \"Fri 2020.03.27 01:10:39:1039 AM EDT\",
\"step\": \"C\"
}
{
\"timeStamp\": \"Fri 2020.03.27 01:10:40:1034 AM EDT\",
\"step\": \"D\"
}"
| eval data=split(data, "}")
| mvexpand data
| rex field=data "timeStamp\":\s\"(?<timeStamp>.*)\",\s*\"step\":\s\"(?<step>\w)"
| eval timeStamp_epoch=strptime(timeStamp, "%a %Y.%m.%d %I:%M:%S:%4N %p %Z")
| delta timeStamp_epoch p=1 AS diff
| eval diff=round(diff, 0)." sec"
| where isnotnull(diff)
| table step, diff
Thanks for the answer. Little more trouble. What needs to be done If we want to treat the input I provided above as a result of one transaction query instead of individual logs ?