topic Re: Use results from search as input to a lookup in Splunk Search

Use results from search as input to a lookup

tnkoehn — Fri, 05 Apr 2013 21:19:21 GMT

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!

Re: Use results from search as input to a lookup

kml_uvce — Mon, 28 Sep 2020 13:41:05 GMT

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

Re: Use results from search as input to a lookup

tnkoehn — Mon, 08 Apr 2013 15:02:06 GMT

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!

Re: Use results from search as input to a lookup

tnkoehn — Mon, 28 Sep 2020 13:41:43 GMT

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

Re: Use results from search as input to a lookup

kml_uvce — Tue, 09 Apr 2013 00:39:50 GMT

this is perfect...

Re: Use results from search as input to a lookup

tnkoehn — Tue, 09 Apr 2013 15:09:26 GMT

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