topic Re: Substring lookup to enhance DB query results? in Splunk Search

Substring lookup to enhance DB query results?

sarge338 — Thu, 28 Sep 2017 19:20:22 GMT

Hello,

I am VERY new to Splunk. I have built some basic dashboards using DB queries, because the data is not (yet) being put directly into the Splunk database. With that said, I would like to enhance my current dashboard with some additional data defined in a CSV file. To be more specific my dashboard contains phone numbers. My CSV file contains the location data of North American Numbering Plan area codes and prefixes (NPA-NXX). I would like to lookup the location of the caller, based on the NPA-NXX, and include that in my dashboard.

Given my limited knowledge/skill set with Splunk, I have a few questions:
1) Is this even possible in Splunk?
2) Does Splunk support data/format manipulation within the search string, such as using RegEx, or can you define a substring to look for?
3) Are there any existing tutorials around these areas that could help guide me to a solution?

Any help would be greatly appreciated!!

EXAMPLE (dots added for spacing purposes)
[Query Results]
Phone Number .......... Call Count
+12345678901........... 12

[CSV Entry]
NPA-NXX .................. Location
234-567 .................... Anytown, USA

Desired Output
Phone Number .............. Location .................................. Call Count
+12345678901............... Anytown, USA ......................... 12

Re: Substring lookup to enhance DB query results?

Sukisen1981 — Thu, 28 Sep 2017 20:07:22 GMT

1) Is this even possible in Splunk?
Yes...BUT just like an excel look up...you need to have 1 common field value...NOT just a field name...in your case if we extact 234-567 from your query we can match it with your csv look up like a common 'key' field join in SQL/EXCEL
2) Does Splunk support data/format manipulation within the search string, such as using RegEx, or can you define a substring to look for?OHH yes 🙂
3) Are there any existing tutorials around these areas that could help guide me to a solution?
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/Knowledge/ConfigureCSVlookups
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/Knowledge/DefineanautomaticlookupinSplunkWeb
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Inputlookup
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup

Re: Substring lookup to enhance DB query results?

sarge338 — Thu, 28 Sep 2017 20:39:35 GMT

Sukisen1981,

Thank you for the quick response!!

I'll work on figuring out how to "extract" the area code and prefix (234-567) from the query results for this purpose. I appreciate the links! I'm so new, I'm not even sure what I'm searching for sometimes. 🙂

Re: Substring lookup to enhance DB query results?

lfedak_splunk — Thu, 28 Sep 2017 20:50:40 GMT

Hey @sarge338, welcome to the Splunk community! When you're responding to answers on Answer posts please use the comment feature rather than posting a new "answer". As well, if @sukisen1981 is able to find the solution for you please accept their answer so you can award karma points and close the question! 🙂 You can also upvote to award points.

Re: Substring lookup to enhance DB query results?

Sukisen1981 — Thu, 28 Sep 2017 21:04:00 GMT

Try this for prefix:
|eval prfxubstr("Phone Number",3,5)"-"+substr("Phone Number",6,8

Re: Substring lookup to enhance DB query results?

sarge338 — Wed, 15 Nov 2017 21:14:52 GMT

Sukisen1981,

Thank you for the sample code. I had to manipulate it a little, but it was close enough to get me where I needed to be.

The assistance is much appreciated.