Find range between min and max values for field in transaction

[matthewcanty]

Hi everyone. I have this query which works really well. It is returning an identifier and list of descriptions, dates and sources. However I'd like to be able to find the range between the min and max dates after the transaction. I then want to only output results where the range is greater than 2 hours for example.

I think mvlist is the solution but I can't find any decent examples of its use.

index=dalprod Action=PublishedDefaultRule Date Id Source |
eval earliestDate = relative_time(now(), "-1d@d") | 
eval latestDate = relative_time(now(), "@d+7d") | 
eval Date = strptime(Date, "%d/%m/%Y %H:%M:%S") | 
where earliestDate < Date AND Date < latestDate | 
eval Date = strftime(Date ,"%d/%m/%Y %H:%M:%S") |
fields Id Date GroupId Description SportId Source | 
join Id [search index=dalprod Action=DALPublish Source SportId NOT Source=Gambit] | 
dedup Date GroupId | 
eventstats count by GroupId | 
where count>=2 | 
transaction GroupId | 
table GroupId Date Source Description SportId

Many thanks.
Matt

[martin_mueller · Accepted solution]

You could consider adding a call to range() to the eventstats, passing the strptime'd date into it. Then you should be able to filter before calculating the transaction. Something like this:

index=dalprod Action=PublishedDefaultRule Date Id Source |
eval earliestDate = relative_time(now(), "-1d@d") | 
eval latestDate = relative_time(now(), "@d+7d") | 
eval strpDate = strptime(Date, "%d/%m/%Y %H:%M:%S") | 
where earliestDate < strpDate AND strpDate < latestDate | 
fields Id strpDate Date GroupId Description SportId Source | 
join Id [search index=dalprod Action=DALPublish Source SportId NOT Source=Gambit] | 
dedup Date GroupId | 
eventstats range(strpDate) as seconds count by GroupId | 
where count>=2 AND seconds > 7200 | 
transaction GroupId | 
table GroupId Date Source Description SportId

You might even be able to filter before the join, think about whether that works with your data. The less you make join and transaction work the faster 🙂
Another thought, is the Date field different from _time? If not, you can use the standard time range to get rid of that earliestDate/latestDate malarkey.

[matthewcanty]

This works excellently. Thank you.

[kristian_kolb]

Wouldn't the duration field provide this information?

... | transaction GroupId | where duration>7200 | ...

or if you want to do it manually (or do other things). Keep the Date in epoch as a new field called eDate;

eval eDate = strptime(Date, "%d/%m/%Y %H:%M:%S") | 
where earliestDate < eDate AND eDate < latestDate | 
eval Date = strftime(eDate ,"%d/%m/%Y %H:%M:%S") |

That would give you an extra field to work on later without having to str(f|p)time again;

... | transaction GroupId 
| eventstats min(eDate) as mindate max(eDate) as maxdate by GroupId 
| eval range = maxdate - mindate 
| where range > 7200
| ...

Hope this helps,

Kristian

[kristian_kolb]

sorry. typos. fixed.