conditions in look up

[smaran06]

Hi All,

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

application_lookup status_lookup
aplicationxxx status >=400 AND status < 500

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

application="aplicationxxx" | lookup status_lookup.csv application_lookup as application OUTPUT status_lookup | eval error=if(( status =status_lookup ),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?

[somesoni2]

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

[marina_rovira]

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.

[smaran06]

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

application="aplicationxxx" | lookup status_lookup.csv application_lookup as application OUTPUT status_lookup | eval error=if(( status =status_lookup ),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

application_lookup status_lookup
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.

[marina_rovira]

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 status_lookup (as eh does | table status_lookup, 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 😞