Dashboards & Visualizations

How do you add blank values to a table visualization?

Esky73
Builder

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
0 Karma

bjoernjensen
Contributor

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

0 Karma

Esky73
Builder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...