How to Generate Bar Chart to Include Hosts with No Events?

[rgcurry]

I have a dashboard panel that displays the number of user sessions on a web server in a column chart. The user wants to know if we can display each of the hosts dedicated to his application even if there are no sessions in selected timeframe. Anything in the Time Picker less than 4hrs will show only a subset of the servers as the others will not have any sessions to report (this is currently only in our TEST Environment, so low volume of sessions -- for now). Is there a way to get Splunk to report each of the hosts reporting a particular sourcetype without any events during a particular timeframe?

My current search looks something like this:

index="{INDEX}" sourcetype="{SOURCETYPE}" ENV="$ENV$" (host="$host$" AND host="{HOST_FILTER}")| 
    dedup sessionID | 
    search NOT "*{SEARCH_FILTER}*"  | 
    stats count AS SessionCount by host

I know that by the nature of this search, there will be events ONLY for those hosts that have had sessions in operation during the specified timeframe. Basically what I am looking to do is force each host to report even if the value is 0 (zero). Can I 'inject' that info into the final search results?

[HiroshiSatoh · Accepted solution]

It's aggregating [SessionCount=0] is to add events in the Sub Search How is?

<your search> | append [| inputlookup LOOKUP.CSV | eval SessionCount=0 | table host,SessionCount ] | stats sum(SessionCount) as SessionCount by host

LOOKUP.CSV

host
hostA
hostB
hostC
etc...

Sub Search is also good in the search for the period can be extracted host.

[rgcurry]

I made a slighgt change to the code you suggested Hiroshi, I added the following to the end of my search so that the proper count is reported. 'Injecting' the 'extra' host into the results made a server with no sessions show as having 1. But now the chart shows what is needed and wanted by the user.

Thank you again for your suggestion. I appreciate your time and expertise.

The following is what I added to the end of the search to realign to the true session count value while reporting all of the servers:

| eval SessionCount=SessionCount-1

[rgcurry]

I will give this a try, it looks like it will do the trick. After I posted this, I got to wondering if I could use the lookup table I already had to "force feed" the host into the results but I did not think of using append to pull in that data. Nicely done Hiroshi! I will post back on the results. Right now -- all servers have been having active sessions over a 15min (our lowest timeframe) period.