How to create a search for timechart plotting for top entity based on sorting?

[venky1544]

Hi i have a table where i obtained the values after sorting PCT_FREE in ascending order now i want to plot a timechart of avg of used_space only for first two entity "B" AND "D" however when i plot timechart it takes all the entity  any suggestions on how can i exclude then rest of the entity and plot the timechart 

time	PCT_FREE	USED_SPACE	Entity
17/07/2023 16:15	10.4	245	B
16/07/2023 16:15	10.5	233	B
15/07/2023 16:15	10.3	235	B
14/07/2023 16:15	10.6	232	B
17/07/2023 16:15	11	245	D
16/07/2023 16:15	11	233	D
15/07/2023 16:15	11	235	D
14/07/2023 16:15	11	232	D
17/07/2023 16:15	12	245	A
16/07/2023 16:15	12	233	A
15/07/2023 16:15	12	235	A
14/07/2023 16:15	12	232	A
17/07/2023 16:15	14	245	C
16/07/2023 16:15	14	233	C
15/07/2023 16:15	14	235	C
14/07/2023 16:15	14	232	C

[yuanliu]

This is very similar to another recent question.  My way to cheat that out was to use the ASCII values of numeric numbers (after proper padding) in eventstats.  There I used the traditional join-split trick.  If your instance is 8.0 and above, however, a little efficiency can be gained by using JSON functions, like this:

| eventstats avg(PCT_FREE) as avg_FREE by Entity
| eval FREE_Entity = json_object("FREE", tostring(round(avg_FREE, 2)), "Entity", Entity)
| eventstats values(FREE_Entity) as FREE_Entity
| eval bottom_Entity = mvappend(json_extract(mvindex(FREE_Entity, 0), "Entity"), json_extract(mvindex(FREE_Entity, 1), "Entity"))
| where Entity IN (bottom_Entity)

Note

1. I used avg in eventstats because the only way your sorting will make practical sense in the requirement is either the real comparison is sort by avg or by total.  If it is total, use sum instead of avg.
2. This method does not depend on presorting the entries.  Given that you only want two of bottom counts, you can go without sorting, or sort afterward when there are fewer rows.

[bowesmana]

Add

| where Entity="B" OR Entity="D"

to the end of your search

[vk1544]

@bowesmana  thanks for the reply but what if these values always change every day sometime entity A or C might be top  i don't specifically want to hardcode the names 

[bowesmana]

@vk1544 @venky1544 

Sorry, I didn't read your question well - you want the top two Enities, whatever they happen to be.

A simple way to do this is using the last 3 lines of this example search

| makeresults
| eval _raw="time	PCT_FREE	USED_SPACE	Entity
17/07/2023 16:15	10.4	245	B
16/07/2023 16:15	10.5	233	B
15/07/2023 16:15	10.3	235	B
14/07/2023 16:15	10.6	232	B
17/07/2023 16:15	11	245	D
16/07/2023 16:15	11	233	D
15/07/2023 16:15	11	235	D
14/07/2023 16:15	11	232	D
17/07/2023 16:15	12	245	A
16/07/2023 16:15	12	233	A
15/07/2023 16:15	12	235	A
14/07/2023 16:15	12	232	A
17/07/2023 16:15	14	245	C
16/07/2023 16:15	14	233	C
15/07/2023 16:15	14	235	C
14/07/2023 16:15	14	232	C"
| multikv forceheader=1
| table time PCT* USED* Entity
``` The above creates your example data set ```
| streamstats values(Entity) as entities
| where mvcount(entities)<=2
| fields - entities

The streamstats collects the different values of Entity to a new field called entities, then the where clause just removes all where the number of values is <= 2, leaving you with the higest two Entity values. Finally the fields statement removes that extra field.

May be a bit late, but hope this helps.