Splunk Search

Find range between min and max values for field in transaction

matthewcanty
Communicator

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

Tags (2)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
Communicator

This works excellently. Thank you.

0 Karma

kristian_kolb
Ultra Champion

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
Ultra Champion

sorry. typos. fixed.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...