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.

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...