topic Re: timechart plotting for top entity based on sorting in Splunk Search

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

venky1544 — Mon, 24 Jul 2023 17:27:46 GMT

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

Re: timechart plotting for top entity based on sorting

bowesmana — Tue, 18 Jul 2023 15:57:46 GMT

Add

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

to the end of your search

Re: timechart plotting for top entity based on sorting

vk1544 — Mon, 24 Jul 2023 14:29:11 GMT

@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

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

yuanliu — Tue, 25 Jul 2023 08:41:26 GMT

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

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

Re: timechart plotting for top entity based on sorting

bowesmana — Sun, 06 Aug 2023 23:54:29 GMT

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