Splunk Search

Lookup with hundreds values for one field

gaok123
Observer

Still new to Splunk, seeking for some help. 

I have a index=account_Information, with account_number, cell_number, etc.    I want to list the account_number and the cell_number associated. 

I have a list of hundreds account_numbers in a csv file. I uploaded the csv file but how to use it? 

My search:   (how to replace the ORs)

index=account_Information account_Number_1 OR account_Number_2 OR account_number_3 ...  |  table account_number cell_number

Thanks a lot. 🙂 

Labels (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust
You have both an index with account information and a CSV with account information. What do you want to do with them? What problem are you trying to solve?
Have you looked at the 'lookup' command?
---
If this reply helps you, Karma would be appreciated.
0 Karma

gaok123
Observer

Thanks for reply. 

I have a raw data index=account_information.   In the raw data, each entry has fields such as   account_number, cell_number, customer_name, address, product, etc. The raw data has (let's say) a million entries. 

I want to search several hundred customer's cell_number, by the known account_number. I copied accont_number in a csv file and uploaded. wandering how to use the csv. 

Will look into "lookup". 

 

0 Karma

to4kawa
Ultra Champion
0 Karma

gaok123
Observer

O, Yes. 

Here's the sample log as My_Log. 

[13/Mar/2018:18:24:02] Account_ID=5036 Code=B Cell_Number=6024298300471575 18767
[13/Mar/2018:18:23:46] Account_ID=7026 Code=C Cell_Number=8702194102896748 13876
[13/Mar/2018:18:23:31] Account_ID=1043 Code=B Cell_Number=2063718909897951 12345
[13/Mar/2018:18:22:59] Account_ID=1243 Code=C Cell_Number=8768831614147676 34466
[13/Mar/2018:18:21:02] Account_ID=4536 Code=B Cell_Number=6024298300471575 34676
[13/Mar/2018:18:20:46] Account_ID=2367 Code=C Cell_Number=54019g3677596748 87765
[13/Mar/2018:18:19:31] Account_ID=4146 Code=B Cell_Number=9476648906654451 15123
[13/Mar/2018:18:18:59] Account_ID=3467 Code=B Cell_Number=1038675849147346 25343

I'm interested in cell_number, input is Account_ID, few hundreds of them. 

To search a single result, I can use

Index=My_log Account_ID=5036 | table Account_ID Cell_Number

To search two result, I can use

Index=My_log Account_ID=5036 OR Account_ID=4146 | table Account_ID Cell_Number

My question is how to search hundreds Account_Id at one shot. 

 

I though I can use a csv file. So I uploaded accountId.csv with one column as Account_ID. 

Sample of accountId.csv 

Account_ID
5036
1243
 4146

 

Tried following , didn't work. 

index=My_log | stats count by Cell_Number | lookup accountId.csv Account_ID output Account_ID | table Cell_Number

 

Hope above examples explain me well. 

Thank a lot. 

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Try this:

 

index=My_log 
| stats count by Account_ID Cell_Number 
| lookup accountId.csv Account_ID output Account_ID as foundme
| where Account_ID = foundme
| table Account_ID Cell_Number

 

 

Notes:

1) You have to keep all the fields you need in the stats command somehow, or they will not exist afterwards.

2) When you output the lookup results, you need to give it a new name or you won't know whether it was found or not.

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The examples help a lot.  I believe you can use a subsearch to do what you want.

index=my_log [ | inputlookup accountId.csv | fields Account_ID | format ]
| table Account_ID Cell_Number

The subsearch reads the CSV file and formats the results into 

(Account_ID=5036) OR (Account_ID=4146) , etc.

which becomes part of the main search and should get you just a few hundred results.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust
Again, I'm not seeing the problem. You already have cell_number in the index so why bother with the CSV file?
---
If this reply helps you, Karma would be appreciated.
0 Karma

gaok123
Observer

The problem is I have hundreds account_number. I want a single search for these hundreds result.   

0 Karma

ayush1906
Path Finder

Hi @gaok123 

 

Please try running following search:

 

index=account_Information | table account_number cell_number


after this, you can manipulate your records 🙂 

 

Upvote if it helps 😊 !! 

 

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...