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!

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...

[Live Demo] Watch SOC transformation in action with the reimagined Splunk Enterprise ...

Overwhelmed SOC? Splunk ES Has Your Back Tool sprawl, alert fatigue, and endless context switching are making ...

What’s New & Next in Splunk SOAR

Security teams today are dealing with more alerts, more tools, and more pressure than ever.  Join us on ...