topic Re: How to compare results and assign values from Lookup? in Splunk Search

How to compare results and assign values from Lookup?

dhawanvarun — Thu, 15 Feb 2018 16:28:55 GMT

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

Re: How to compare results and assign values from Lookup?

adonio — Thu, 15 Feb 2018 17:16:32 GMT

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

Re: How to compare results and assign values from Lookup?

somesoni2 — Thu, 15 Feb 2018 17:28:45 GMT

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")]

Re: How to compare results and assign values from Lookup?

dhawanvarun — Thu, 15 Feb 2018 17:47:19 GMT

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?

Re: How to compare results and assign values from Lookup?

somesoni2 — Thu, 15 Feb 2018 18:42:25 GMT

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

Re: How to compare results and assign values from Lookup?

dhawanvarun — Thu, 15 Feb 2018 19:10:04 GMT

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

Re: How to compare results and assign values from Lookup?

dhawanvarun — Thu, 15 Feb 2018 19:17:29 GMT

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.

Re: How to compare results and assign values from Lookup?

somesoni2 — Thu, 15 Feb 2018 19:21:52 GMT

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]

Re: How to compare results and assign values from Lookup?

dhawanvarun — Thu, 15 Feb 2018 22:43:22 GMT

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!!!