How to create timechart to show server log ingestion stats including servers with 0 ingestion?

[kelvinchan]

I'm trying to create a timechart statistics to show server log ingestion status by days:

index=zzz (host=Server1 OR host=Server2 OR host=Server3) | timechart span=1d count by host

All 3 servers are shown in the Statistics/Line Chart if they have ingested data into Splunk before, but if for example 'Server3' has not been setup to ingest data into Splunk before, and I would like this server to be appearing in the statistics/line chart as well (shown as 0 on all the _time field), how can I do that? Can anyone advise me on this? Thank you.

[DalJeanis · Accepted solution]

Here's a couple of methods that would work...

For a small number of servers, you could do it this way... using a loop to manually add the field to each record if it is missing from all of them.

index=zzz (host="Server1" OR host="Server2" OR host="Server3")
| timechart span=1d count by host
| foreach Server1 Server2 Server3 [eval <<FIELD>>=coalesce(<<FIELD>>,0)]

Or, you could do it this way...

index=zzz (host="Server1" OR host="Server2" OR host="Server3")
| bin _time span=1d
| stats count as daycount by host _time

| rename COMMENT as "Create a single zero record for each server with the lowest _time"
| appendpipe [
    | stats min(_time) as _time 
    | eval host=mvappend("Server1", "Server2", "Server3") 
    | eval daycount=0
    | mvexpand  host
    ] 

| rename COMMENT as "Now timechart, rolling in the zero records.  Fillnull will zero-fill any missing days"
| timechart span=1d sum(daycount) by host
| fillnull

---

For a larger number of records, you are going to want to put the host names into a lookup table or csv. For this example I used a lookup, and assumed the name of the table was myHosts.csv and the server field in the lookup was Host.

index=zzz  ( [|inputlookup myHosts.csv | table Host | rename Host as host ] )
| bin _time span=1d
| stats count as daycount by host _time

| rename COMMENT as "Create a single zero record for each server with the lowest _time"
| appendpipe [
    | rename COMMENT as "find the lowest _time"
    | stats min(_time) as _time 

    | rename COMMENT as "add in all the host values from the lookup"
    | inputlookup append=t myHosts.csv

    | rename COMMENT as "Roll them all together in one record."
    | rename COMMENT as "We  use 'list' instead of 'values' in case there are more than 100 records in the lookup"
    | rename COMMENT as "We won't bother to `dedup` them because they are about to be timecharted back together anyway." 
    | stats values(_time) as _time list(Host) as host

    | rename COMMENT as "add the zero and expand them to one record per host " 
    | eval daycount=0
    | mvexpand  host
    ] 

| rename COMMENT as "Now timechart, rolling in the zero records.  fillnull will fill the zeroes."
| timechart span=1d sum(daycount) by host useother=f
| fillnull

---

For an absolutely MASSIVE number of servers - more than 1000 - you are going to want to use the lookup table as a lookup, so change the first line to be like this...

index=zzz 
| fields host
| lookup Host as host myHosts.csv OUTPUT Host as foundme
| where isnotnull(foundme)

In this case, the appendpipe section might also need to change for efficiency's sake to an append...

| rename COMMENT as "Create a single zero record for each server using _time as earliest from the search"
| append  [

    | rename COMMENT as "add in all the host values from the lookup"
    | inputlookup append=t myHosts.csv
    | fields Host
    | rename Host as host

    | rename COMMENT as "add in the `earliest` from the search as _time, then table out the records"
    | addinfo
    | eval _time = info_min_time
    | eval daycount=0
    | table _time host daycount
    ] 

---

All of the above methods are mix-and-match to your needs. Once you understand why each one works, feel free to move them around and test them against each other to see which ones are most efficient to your use case and your data.

[kelvinchan]

@DalJeanis,

Thank you very much. Your solutions worked for my ad-hoc report and long term report (both small number of servers and large number of servers)!! However, I do noted that the timechart has a limit of 10 servers only in the line chart, I used this to show all servers in the line chart:

| timechart span=1d sum(daycount) by host useother=f limit=0

Anyway, it looks very messy in the line chart as I have 3000+ servers to display, so I decided to filter according to server role in my inventory list and export it to Excel spreadsheet and do a pivot to show my manager server count based on the last ingested time grouped into day (for specific server roles only, which I can change it according to which server role he wants to look at). My final solution is as below.

index=zzz [
| inputlookup ServerInventory.csv
| search Status="Active" Server_Role="Web"
| fields ServerName
| rename ServerName as host
]
| append [
inputlookup append=t ServerInventory.csv
| search Status="Active" Server_Role="Web"
| fields ServerName
| rename ServerName as host
| addinfo
| eval _time = min_time_info
| table _time host
]
| stats max(_time) as _time by host

[DalJeanis]

@kelvinchan - Yes, for that many hosts, I would not use timechart at all. Timechart is a presentation tool, no more, no less.

I"d have to say, for that final use case, you'd want to look at tstats instead. All you are doing is finding the highest _time value in a given index for each host. Try the code in the accepted answer by @somesoni2 on this one:
https://answers.splunk.com/answers/497834/how-to-use-tstats-to-show-the-last-event-and-event.html

If that answer helps you, please give Somesh's answer an upvote over there.

[kelvinchan]

@DalJeanis, thanks!! Should have figured that out to speed up the search and performance. Somesh's answer works great, I've upvoted his answer. Thanks.

[YoungDaniel]

Try to add this to your search .
index=zzz (host=Server1 OR host=Server2 OR host=Server3)
| eval server3 = "server3"
| rename server3 as host
| timechart span=1d count by host
| fillnull value="0"
This should work.

[kelvinchan]

@YoungDaniel, your solution isn't working, but thank you for your help in suggesting a solution. Appreciate that. 🙂

[DalJeanis]

Upvote for your polite expression of appreciation.

[DalJeanis]

@YoungDaniel -

That will add "server3" as host on every record, resulting in doubling the response for server3 and adding the other two servers to server3 as well.

If you do your add work in an append or appendpipe, then it would only create new records.

---

I was wrong... it actually overwrote the other host values, so it rolled everything together into Server3. Here's a run-anywhere code sample to show how that came out..

| makeresults count=100 
| eval host=mvappend("Server1","Server2")
| mvexpand host  
| eval _time = _time -500000 + (random() %500000)  
| sort _time

| eval server3 = "server3"
| rename server3 as host
| timechart span=1d count by host
| fillnull value="0"