Splunk Search

Highest value selection from table

jerinvarghese
Communicator

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:

TimeIPSLA1IPSLA2IPSLA3
10:13:00 38 10.1
10:14:0077.77  
10:23:0077 35 9.89
10:34:0078.35  
10:37:00  10.76
10:43:007836.2910.61
11:13:0079  
11:14:0072.82  
11:23:00 36.33 
11:24:0073.02  
11:33:00 37.67

 


Requirement : 

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

Expected output table: 

TimeIPSLA1IPSLA2IPSLA3Highest
10:13:00 38 10.138
10:14:0077.77  77.77
10:23:0077 35 9.8977
10:34:0078.35  78.35
10:37:00  10.7610.76
10:43:007836.2910.6178
11:13:0079  79
11:14:0072.82  72.82
11:23:00 36.33 36.33
11:24:0073.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
Labels (6)
0 Karma
1 Solution

tscroggins
Motivator

@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

View solution in original post

0 Karma

tscroggins
Motivator

@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
0 Karma

jerinvarghese
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

jerinvarghese
Communicator

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: 

 

TimeCACCO-WANINF092CATRC-WANINF092USHCO-WANINF092
11:13:00 36.34 
11:23:00  9
11:24:0073.02  
11:33:00 36.23 
11:44:0072.07  
12:12:00 36 
12:13:00 36.15 
12:14:0072.04  
12:23:00 36.15 
12:33:0072  
12:37:00  10
12:43:00  10.76
13:13:00 36.27 
13:33:0074  
13:34:0072.17  
13:43:00  11
14:13:00 36.25 
14:14:0072.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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

jerinvarghese
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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

0 Karma