How to compare results and assign values from Lookup?

[dhawanvarun]

Hello everyone,

Splunk beginner here!! Just trying to do something simple. I have a list of students being obtained from a simple splunk query with their respective marks. For example, a small sample of my current output -
SName, Marks
Neymar, 87
Ronaldo, 92
Messi, 98

Now, I have a lookup table which has a range of marks and the respective grade. Lookup table format -
LRange, HRange, Grade
95, 100, A
90, 95, B
85, 90, C

My objective is to compare and assign each student an appropriate grade. For example, Neymar has 87 marks so he will fall under greater than 85 and less 90, so 'C'. Want my final result to be displayed as follows -
Sname, Marks, Grade
Neymar, 87, C
Ronaldo, 92, B
Messi, 98, A

The query I wrote is which doesn't work -
..basic query.. |inputlookup stu_marks append=t where ("Marks">="LRange") AND ("Marks"<"HRange") | fields "Grade"

Any help is highly appreciated.

Regards,
V

[somesoni2 · Accepted solution]

If you really want to keep it dynamic (via lookup), I believe there are two ways.

Option 1: Join. It'll basically multiply your current student result set with number of rows in your lookup, so if you need to re-consider if both are really high. Below search assumes your lookup has less than 100 entries

Fixed typo

Your current search giving results with  fields SName, Marks
| join max=100 [| inputlookup stu_marks | table LRange, HRange, Grade]
| eval Grade=if( (Marks>=LRange) AND (Marks<HRang), Grade, null())
| where isnotnull(Grade) 

Option 2: Dynamically generate eval using lookup data. Works best when number of lookup entries are smaller

Fixed typo

Your current search giving results with  fields SName, Marks
| eval Grade=[| inputlookup stu_marks | eval search="Marks>=".LRange." AND Marks<".HRange.",\"".Grade."\"," | table search | format "" "" "" "" "" "" | eval search="case(".replace(replace(replace(search,",\"",","),"\\\\",""),"\"\"","").",1=1,\"Unknown\")" | eval search=replace(replace(search,",\s*,",","),"\"Marks","Marks")]

[dhawanvarun]

Thanks you for replying! I do need to keep lookup, so yes dynamic is the option I am looking at. I ran this and it yielded 'No results found.' which isn't true. Do you know what could be going wrong?

[somesoni2]

There were typos in the query before. I corrected them, can you try again? Also, which one are you trying?

[dhawanvarun]

I am using Option 1 and I thought the only type was with the If brackets '('.

[dhawanvarun]

Okay so I noticed something, when I look at the lookup file, I see all the values for LRange and HRange. However, I just used a simple join (removed the eval statement from your example) and all I can see is 0s and 1s in HRange and LRange.

[somesoni2]

There were typos for both bracket and extra double qoutes. Fixed now.
So when you run this, you get wrong values for LRange and HRange columns??

Your current search giving results with  fields SName, Marks
 | join max=100 [| inputlookup stu_marks | table LRange, HRange, Grade]

[dhawanvarun]

My bad, I just realized what I was doing wrong. I had removed the max statement instead of giving max=0. Thank you somesoni2! It works!!!

[adonio]

hello there,

although can be done with lookup, i feel it will be much easier using some eval command.
try this search anywhere and use the eval command as you wish:

| makeresults count=1 
| eval data = "Neymar,87 Ronaldo,92 Messi,98"
| makemv delim=" " data
| mvexpand data
| rex field=data "(?<sName>\w+),(?<Mark>\d{1,3})"
| eval Grade = case(Mark<=100 AND Mark>=95,"A",Mark<95 AND Mark>=90,"B",Mark<90 AND Mark>=85,"C")

hope it helps