Highest value selection from table

[jerinvarghese]

Hi All,

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

Code: 

index=nw_ppm
| table "From Device", "To Device", "Latency", "Time (UTC/GMT)"
| search Latency!=0
| eval Latency = round(Latency, 2)
| rename "Time (UTC/GMT)" as Time
| xyseries  Time  "From Device" "Latency"

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

[tscroggins · Accepted solution]

@jerinvarghese 

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

index=nw_ppm
| table "From Device", "To Device", "Latency", "Time (UTC/GMT)"
| search Latency!=0
| eval Latency = round(Latency, 2)
| rename "Time (UTC/GMT)" as Time
| appendpipe 
    [| stats max(Latency) as Latency by Time 
    | eval "From Device"="Highest"] 
| xyseries Time "From Device" Latency 
| table Time * Highest

[jerinvarghese]

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

[ITWhisperer]

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

[jerinvarghese]

The complete code is  

index=nw_ppm
| dedup "From Device", "To Device", "Latency"
| table "From Device", "To Device", "Latency", "Time (UTC/GMT)"
| search Latency!=0
| eval Latency = round(Latency, 2)
| rex field="Time (UTC/GMT)" "(?P<Date>[^\s]+)\s(?P<Time>[^\s]+)"
| xyseries  Time  "From Device" "Latency"

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

[ITWhisperer]

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?

[jerinvarghese]

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

[ITWhisperer]

| 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