topic Re: If a value is not in a lookup table, can I return that value as the OUTPUT field? in Splunk Search

If a value is not in a lookup table, can I return that value as the OUTPUT field?

j_partsch — Tue, 29 Sep 2020 10:45:20 GMT

I apologize if this has already been answered, but I looked through numerous inquiries on answers.splunk.com and did not find one to match my issue. I have a CSV lookup table of CustID, CustName, src_ip. I am charting the top 10 accesses by scr_ip over a time period. If the src_ip is in the lookup table, I want to display the CustName, else display src_ip.

CustID,CustName,src_ip
99999,Customer1,123.123.123.123
88888,Customer2,123.45.67.8
77777, Customer3,123.67.8.3
...

This is my search:

sourcetype=access_combined | lookup TestIPs.csv src_ip OUTPUT CustName | chart count over CustName| sort -count limit=10

This results in a chart of only the Customer hits, but does not show any information from hits from non-customers. Theoretically, non-customer could be in the top 10 site users.

Sample Output

CustName     count
Customer3    10
Customer1    6
Customer2    3

Desired Output

CustName          count
111.222.333.4     20
1.2.3.4           15
Customer3         10
4.9.1.6           7
Customer1         6
Customer2         3
1.1.1.1           2
1.2.3.45          1
2.3.4.5           1
3.5.7.9           1

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

sundareshr — Thu, 25 Aug 2016 16:20:21 GMT

Try this

sourcetype=access_combined | lookup TestIPs.csv src_ip OUTPUT CustName | eval CustName=coalesce(CustName, src_ip) | chart count over CustName| sort -count limit=10

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

j_partsch — Thu, 25 Aug 2016 16:55:53 GMT

This did not work, every record shows src_ip in the CustName field now including for customers, there are no customer names shown.

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

ntaylorsplunk — Thu, 25 Aug 2016 17:25:03 GMT

Try this instead

sourcetype=access_combined | lookup TestIPs.csv src_ip OUTPUT CustName | eval CustName=if(len(CustName) > 2, CustName, src_ip) | chart count over CustName| sort -count limit=10

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

j_partsch — Thu, 25 Aug 2016 17:31:01 GMT

This works! Thank you very much ntaylorsplunk!!!!

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

j_partsch — Thu, 25 Aug 2016 17:33:49 GMT

I downvoted this post because this did not work, every record shows src_ip in the custname field now including for customers, there are no customer names shown.

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

ppablo — Tue, 30 Aug 2016 22:50:24 GMT

Hi @j_partsch

Glad you found an answer to your question through @ntaylorsplunk 🙂 Please don't forget to resolve the post by clicking "Accept" directly below the answer.

I noticed you gave sundareshr a downvote for his attempted answer, but please note that for voting etiquette in this forum, it's best to only use downvoting for answers/suggestions that could potentially do harm to your environment. If an answer is helpful, it's encouraged to upvote it and that will already bump it up in the list of answers. If an attempted answer didn't get you what you needed, then no need to downvote someone for simply trying to help you out. We want to encourage community oriented behavior, not deter people from trying to help.

For more info in how voting etiquette works in this community, feel free to check out the discussion on this previous Splunk Answers post.
https://answers.splunk.com/answers/244111/proper-etiquette-and-timing-for-voting-here-on-ans.html

Cheers

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

j_partsch — Wed, 31 Aug 2016 11:47:34 GMT

Thank you for the pointers. I was unaware of the proper-etiquette on answers.splunk.com.

Re: If a value is not in a lookup table, can I return that value as the OUTPUT field?

ppablo — Tue, 13 Sep 2016 00:11:55 GMT

No problem. Glad you got the help you needed from the community 🙂