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 |
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
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
This is not working with my xyseries table. any other solution ?
That probably depends on why it isn't working - can you provide more details?
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
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?
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
| 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