Splunk Search

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

j_partsch
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, 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
0 Karma
1 Solution

ntaylorsplunk
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

ntaylorsplunk
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

j_partsch
Explorer

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

0 Karma

ppablo
Retired

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

j_partsch
Explorer

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

0 Karma

ppablo
Retired

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

0 Karma

sundareshr
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

j_partsch
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

j_partsch
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
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...