Splunk Search

Substring lookup to enhance DB query results?

sarge338
Path Finder

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

0 Karma
1 Solution

Sukisen1981
Champion

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

View solution in original post

Sukisen1981
Champion

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

Sukisen1981
Champion

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

sarge338
Path Finder

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.

0 Karma

sarge338
Path Finder

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. 🙂

0 Karma

lfedak_splunk
Splunk Employee
Splunk Employee

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.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...