Splunk Search

How to get value of a field corresponding to max value of another field

ggangwar
Path Finder

Hi,
I have a table with the fields 'loadtime', 'application', and 'user'.
First I want to compute the maximum value of loadtime for all application. Then I want to create a table/chart which has application field's value in rows, corresponding maximum loadtime value in column. I also want to have user field's value for the maximum loadtime calculated for each application.
alt text
Is there any way to accomplish this using Splunk?

0 Karma
1 Solution

ggangwar
Path Finder

Solution to my query:

 search_string|streamstats max(LoadTime) as max_time by Application|sort +Application -LoadTime|streamstats first(max_time) as max_time by Application|where LoadTime=max_time|table Application,max_time,User

If you need to use stats function like sum as well on any of the field, you can do as follows:

 search_string|eventstats sum(LoadTime) as TotalTime by Application| streamstats max(LoadTime) as max_time by Application|sort +Application -LoadTime|streamstats first(max_time) as max_time by Application|where LoadTime=max_time|table Application,max_time,User,TotalTime

View solution in original post

0 Karma

ggangwar
Path Finder

Solution to my query:

 search_string|streamstats max(LoadTime) as max_time by Application|sort +Application -LoadTime|streamstats first(max_time) as max_time by Application|where LoadTime=max_time|table Application,max_time,User

If you need to use stats function like sum as well on any of the field, you can do as follows:

 search_string|eventstats sum(LoadTime) as TotalTime by Application| streamstats max(LoadTime) as max_time by Application|sort +Application -LoadTime|streamstats first(max_time) as max_time by Application|where LoadTime=max_time|table Application,max_time,User,TotalTime
0 Karma

ggangwar
Path Finder

Can anyone please help in above query?

0 Karma

ggangwar
Path Finder

I have got the solution. Using streamstats I can achieve these stats.

search_string|streamstats max(LoadTime) as max_time by Application|sort +Application -LoadTime|streamstats first(max_time) as max_time by Application|where LoadTime=max_time|table Application,max_time,User
0 Karma

HiroshiSatoh
Champion

Is this OK?

(your search)|stats max(loadtime) as loadtime by application,user
0 Karma

ggangwar
Path Finder

No, it displays results by both columns i.e. for every user wise I will get the result using suggested query.

0 Karma

niketn
Legend

@ggangwar your requirement and table snapshot is confusing. As per your question you need ...maximum value of loadtime for all application which should have only one value for Application1 i.e. 120.

As per your screenshot seems like you have two max loadTimes per application per user i.e. 120 for user abc and 100 from user xyz for Application1. So, as per your table @HiroshiSatoh seems to be correct. So, if it is not we would need further raw events i.e. data sample with Application/s and Users/s with various loadtime and the final output that you need.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ggangwar
Path Finder

Apologies for confusion and typo error from me. Its Application2 in second row:

                 LoadTime   User

Application1 120 abc
Application2 100 xyz

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...