Splunk Search
Highlighted

conditions in look up

Path Finder

Hi All,

I have a lookup file which contains following values and my lookup name is "status_lookup.csv "

applicationlookup statuslookup
aplicationxxx status >=400 AND status < 500

I am calculating the sum of error rate using the below splunk query.

application="aplicationxxx" | lookup statuslookup.csv applicationlookup as application OUTPUT statuslookup | eval error=if(( status =statuslookup ),1,0) | stats sum(error) as error_rate.

This is not working as expected, because in if condition the lookup is treating it as a string instead of normal statement, can someone help me how, I can achieve this?

Tags (3)
0 Karma
Highlighted

Re: conditions in look up

Contributor

Hi!

Maybe in the stats part, you can try to put this:

| stats sum(eval(error="1")) as error_rate

It works in some of my searches, if omething of the lookup part is wrong I can try to look it with more caution.

0 Karma
Highlighted

Re: conditions in look up

Path Finder

Thanks for replying, you mean to change as shown below?

application="aplicationxxx" | lookup statuslookup.csv applicationlookup as application OUTPUT statuslookup | eval error=if(( status =statuslookup ),1,0) | stats sum(eval(error="1")) as error_rate

This didn't help, I tried keeping only status in lookup instead of entire statement, something like this

applicationlookup statuslookup
aplicationxxx 400

This worked properly, somehow look up is not accepting statements, is this limitation for lookup in splunk? if so is there any alternate way?

can you let me know is there any alternate way of achieving this.

0 Karma
Highlighted

Re: conditions in look up

Contributor

It seems somesoni2 answer you. Although with my knowledge I don't understand the whole answer. But he's right in the lookup part, the lookups are used to add fields and information, not filtering directly, for filtering you have to select the fields you want, as he does: | fields statuslookup (as eh does | table statuslookup, it's the same)

However, I'm not sure why it has to be renamed and also, I'm not quite sure how it works. I have enough knowledge of splunk, I've been using it for 5 months now. I'm sure he has the right answer, so ask him if you need more, I will be following this question to learn more of this.

Sorry not being able to help more 😞

0 Karma
Highlighted

Re: conditions in look up

SplunkTrust
SplunkTrust

The lookup tables are used for data enrichment, not really for data filters. You can filter data once you've additional field values (not expressions) from the lookup added to your search result.

For you case, I think you can just get away with using lookup in a subsearch, something like this (assuming you're running the search for one particular application, applicationxxx

application="aplicationxxx" [| inputlookup status_lookup.csv | search application_lookup="aplicationxxx" | table status_lookup | rename status_lookup as search ] | stats count as error_rate

The subsearch adds the value of the field status_lookup (which has to be renamed as search OR query) as a filter in the search and thus leaving only the error events. The resulting query, after subsearch is executed will be like this

  application="aplicationxxx" status >=400 AND status < 500 | stats count as error_rate
0 Karma