topic Re: Search for active hosts over a period of time in Splunk Search

Search for active hosts over a period of time

jspears — Wed, 11 Apr 2012 00:56:06 GMT

I'm trying to check for hosts that were sending data last week and now are not, or newly added hosts. I don't think the answer here is actually a correct answer nor answers my problem.

So far what I am thinking is to create a lookup:

index=main earliest=-168h latest=-166h | dedup host | table host | outputlookup hosts_weekold.csv

Then run searches on new data to find hosts that are in the lookup but not in current results, or vice versa.

Re: Search for active hosts over a period of time

lguinn2 — Thu, 12 Apr 2012 05:44:00 GMT

index=_internal source=*metrics.log group="tcpin_connections" earliest=-7d@d latest=@d

| eval sourceHost=if(isnull(hostname), sourceHost,hostname)

| stats sum(kb) as KB_thisweek by sourceHost | eval KB_thisweek = round(KB_thisweek)

| join type=outer sourceHost

[search index=_internal source=*metrics.log group="tcpin_connections" earliest=-14d@d latest=-7d@d

| eval sourceHost=if(isnull(hostname), sourceHost,hostname)

| stats sum(kb) as KB_lastweek by sourceHost | eval KB_lastweek = round(KB_lastweek) ]

| eval NewThisWeek = if(KB_lastweek < 1,"New", " ")

| eval MissingThisWeek = if (KB_thisweek < 1, "Missing", " ")

| where KB_lastweek < 1 or KB_thisweek < 1

may be closer to what you want. This is based on the forwarders that connect to Splunk, not the host names that are assigned to the data.
Another alternative is to turn on the Deployment Monitor app that ships with Splunk...

Re: Search for active hosts over a period of time

jspears — Wed, 25 Apr 2012 12:34:51 GMT

This is a great answer! Unfortunately I do need to see the hosts missing from the data, not just missing forwarders.

Re: Search for active hosts over a period of time

lguinn2 — Wed, 25 Apr 2012 19:54:07 GMT

Here is a new answer - this one is based on the hosts represented in the data, not the forwarders.
It's another fun search!

index=* earliest=-7d@d latest=@d

| eval indextime=_indextime

| fields host indextime

| stats max(indextime) as LastIndexedThisWeek count as EventsThisWeek by host

| join type=outer host

[search index=* earliest=-14d@d latest=-7d@d

| eval indextime=_indextime

| fields host indextime

| stats max(indextime) as LastIndexedLastWeek count as EventsLastWeek by host

]

| eval NewThisWeek = if(EventsLastWeek < 1,"New", " ")

| eval MissingThisWeek = if (EventsThisWeek < 1, "Missing", " ")

| fieldformat LastIndexedThisWeek = strftime(LastIndexedThisWeek ,"%x %X")

| fieldformat LastIndexedLastWeek = strftime(LastIndexedLastWeek ,"%x %X")

| table host LastIndexedThisWeek EventsThisWeek LastIndexedLastWeek EventsLastWeek NewThisWeek MissingThisWeek

| where EventsThisWeek < 1 or EventsLastWeek < 1