topic Re: Highest value selection from table in Splunk Search

Highest value selection from table

jerinvarghese — Fri, 23 Apr 2021 15:30:15 GMT

Hi All,

I am having challenge to filter the highest value and prepare a new column.

Code:

Table am getting:

Time	IPSLA1	IPSLA2	IPSLA3
10:13:00		38	10.1
10:14:00	77.77
10:23:00	77	35	9.89
10:34:00	78.35
10:37:00			10.76
10:43:00	78	36.29	10.61
11:13:00	79
11:14:00	72.82
11:23:00		36.33
11:24:00	73.02
11:33:00		37.67

Requirement :

I want the highest value to be populated on to a last new Colum.

Expected output table:

Time	IPSLA1	IPSLA2	IPSLA3	Highest
10:13:00		38	10.1	38
10:14:00	77.77			77.77
10:23:00	77	35	9.89	77
10:34:00	78.35			78.35
10:37:00			10.76	10.76
10:43:00	78	36.29	10.61	78
11:13:00	79			79
11:14:00	72.82			72.82
11:23:00		36.33		36.33
11:24:00	73.02			73.02
11:33:00		37.67		37.67

Also the "From Device" list is :

index=nw_ppm | table "From Device"

From Device

IPSLA1

IPSLA2

IPSLA3

Re: Highest value selection from table

ITWhisperer — Fri, 23 Apr 2021 16:04:51 GMT

| makeresults | eval _raw="Time,IPSLA1,IPSLA2,IPSLA3 10:13:00,,38,10.1 10:14:00,77.77,, 10:23:00,77,35,9.89 10:34:00,78.35,, 10:37:00,,,10.76 10:43:00,78,36.29,10.61 11:13:00,79,, 11:14:00,72.82,, 11:23:00,,36.33, 11:24:00,73.02,, 11:33:00,,37.67," | multikv forceheader=1 | fields - _* linecount | foreach IP* [eval highest=if(isnull(highest),<<FIELD>>,if('<<FIELD>>'>highest,<<FIELD>>,highest))]

If all your devices can't be expressed in a wildcard that excludes the Time field, you can add an extra condition to the eval to ignore the Time field

Re: Highest value selection from table

jerinvarghese — Sat, 24 Apr 2021 15:45:15 GMT

This is not working with my xyseries table. any other solution ?

Re: Highest value selection from table

ITWhisperer — Sat, 24 Apr 2021 15:51:40 GMT

That probably depends on why it isn't working - can you provide more details?

Re: Highest value selection from table

jerinvarghese — Sat, 24 Apr 2021 16:07:54 GMT

The complete code is

Below is my exact output that i got from the search:

Time	CACCO-WANINF092	CATRC-WANINF092	USHCO-WANINF092
11:13:00		36.34
11:23:00			9
11:24:00	73.02
11:33:00		36.23
11:44:00	72.07
12:12:00		36
12:13:00		36.15
12:14:00	72.04
12:23:00		36.15
12:33:00	72
12:37:00			10
12:43:00			10.76
13:13:00		36.27
13:33:00	74
13:34:00	72.17
13:43:00			11
14:13:00		36.25
14:14:00	72.07
14:22:00			11
14:23:00			11.79
14:33:00			10

atleast one Probe device will scan and find the latency at any time. We need only the highest latency they recorded, and that value should the required data. comparing the above 3 devices.

Hope this helps

Re: Highest value selection from table

ITWhisperer — Sat, 24 Apr 2021 16:53:01 GMT

Try this:

| foreach * [eval highest=if("<<FIELD>>"="Time",highest,if(isnull(highest),<<FIELD>>,if('<<FIELD>>'>highest,<<FIELD>>,highest)))]

Having said that, I am not sure what your search is trying to do. For example, the dedup will find the first of each combination of the fields. What is the intention of the search?

Re: Highest value selection from table

jerinvarghese — Sat, 24 Apr 2021 17:28:46 GMT

I have a website, that is been continuously polled by these 3 devices. It monitored for the Latency to that server. Ultimate Aim is a Line graph that shows only the highest values w.r.t time

Re: Highest value selection from table

tscroggins — Sat, 24 Apr 2021 18:56:33 GMT

@jerinvarghese

I love @ITWhisperer's use of foreach. Here's an alternative solution using appendpipe: