Splunk Search

How to edit my search to populate select fields in a lookup table?

Builder

I have a lookup table that contains a list of about 50 computers. The columns are ComputerName, SoftwareVersion, cs_username.

Currently only ten of the computers are online. The balance will be coming online in the coming weeks. I want to build a scheduled saved search that will read the contents of the lookup table and compare the csusername to the matching IIS field. If the fields match then update the SoftwareVersion number with a value found in the csuseragent field, otherwise set the value to "UNK". As time passes all of the records will have the correct SoftwareVersion. This search returns only the matching csusername records from IIS logs but not the non-matching cs_username records from the lookup table. ComputerInfo is an extracted field from the User Agent field. What am I missing?

index=iis sourcetype=iis Location | dedup cs_username
| lookup CustomerBoxes.csv cs_username OUTPUT ComputerName, SoftwareVersion, cs_username
| makemv delim="/" ComputerInfo
| eval Version=mvindex(ComputerInfo,1)
| table ComputerName, SoftwareVersion, cs_username
| outputlookup CustomerBoxes.csv
0 Karma
1 Solution

SplunkTrust
SplunkTrust

start with the lookup and inputlookup command, kinda like this maybe... you have some fields in there and commands that i dont know where to put:

| inputlookup CustomerBoxes.csv 
| join type=outer cs_username [search index=iis sourcetype=iis Location | dedup cs_username  ]
| table ComputerName, SoftwareVersion, cs_username
| outputlookup CustomerBoxes.csv

??? something like that. The lookup you're doing will only return matches. Instead if you start with the lookup, and join/append/etc the iis data to it, you will keep the original data from the lookup.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

start with the lookup and inputlookup command, kinda like this maybe... you have some fields in there and commands that i dont know where to put:

| inputlookup CustomerBoxes.csv 
| join type=outer cs_username [search index=iis sourcetype=iis Location | dedup cs_username  ]
| table ComputerName, SoftwareVersion, cs_username
| outputlookup CustomerBoxes.csv

??? something like that. The lookup you're doing will only return matches. Instead if you start with the lookup, and join/append/etc the iis data to it, you will keep the original data from the lookup.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

If this worked can you please accept as the answer?

0 Karma

Builder

Perfect!

Thanks

0 Karma

SplunkTrust
SplunkTrust

Add type=outer to the join command...

  join type=outer cs_username [...]
0 Karma

Builder

Our suggested search returns exactly the same set of 10 computers. I tried ideas like that already. Thanks

0 Karma