Splunk Statics Table - How to get the max of column and use it to evaluate each row

[stevenulbrich]

Splunk Statics Table - How to get the max of column and use it to evaluate each row

Hello, looking for advice and recommendations.
I have a splunk query 
index=idx_source1 source=*app.log* clientEntitlementsCacheDataRetriever clientCount|table _time,host,clientCount

I am trying to get the max value of the clientCount  then use that value to compare to the each host.  The idea to make are report/alert of host not having all the clients in cache.

I suspect a subquery could be used but not sure  that will work on a report. 

Need Help - from banging my Head more

Steven

[stevenulbrich]

I will give it a try tomorrow and update with my results. 

[tread_splunk]

Do you want max value of clientCount for each host?  In which case... 

| eventstats max(clientCount) by host

Or max value of clientCount regardless of host?  In which case ...

| eventstats max(clientCount)

[scelikok · Accepted solution]

Hi @stevenulbrich,

You can try below;

index=idx_source1 source=*app.log* clientEntitlementsCacheDataRetriever clientCount
| fields _time,host,clientCount
| eventstats max(clientCount) as max_clientCount
| eval status=if(clientCount<max_clientCount,"NotOK","OK")
| table _time host clientCount status