How do I extract the largest value between two strings?

[kingwaras]

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.

[renjith_nair]

@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

[kingwaras]

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

[renjith_nair]

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

[FrankVl]

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

[FrankVl · Accepted solution]

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