I have a lookup file which contains following values and my lookup name is "status_lookup.csv "
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?
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.
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
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.
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 😞
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,
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
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