How to create Timechart when no splitby available?

[dvg06]

Hi Splunkers

Need some help with a timechart query please.

index=linux host IN (a,b,c,d,e) | timechart span=1week eval(avg(CPU) * avg(MEM)) BY host

This works well if there is atleast an event per host. But I wanted to show zero value when there are no events for a particular host. Is that possible?

eg: I have events only for a,b,c but still wanted to show zero for d and e hosts. 

[bowesmana · Accepted solution]

This is a very common question - there is no data in Splunk for d and e, so you need to tell Splunk that you want information about hosts it does not know about.

The most normal way to do this is to create a lookup with your (5?) hosts in, e.g. my_hosts.csv

host
a
b
c
d
e

 then do your search

index=linux [ | inputlookup my_hosts.csv ]
| timechart span=1week eval(avg(CPU) * avg(MEM)) BY host
| appendcols [ 
  | inputlookup append=t my_hosts.csv
  | eval {host}=0
  | fields - host
  | stats max(*) as * 
]
| filldown

which will make the time chart and then add columns for each of the missing hosts