Splunk Search

Use results from search as input to a lookup

tnkoehn
Path Finder

I have an initial search that will find the top 100 Called_Numbers by the number of calls they are taking. A simplified version is:

index=Sonus sourcetype=Sonus earliest=-1h (START OR STOP)
|stats count(eval(CDR_Type=="START")) as starts, count(eval(CDR_Type=="STOP")) as stops by Called_Number
|eval calls=starts-stops
|sort -calls
|head 100

I then want to take those 100 Called_Numbers and perform a lookup to find client name and application. The lookup looks like this:

|lookup client_info.csv apn as Called_Number OUTPUT client as Client_Name, program as App_Name
|table Called_Number Client_Name App_Name calls

And the client_info.csv file contains 100's of thousands of records and looks like this:

apn,client,program
1420102,Client A,Widgets
2004029,Client B,Web
2007172,Client C,Mobile
2007226,Client D,Customer Service
2910509,Client E,Maintenance
.
.

After the search and the lookup, the output should be:

Called_Number     Client_Name     App_Name     calls
1234567           ABC Client      ABC App      159
2345678           XYZ Client      XYZ App      231
.
.

How do I do that in one search? I've tried subsearches and looked thru the Splunkbase for days. Can't seem to figure it out. Thanks for any help!

Tags (1)
0 Karma
1 Solution

tnkoehn
Path Finder

Here's the answer. Thanks for the help and confirmation.

index=Sonus sourcetype=Sonus earliest=-1h (START OR STOP)
|stats count(eval(CDR_Type=="START")) as starts, count(eval(CDR_Type=="STOP")) as stops by Called_Number
|eval calls=starts-stops
|sort -calls
|head 100
|eval apn=mvappend(Called_Number,calls)
|lookup client_info.csv apn OUTPUT apn as Called_Number, client as Client_Name, program as App_Name
|table Called_Number Client_Name App_Name calls

View solution in original post

0 Karma

tnkoehn
Path Finder

Here's the answer. Thanks for the help and confirmation.

index=Sonus sourcetype=Sonus earliest=-1h (START OR STOP)
|stats count(eval(CDR_Type=="START")) as starts, count(eval(CDR_Type=="STOP")) as stops by Called_Number
|eval calls=starts-stops
|sort -calls
|head 100
|eval apn=mvappend(Called_Number,calls)
|lookup client_info.csv apn OUTPUT apn as Called_Number, client as Client_Name, program as App_Name
|table Called_Number Client_Name App_Name calls
0 Karma

kml_uvce
Builder

this is perfect...

0 Karma

kml_uvce
Builder

try this
index=Sonus sourcetype=Sonus earliest=-1h (START OR STOP) [|inputlookup client_info.csv |fields apn , client,program|rename apn as Called_Number,client as Client_Name, program as App_Name
]|stats count(eval(CDR_Type=="START")) as starts, count(eval(CDR_Type=="STOP")) as stops by Called_Number
|eval calls=starts-stops
|sort -calls
|head 100

-Kamal Bisht

0 Karma

tnkoehn
Path Finder

I think I may have figured this out (because it works!), although it may not be the best way. Let me know what you think:

index=Sonus sourcetype=Sonus earliest=-1h (START OR STOP)
|stats count(eval(CDR_Type=="START")) as starts, count(eval(CDR_Type=="STOP")) as stops by Called_Number
|eval calls=starts-stops
|sort -calls
|head 100
|eval apn=mvappend(Called_Number,calls)
|lookup client_info.csv apn as apn OUTPUT apn as Called_Number, client as Client_Name, program as App_Name
|table Called_Number Client_Name App_Name calls

0 Karma

tnkoehn
Path Finder

This doesn't work. Just to be clear, I want to find the top 100 called numbers first, and then use those called numbers to perform the client lookup. I edited my original post to include the format of the client_info.csv file and attempted to make the question a little more understandable . Let me know if you need anything else that can help. Thanks!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...