Splunk Search

Why is stats(max) is not giving correct results?

anooshac
Communicator

Hi all,

I have a set of data and i used stats(max) to get the maximum task number of every group. But the maximum number are not proper for most of the groups. I used the following query,

| stats max(task_no) as "Latest Task " by group

Can anyone tell is there anything should i add to get the maximum value?

Labels (4)
Tags (2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Your syntax for the stats command is not correct - try this

| stats max(task_no) as "Latest Task" by group

 

0 Karma

anooshac
Communicator

Sorry that was my typing mistake.

0 Karma

PickleRick
Ultra Champion

Hard to say without seeing your events and results but the typical case where arithmetic operations and comparisons don't seem to be working properly is when you work on text fields which contain "numeric" values.

In this case the sorting would be lexicographic so "95" would be "more" than "123".

0 Karma

anooshac
Communicator

{

"Task_no":"5",

"Group": "G1",

"EXECUTION_DATE":1648081994535,

"STATUS":"FAILURE",

"DURATION":1951628,

"PARTS":[

{ "NAME":"abc",

"PART_NO":[ "2634702", "2634456","2634890",] },

{ "NAME":"xyz",

"PART_NO":[ "2634702", ] },

] }

 

My events are in json format like this.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Your task_no looks like a string, try converting it to a number 

| eval task_no=tonumber(task_no)

anooshac
Communicator

Already tried this but this does not give the correct results.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

 Can you share your SPL?

0 Karma

anooshac
Communicator
myindex| eval Task_no=tonumber(Task_no)|eventstats max(Task_no) as max_task by Group|where Task_no=max_task|table Group,max_task,EXECUTION_DATE,STATUS|sort Group

This is the query i am using. For some of the Groups max values comes properly. But for some it is not considering the maximum number.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

So, it works, just not for all the data. Sounds like an issue with the data. Or at least, the SPL not being able to cope with the actual data you have. Can you share some of the "failing" data, e.g. the event it should have treated as max and the event it returned as max? (Obfuscate any personal data of course.)

0 Karma

anooshac
Communicator

This is returned as maxThis is returned as maxThis should have been the max.This should have been the max.

 

 

 

 

 

 

 

5 is returned as max but the correct value should be 23.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It looks like Task_no is being treated as a string (although I have not been able to reproduce the issue with the example data you have shared).

Can you show the results from your search so we can see how Task_no is treated

ITWhisperer_0-1657706183938.png

 

0 Karma

anooshac
Communicator

This is the result in which i have printed group and Task_no.This is the result in which i have printed group and Task_no.

 

 

 

 

Yes, it is treated as string. Is there any solution apart from tostring(Task_no)?

0 Karma

PickleRick
Ultra Champion

Are you sure you're not mixing tonumber() and tostring()? If you use tostring() where you should have used tonumber(), you'll get the results you're reporting.

0 Karma

anooshac
Communicator

Hi @PickleRick  , Thanks for the help. I was able to solve the problem. This was becuase of the sort command. The | sort Group command was in the beginning of the query , i placed it at the end and the stats max() was giving correct values. I don't know how the sort was affecting the values.

0 Karma

PickleRick
Ultra Champion

That is strange. sorting your data, apart from making the search take longer, should not affect your summary statistics. Results of

<search> | stats max(field)

and

<search> | sort whatever_field | stats max(field)

should not differ.

If they do, there's something wrong somewhere else.

0 Karma

anooshac
Communicator

I have a big query where i calculate different data and join them into a table. When i changed the sort commands position i was able to get the required results. I am not sure how was this possible and how a sort command can impact the result.

0 Karma

PickleRick
Ultra Champion

OK. If it's a big enough search, you might be hitting sort limits (10000 rows if I remember correctly). You could try sort with limit=0 parameter but that might turn out to be a resource-intensive operation.

Always think about what you want to achieve and whether you need to do all the operations and if you need them at all. (like - for example - sorting data only to do stats max seems pointless)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I also noticed that you are running in Fast mode - does the problem still occur in verbose mode?

0 Karma

anooshac
Communicator

Hi @ITWhisperer , Thanks for the help. I was able to solve the problem. This was becuase of the sort command. The | sort Group command was in the beginning of the query , i placed it at the end and the stats max() was giving correct values. I don't know how the sort was affecting the values.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It would be useful to see the full searches, both before and after the fix, to help others avoid the same issue.

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...