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?
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".
"PART_NO":[ "2634702", "2634456","2634890",] },
"PART_NO":[ "2634702", ] },
My events are in json format like this.
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.
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.)
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
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.
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)
<search> | sort whatever_field | stats max(field)
should not differ.
If they do, there's something wrong somewhere else.
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.
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)
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.