topic Re: Show a timechart of all hosts even if 0 values exist in Splunk Search

Show a timechart of all hosts even if 0 values exist

Lgo — Thu, 17 Aug 2017 01:14:20 GMT

I'm attempting to write a query to show a timechart of the number of results for each host per minute, which is easy enough using the following query

index=index basesearch | timechart count by host span=1m

It returns exactly what I'm after, except if there are 0 results in the base search for a specific host it doesn't include a column for it.

If there are results for two hosts but not the 3rd I want it to return the following
--------------Host 1 Host 2 Host 3
Minute1 ------2---------1-----------0
Minute2 ------3---------0-----------0
Minute3 ------2---------6-----------0

The number of hosts will always be the same so if needed can be specified somehow in the search

Re: Show a timechart of all hosts even if 0 values exist

HiroshiSatoh — Thu, 17 Aug 2017 04:54:58 GMT

There may be more efficient search sentences･･･

| tstats count where index=index  by host
| map maxsearches=10000 search="search index=index basesearch  host=$host$
      | timechart span=1m count
      | eval host=\"$host$\""
| timechart span=1m sum(count) as count by host

Replace tstats with the LOOKUP file if possible
|inputlookuo XXXX|table host

Re: Show a timechart of all hosts even if 0 values exist

somesoni2 — Thu, 17 Aug 2017 18:35:47 GMT

Try like this (since host names are fixed)

index=index basesearch | timechart count by host span=1m | table _time Host1 Host2 Host3 | fillnull value=0