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!
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
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
this is perfect...
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
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
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!