Splunk Search
Highlighted

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

Explorer

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, srcip. I am charting the top 10 accesses by scrip over a time period. If the srcip is in the lookup table, I want to display the CustName, else display srcip.

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
0 Karma
Highlighted

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

Legend

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
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Explorer

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

View solution in original post

Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Community Manager
Community Manager

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

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Community Manager
Community Manager

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

0 Karma