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

[kmattern]

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

[jkat54 · Accepted solution]

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.

[jkat54]

If this worked can you please accept as the answer?

[kmattern]

Perfect!

Thanks

[jkat54]

Add type=outer to the join command...

  join type=outer cs_username [...]

[kmattern]

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