All Apps and Add-ons

Heatmap - Date column automatically append hour. How to format and remove

h52huang
Path Finder

Hi, I sent an question but seems like it was not delivered by my poor network.

I have a heatmap. For raw events, I have a column as Date where example values are like 2018-05-21. my X contains all timewindow string, 01:00:00-01:00:20 for 24 hours. Z is a count value.

On heatmap, when I hover, it shows:
x: 01:00:00-01:00:20
y: 2018-05-21 06h (or 12h, or 00h)
z: 12

Is there a way to remove the automatically appended hour characters on the heatmap please? They are not in the raw data column. Do I do data formation and then hide? How to do it please? Could someone give an example please? Or, how can I resolve this in more straightforward ways please?

Thank you very much.

0 Karma
1 Solution

aljohnson_splun
Splunk Employee
Splunk Employee

I see. I think the visualization's framework, plotly.js, is automatically converting your date into a a string.

Can you try converting your timestamp to a different format before you do chart? For example,

| eval date_time_as_unix_timestamp = strptime(Date, "%F")
| eval Date = strftime(date_time_as_unix_timestamp, "%Y %b %d")

View solution in original post

aljohnson_splun
Splunk Employee
Splunk Employee

I see. I think the visualization's framework, plotly.js, is automatically converting your date into a a string.

Can you try converting your timestamp to a different format before you do chart? For example,

| eval date_time_as_unix_timestamp = strptime(Date, "%F")
| eval Date = strftime(date_time_as_unix_timestamp, "%Y %b %d")

aljohnson_splun
Splunk Employee
Splunk Employee

Can you post a few rows of the dataset (or one equivalent) so that I can help you?

0 Karma

h52huang
Path Finder

@aljohnson_splunk @aljohnson
Thank you very much.

Date is strings of dates like "2018-05-07"
TimeWindow is string of fixed 20-min window for 24 hours.

TimeWindow 2018-05-07 2018-05-08 2018-05-09 2018-05-10 2018-05-11
00:00:00--00:20:00 0 3 1 6 4
00:20:00--00:40:00 0 1 2 4 0
00:40:00--01:00:00 0 0 2 1 4
01:00:00--01:20:00 0 0 2 4 0
01:20:00--01:40:00 0 8 1 2 4
01:40:00--02:00:00 0 9 2 1 2
02:00:00--02:20:00 0 3 2 2 4
... More windows...

The query I used for the heatmap is:
index="blabla" | chart sum(My_count_column) by TimeWindow Date | sort TimeWindow

When I hover on the heatmap, it shows as:
x: 02:00:00--02:20:00
y: May 08 00h ---> This has been converted from string '2018-05-05" to date with hour information. I want to keep it as '2018-05-08' please.
z: 3

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...