How to show matched and unmatched lookup results?

[vinit_masaun]

Hello,

I have the following query that shows the results of all the values from the splunk events that matched with the values in the lookup table; however I would also like to display those values in the lookup table that are not present in the splunk events:

| metadata type=hosts index=_internal
| rex field=host "(?<host>.+)--.+)"
| lookup mylookup Name as host OUTPUT Name "IP Address" as IP Classification "Used for" as used_for
| fillnull value="No match"
| search Classification=Production used_for!=*Citrix* used_for!=*Virtualization*
| stats c by host,Name,IP,Classification,used_for
| fields - c

How can I show both matched and unmatched values?

[bowesmana]

This is a classic "missing hosts" problem, which simply involves appending the lookup at the end and then aggregating to remove the duplicates, i.e.

| metadata type=hosts index=_internal
| rex field=host "(?<host>.+)--.+)"
| lookup mylookup Name as host OUTPUT Name "IP Address" as IP Classification "Used for" as used_for
| fillnull value="No match"
| search Classification=Production used_for!=*Citrix* used_for!=*Virtualization*
| stats c by host,Name,IP,Classification,used_for
| fields - c
| append [
  | inputlookup mylookup 
  | fields Name "IP Address" as IP Classification "Used for" as used_for
]
| stats first(*) as * by Name IP

add as many fields to the by clause as needed

[vinit_masaun]

I tried the above append command with my original query but still got the same results.  It's only showing values that matched.

[bowesmana]

Please show what you tried.

[vinit_masaun]

| metadata type=hosts index=_internal
| rex field=host "(?<host>.+)(--.+|---.+)"
| lookup mylookup Name as host OUTPUT Name "IP Address" as IP Classification "Used for" as used_for | fill null value="No match"| search Classification=Production used_for!=*Citrix* used_for!=*Virtualization*
| stats c by host,Name,IP,Classification,used_for
| fields - c
| append
[| inputlookup mylookup
| fields Name "IP Address" as IP Classification "Used for" as used_for ]
| stats first(*) as * by Name IP

[bowesmana]

My mistake - I copy/pasted your renames but that doesn't work as fields statement - please use this append

| append [
  | inputlookup mylookup 
  | fields Name "IP Address" Classification "Used for"
  | rename "IP Address" as IP "Used for" as used_for
]

[vinit_masaun]

Thank You!  Your example didn't quite work as is but it pointed me in the right direction and the following query is what ended up working for my use case:

| metadata type=hosts index=_internal
| rex field=host "(?<host>.+)(--.+|---.+)"
| lookup mylookup Name as host OUTPUT Name "IP Address" as IP Classification "Used for" as used_for
| stats c by host,Name,IP,Classification,used_for
| fields - c
| append
[| inputlookup mylookup
| fields Name "IP Address" Classification "Used for"
| rename "IP Address" as IP "Used for" as used_for ]
| fillnull value="Missing in Splunk" host
| search Classification=Production used_for!=*Citrix* used_for!=*Virtualization* used_for!="ESX Server"
| stats first(*) as * by Name,IP,Classification,used_for
| table host,Name,IP,Classification,used_for