Splunk Search

tstats and a lookup

chirsf
Explorer

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.

0 Karma
1 Solution

peter_krammer
Communicator

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" ...)

View solution in original post

0 Karma

peter_krammer
Communicator

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" ...)
0 Karma

chirsf
Explorer

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
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 ...