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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...