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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...