Splunk Search

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

kmattern
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 cs_username to the matching IIS field. If the fields match then update the SoftwareVersion number with a value found in the cs_user_agent 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 cs_username 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

jkat54
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

jkat54
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

jkat54
SplunkTrust
SplunkTrust

If this worked can you please accept as the answer?

0 Karma

kmattern
Builder

Perfect!

Thanks

0 Karma

jkat54
SplunkTrust
SplunkTrust

Add type=outer to the join command...

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

kmattern
Builder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!