Splunk Search

How to append a field and it's value from lookup csv to a main search

Explorer

I have a search, main and subsearch. The subsearch uses a lookup table (a csv file). The csv file has 4 columns, count, devID, src, username. The main search does not have a field called devID at all. I want the devID field from the subsearch to be in the stats command after the main the search. The results for username field in the csv are stored in space delimited format, hence the makemv command.

Example of search:
index=myindex [|inputlookup singledevIDmusername.csv |makemv delim=" " "username" | fields username ] |dedup acctnum username |fillnull value=NULL | stats count values(acctnum) as acctnum by Client username src devID

So far I've been unable to get the complete search to work. I've tried using the eval cmd to create the devID field in the main search but that didn't seem to work....i.e. adding in the devID field to the stats command.

Thanks for the help.

Tags (3)
0 Karma

Explorer

Here is the final search, which works perfectly for my needs.

index=rcecom [|inputlookup singledevIDmusername.csv | makemv delim=" " "username" | fields username ] | dedup acctnum username | fillnull value=NULL | stats count values(acctnum) as acctnum by Client username src | lookup singledevIDm_username.csv src OUTPUT devID | stats count values(devID) as devID by acctnum, username, Client, src | sort - devID | fields - count

0 Karma

Esteemed Legend

Your lookup file is trash and splunk will not be able to use it. You CAN NOT have multi-valued fields in lookup file (although, interestingly enough, you can in a KV Store). Unless you first fix your lookupfile to use fully-qualified rows (every cell in the row/column matrix has a value), then you can do nothing with Splunk.

0 Karma

Explorer

Have some tact when replying to posts. The lookup file is not trash, you can have multi-values fields in the lookup file and it works as needed. Maybe not perfect, but it works. For me to pull the devid field as I wanted, I simply used the src field.

Here is the final search, which works perfectly for my needs.

index=rcecom [|inputlookup singledevIDmusername.csv | makemv delim=" " "username" | fields username ] | dedup acctnum username | fillnull value=NULL | stats count values(acctnum) as acctnum by Client username src | lookup singledevIDm_username.csv src OUTPUT devID | stats count values(devID) as devID by acctnum, username, Client, src | sort - devID | fields - count

0 Karma

SplunkTrust
SplunkTrust

If you can update the lookup table, update the username field to have single values and they try like this

index=my_index [|inputlookup single_devID_m_username.csv | fields username ] |dedup acctnum username |fillnull value=NULL | stats count values(acctnum) as acctnum by Client username src | lookup single_devID_m_username.csv username OUTPUT devID 
| table Client username src devID  acctnum 

If you can update the lookup, try this (inefficient)

index=my_index [|inputlookup single_devID_m_username.csv |makemv delim=" " "username" | fields username ] |dedup acctnum username |fillnull value=NULL | stats count values(acctnum) as acctnum by Client username src | join username [[|inputlookup single_devID_m_username.csv |makemv delim=" " "username" | fields username devID | dedup username devID ]]  | table Client username src devID  acctnum  | table Client username src devID  acctnum 
0 Karma

Legend

In your lookupfile, can the user name have multiple values per row? If not, you can add a lookup command before the stats command in your query. If not, can you share sample data from your .csv

http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Lookup

0 Karma

Explorer

Sorry, maybe I'm not being clear. I want to add in the devID field from the lookup file into the final "stats" cmd results. I believe the issue to be that the deviceID does not exist in the search that is not the subsearch.

I can't provide an actual sample but the below is an example of the lookup file:
count devID src username
3 123456789 1.2.3.4 test1
test2
test3
2 987654321 5.6.7.8 user1
user2

What I'd like my final stats cmd to show - results
Client username src devID acctnum
client1 test1 1.2.3.4 123456789 abc1234
client2 test1 1.2.3.4 123456789 defg567
client3 user1 5.6.7.8 987654321 987gjhk
client4 user2 5.6.7.8 987654321 678defr

I hope this helps and thanks again.

0 Karma