How to write lookup to include Non-Matches?

[fzuazo]

Greetings,

I have a query I'm working on using tstats and lookup.

My lookup is named hosts_sites and has two columns, hosts and site.

My sample query is below;

| tstats latest(_time) as latest where index=main by host
| lookup hosts_sites hosts as host OUTPUT site
| table host, site, latest

How can I make sure that my table includes non-matches. I want to make sure that hosts in the lookup that were not matched are included in the table so they can be addressed/remediated

[somesoni2]

Try like this

| tstats latest(_time) as latest where index=main by host
| append [| inputlookup hosts_sites | table host site ]
| stats values(site) as site values(latest) as latest by host
| table host, site, latest

[fzuazo]

Thanks for this, I will give it a shot !

[yuanliu]

Does your query not list non-matches?  lookup command does not filter out any event so non-matches should be already included.

[fzuazo]

Unfortunately, not.

If there is a hostname in my lookup table that does not have a corresponding value in the indexed events it will not show up in my results.