How do you add blank values to a table visualization?

[Esky73]

I have a visualisation that requires fields in a table and the search is :

index=X "Name"="X" sourcetype=csv EXEC_AVAILABILITY=201 OR 401 
| eval value = tonumber(replace(STEP_RESPONSE_TIME, "," , ""),10)/1000 
| eval name = strftime(_time,"%d/%m/%y") 
| eval order = strftime(_time,"%w") 
| eval status=case(value<=4.0,"GREEN",value>=4.0 AND value <=6.5, "AMBER", 1==1, "RED") 
| eval new_time = strptime("2018-01-01 " . strftime(_time, "%H:%M"), "%Y-%m-%d %H:%M") 
| rename new_time as _time 
| eval status = value 
| sort name, _time 
    | table _time, name, value, status, order

Now, if there is a gap in the data, then the table only renders the first few days — e.g.: if there is data for the 1st - 3rd of a month, then nothing til the 5th - 31st, for example, — it only renders the 1st to the 3rd in the table.

I've been playing around with adding blank values using | gentimes to populate the missing data points, but I can't seem to marry the two up. For example, this search gives me an empty visualization from the 1st of November to the present date

Now i want this — plus the search from above showing the values where present.

| gentimes start=11/1/2018 increment=1d 
| eval _time=starttime
| eval name = strftime(_time,"%d/%m/%y")  | eval order = strftime(_time,"%w")
| eval value=" "
| table _time, name, value, status

[bjoernjensen]

Hi,

basically you might want to use timechart to get a time series with span=1d in order to aggregate to one day. You could aggregate all your values with avg(xxx) as xxx or any other aggregation method (also list() might help with developing that spl).

Plain and expensive would be using join:
index=X "Name"="X" sourcetype=csv EXEC_AVAILABILITY=201 OR 401
| eval value = tonumber(replace(STEP_RESPONSE_TIME, "," , ""),10)/1000
| eval name = strftime(_time,"%d/%m/%y")
| eval order = strftime(_time,"%w")
| eval status=case(value<=4.0,"GREEN",value>=4.0 AND value <=6.5, "AMBER", 1==1, "RED")
| eval new_time = strptime("2018-01-01 " . strftime(_time, "%H:%M"), "%Y-%m-%d %H:%M")
| rename new_time as _time
| eval status = value
| sort name, _time
| table _time, name, value, status, order

| join type=inner _time [ 
 | gentimes start=11/1/2018 increment=1d 
 | eval _time=starttime
 | eval name = strftime(_time,"%d/%m/%y")  | eval order = strftime(_time,"%w")
 | eval value=" "
 | table _time, name, value, status
]

Cheerz,
Björn

[Esky73]

thanks but this doesn't give any statistical data that can be plotted so doesn't generate the visualisation.