Splunk Search

How do I extract the largest value between two strings?

kingwaras
Engager

Hi all,

is there a way to compare two strings in a search query?

I would extract only the value greater than of Level2 value in the hierarchy_lookup file and that it starts with my same letter.
In the file hierarchy_lookup.csv, the values are:

[Name] [Level2]
Elizabeth A1
William A2
Madison A3
Victoria B1
James B2
Daniel B3
Matthew B3

I will try to explain my issue better.

My level is A2. In my case, I would extract all names that have a level lower than mine, and that start with my same letter (A in my case)

You can see my first step of the query below.

| inputlookup hierarchy_lookup.csv 
 | where Level2 > [| inputlookup hierarchy_lookup.csv  | 
          where [| rest /services/authentication/current-context | table username | rename username as Name] 
            | table Level2 | rename Level2 as search] 

Thanks in advance.

0 Karma
1 Solution

FrankVl
Ultra Champion

Try the following. It takes the full lookup. It than adds an extra line containing your name and level, with the level value copied to the myLevel field. It then copies that myLevel field to all other lines. It then splits up the myLevel and Level2 field in each line into the letter and the digit. And finally filters for same letter, higher digit.

| inputlookup hierarchy_lookup.csv 
| append [ 
   | rest /services/authentication/current-context
   | table username
   | rename username as Name
   | lookup hierarchy_lookup.csv Name
   | eval myLevel = Level2
]
| eventstats values(myLevel) as myLevel
| eval myLevelLetter = substr(myLevel,0,1)
| eval myLevelDigit = substr(myLevel,1,1)
| eval LevelLetter = substr(Level2,0,1)
| eval LevelDigit = substr(Level2,1,1)
| where myLevelLetter = LevelLetter AND LevelDigit > myLevelDigit

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@kingwaras ,
In the above example , the lowest level for A should be A3 instead of A2 . Isn't it ?
With the mentioned data, try the below and see if it works for you

| inputlookup hierarchy_lookup.csv|rex field="Level2" "(?<Alphabet>[A-Z])(?<Number>\d)"|eventstats max(Number) as max by Alphabet|where Number<max

Based on the actual Level2 data , you need to adjust the rex

Happy Splunking!
0 Karma

kingwaras
Engager

Hi @renjith.nair. Yes, correct. The lowest levels are A3 and A2, but your script extract also values B2 and C2.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

OK if you are looking for only "A" , just filter with |where Number<max AND Alphabet="A"

Happy Splunking!
0 Karma

FrankVl
Ultra Champion

The thing is that he wants this dynamic based on the currently logged in user, as far as I understand.

0 Karma

FrankVl
Ultra Champion

Try the following. It takes the full lookup. It than adds an extra line containing your name and level, with the level value copied to the myLevel field. It then copies that myLevel field to all other lines. It then splits up the myLevel and Level2 field in each line into the letter and the digit. And finally filters for same letter, higher digit.

| inputlookup hierarchy_lookup.csv 
| append [ 
   | rest /services/authentication/current-context
   | table username
   | rename username as Name
   | lookup hierarchy_lookup.csv Name
   | eval myLevel = Level2
]
| eventstats values(myLevel) as myLevel
| eval myLevelLetter = substr(myLevel,0,1)
| eval myLevelDigit = substr(myLevel,1,1)
| eval LevelLetter = substr(Level2,0,1)
| eval LevelDigit = substr(Level2,1,1)
| where myLevelLetter = LevelLetter AND LevelDigit > myLevelDigit
0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...