Archive

date modifier

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

Legend

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

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

Legend

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

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

Legend

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

0 Karma

Communicator

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

0 Karma

Legend

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