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

[anooshac]

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?

[ITWhisperer]

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

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

[anooshac]

Sorry that was my typing mistake.

[PickleRick]

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".

[anooshac]

{

"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.

[ITWhisperer]

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

| eval task_no=tonumber(task_no)

[anooshac]

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

[ITWhisperer]

 Can you share your SPL?

[anooshac]

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.

[ITWhisperer]

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.)

[anooshac]

This is returned as maxThis should have been the max.

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

[ITWhisperer]

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

[anooshac]

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)?

[PickleRick]

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.

[anooshac]

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.

[PickleRick]

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.

[anooshac]

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.

[PickleRick]

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)

[ITWhisperer]

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

[anooshac]

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.

[ITWhisperer]

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