Splunk Search

How to compare last value with the 7th last value?

aguasd12
Observer
How to compare last value with the second last value?
 

Say I have a column with N records in it
88
22
67. -->
44
55
12
44
75
80 -->

I want to compare the last record 80 with that of 67( last value and want to write whether the value was 'greater' or 'smaller' in the output.

In above case 55 was greater so my output should say GREATER. Do we have any command to accomplish this?

Labels (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

How to compare last value with the second last value?
....
I want to compare the last record 80 with that of 67( last value and want to write whether the value was 'greater' or 'smaller' in the output.

In above case 55 was greater so my output should say GREATER. ...


You may need to describe the use case with consistency.  The title says last vs 7th last, which kinda agrees with 80 vs 67.  Then, the opening sentence says last with second last; and the last sentence cites a number 55 which is neither the 7th last nor the second last.

Here, I'll take 7th last.  The following statement also needs clarification:


Say I have a column with N records in it

Does this mean that the column is multivalued with 7 records, or does it mean that you have 7 separate events with this column?

If former, a semantical interpretation of your requirement (compare with 7th last) is

| eval output = if(tonumber(mvindex(column, -1)) - tonumber(mvindex(column, -7)) > 0, "GREATER", "SMALLER or EQUAL")

If latter, you first make the column multivalued with original sequence.

| stats list(column) as column
| eval output = if(tonumber(mvindex(column, -1)) - tonumber(mvindex(column, -7)) > 0, "GREATER", "SMALLER or EQUAL")

Here, I also do not know if you want EQUAL to be a separate output so I'm using the simplest if() function.  If you need a separate EQUAL, it would be more economic to save output of mvindex to variables (fields) before applying case() command.

Hope this helps.

yeahnah
Motivator

Hi @aguasd12 

An interesting use case.  Something like this should work...

| makeresults
| eval n=mvrange(0,80)
| mvexpand n
| eval n=substr(tostring(n * random()), 1, 2)
  ``` above just creates dummy results ```
  ``` add the following to you query ```
| streamstats count
| where count=67 OR count=80
| table count n
| transpose header_field="count"
| eval status=case(('67'=='80'), "EQUAL", ('67' > '80'), "GREATER THAN", ('67' < '80'), "LESS THAN")

Hope that helps

0 Karma

yeahnah
Motivator

On rereading your question I see I misunderstood it, initially.  This should meet you use case 

 

| makeresults
| eval n=mvrange(0,100)
| mvexpand n
| eval n=substr(tostring(n * random()), 1, 2)
  ``` above just creates dummy results ```
  ``` add the following to your query ```
| streamstats count
| eventstats max(count) as total_rows
| eval 7thlast=(total_rows-7)
| where count='7thlast' OR count='total_rows'
| table count n 7thlast total_rows
| transpose
| where column="n"
| rename "row 1" AS 7thlast, "row 2" AS last
| eval status=case(('7thlast'=='last'), "EQUAL", ('7thlast' > 'last'), "GREATER THAN", ('7thlast' < 'last'), "LESS THAN")

 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...