tstats and a lookup

[chirsf]

I hope I explain this well. I have the following tstats search:

| tstats max(_time) AS _time WHERE index=_internal sourcetype=splunkd source=*metrics.log by host 

I also have a lookup table with hostnames in in a field called host set with a lookup definition under match type of WILDCARD(host). In the data returned by tstats some of the hostnames have an fqdn and some do not. The problem becomes the order of operations. Say I do this:

| tstats max(_time) AS _time WHERE index=_internal sourcetype=splunkd source=*metrics.log by host 
| lookup serverswithsplunkufjan2020 host OUTPUT host as match 
| eval "Sending Data?" = if(isnotnull(match), "Yes", "No")

Then it gives me more hosts than I'm looking for. It'll indeed show where there is a match. When I search in this manner:

| inputlookup serverswithsplunkufjan2020.csv
| join type=left host 
    [| tstats max(_time) AS _time WHERE index=_internal sourcetype=splunkd source=*metrics.log by host 
    | eval seen=1] 
| eval "Sending Data?" = case(seen=1, "Yes", isnull(true), "No")
| fields - seen

1. It's using a join and who wants that?
2. I can't seem to use lookup which I need for wildcards. I can use | inputlookup fine but I can't seem to make wildcard matching work there.

I could do something like host IN ("foohost1*", "foohost2*") to search for what I need to gather, but I'd like to build something dynamic.

[peter_krammer · Accepted solution]

you could filter after the lookup:

| tstats max(_time) AS _time WHERE index=_internal sourcetype=splunkd source=*metrics.log by host 
| lookup serverswithsplunkufjan2020 host OUTPUT host as match
| where isnotnull(match) 

depending on the amount of hosts in your lookup you can also do this to filter in tstats already:

| tstats max(_time) AS _time WHERE index=_internal sourcetype=splunkd source=*metrics.log [
    | inputlookup serverswithsplunkufjan2020 | table host 
] by host 

the subsearch will expand to:

(host="host1" OR host="host2" ...)

[chirsf]

This is what I ended up with:

| tstats max(_time) AS _time WHERE index=_internal sourcetype=splunkd source=*metrics.log by host 
| rex field=host "^(?[^\.]+)" 
| lookup serverswithsplunkufjan2020 host OUTPUT host as match 
| where isnotnull(match) 
| inputlookup serverswithsplunkufjan2020 append=true 
| rex field=host "^(?[^*]+)" 
| dedup host 
| eval "Sending Data?" = if(isnotnull(_time), "Yes", "No") 
| stats max(_time) AS _time by host "Sending Data?" 
| convert timeformat="%Y/%m/%d" ctime(_time) as "Last Send Date"
| fields - _time