Splunk Search

date modifier

Shark2112
Communicator

Hey guys.

I want to find hosts for all time which haven't any messages last 7 days, trying this:

index=main source=syslog | dedup host | table _time host | search latest=-7d

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

you should do this in two steps:

  • populate a lookup with all you hosts using a search or uploading a csv,
  • creating a search that check if all the hosts in the lookup are present in the last 7 days

populate the lookup
| metasearch index=main source=syslog | dedup host | table host | outputlookup HostList.csv
(Obviously you have to create the lookup in which the column name is "host" before to execute this search)

check the host lookup
| inputlookup HostList.csv
| eval count=0
| eval host=upper(host)
| append [
|metasearch index=main latest=-7d
| eval host=upper(host)
| stats count by host
]
| stats sum(count) AS Total by host
| where Total=0
| table host

The result will be a list of hosts in the lookup that doesn't send any log in the last seven days.

You could have the same result putting the search used to populate the lookup in the second search (instead of |inputlookup ...), but if you have many events it will be very slow.

Try this.

Bye.
Giuseppe

View solution in original post

0 Karma

woodcock
Esteemed Legend

You could just do a very long search (like last 6 months) like this:

index=main source=syslog | eval host=lower(host) | dedup host | table _time host | eval daysAgo=(now()-_time)/(60*60*24) | where daysAgo > 30
0 Karma

gcusello
SplunkTrust
SplunkTrust

you should do this in two steps:

  • populate a lookup with all you hosts using a search or uploading a csv,
  • creating a search that check if all the hosts in the lookup are present in the last 7 days

populate the lookup
| metasearch index=main source=syslog | dedup host | table host | outputlookup HostList.csv
(Obviously you have to create the lookup in which the column name is "host" before to execute this search)

check the host lookup
| inputlookup HostList.csv
| eval count=0
| eval host=upper(host)
| append [
|metasearch index=main latest=-7d
| eval host=upper(host)
| stats count by host
]
| stats sum(count) AS Total by host
| where Total=0
| table host

The result will be a list of hosts in the lookup that doesn't send any log in the last seven days.

You could have the same result putting the search used to populate the lookup in the second search (instead of |inputlookup ...), but if you have many events it will be very slow.

Try this.

Bye.
Giuseppe

0 Karma

Shark2112
Communicator

thx for help!
i can't see difference between make output file for all time and make search with same parameter, so this request work fine for me:

index=main source=syslog | dedup host | table host
| eval count=0
| eval host=upper(host)
| append [
|metasearch index=main latest=-7d
| eval host=upper(host)
| stats count by host
]
| stats sum(count) AS Total by host
| where Total=0
| table host

0 Karma

gcusello
SplunkTrust
SplunkTrust

It depends by the number of events: if you have many events it will be very slow!
Bye.
Giuseppe

0 Karma

Shark2112
Communicator

and and you explain what difference between search and metasearch in subsearch?? thx

0 Karma

gcusello
SplunkTrust
SplunkTrust

For metasearch see this http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Metasearch
but the importance is to use a lookup instead a search on a large time period.
Bye.
Giuseppe

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