topic Re: Lookup with hundreds values for one field in Splunk Search

Lookup with hundreds values for one field

gaok123 — Mon, 22 Jun 2020 19:26:41 GMT

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. 🙂

Re: Lookup with hundreds values for one field

richgalloway — Mon, 22 Jun 2020 19:38:37 GMT

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?

Re: Lookup with hundreds values for one field

gaok123 — Tue, 23 Jun 2020 02:32:35 GMT

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".

Re: Lookup with hundreds values for one field

richgalloway — Tue, 23 Jun 2020 12:31:57 GMT

Again, I'm not seeing the problem. You already have cell_number in the index so why bother with the CSV file?

Re: Lookup with hundreds values for one field

gaok123 — Tue, 23 Jun 2020 12:42:18 GMT

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

Re: Lookup with hundreds values for one field

ayush1906 — Tue, 23 Jun 2020 13:06:30 GMT

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 😊 !!

Re: Lookup with hundreds values for one field

to4kawa — Tue, 23 Jun 2020 13:14:47 GMT

https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Aboutlookupsandfieldactions

This question is

no query

no log

no detail

https://docs.splunk.com/Documentation/Splunkbase/splunkbase/Answers/Questions

Have you seen the reference?

Re: Lookup with hundreds values for one field

gaok123 — Tue, 23 Jun 2020 15:07:39 GMT

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.

Re: Lookup with hundreds values for one field

richgalloway — Tue, 23 Jun 2020 17:25:40 GMT

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.

Re: Lookup with hundreds values for one field

DalJeanis — Tue, 23 Jun 2020 17:59:37 GMT

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.