Plotting end times on a chart

[kenbaugher]

We have data similar to the below and are trying to chart it with a line or bar graph similar to the chart shown that was created in excel.   Been able to do different things to calculate a duration since midnight on the date to the end time to give a consistent starting point for each, but splunk does not seem to like to chart the duration or a time stamp as they are strings.   We can chart it as a value like a unix format date but that isn't really human readable.

Date	System	End Time
20240209	SYSTEM1	2/9/24 10:39 PM
20240209	SYSTEM2	2/9/24 10:34 PM
20240209	SYSTEM3	2/9/24 11:08 PM
20240212	SYSTEM1	2/12/24 10:37 PM
20240212	SYSTEM2	2/12/24 10:19 PM
20240212	SYSTEM3	2/12/24 11:10 PM
20240213	SYSTEM1	2/13/24 11:19 PM
20240213	SYSTEM2	2/13/24 10:17 PM
20240213	SYSTEM3	2/13/24 11:00 PM
20240214	SYSTEM1	2/14/24 10:35 PM
20240214	SYSTEM2	2/14/24 10:23 PM
20240214	SYSTEM3	2/14/24 11:08 PM
20240215	SYSTEM1	2/15/24 10:36 PM
20240215	SYSTEM2	2/15/24 10:17 PM
20240215	SYSTEM3	2/15/24 11:03 PM

[bowesmana]

@ITWhisperer is right, but I've used a time as a fraction to show on the Y axis, something like

| rex field=End "\d+/\d+/\d+ (?<h>\d+):(?<m>\d+)\s+"
| eval t=round(h+(m/100),2)
| chart max(t) over Date by System

It's a bit of a kludge, as it will represent 10:50 as 10.50 so will be half way between 10 and 11, but you could represent it as a true fraction, i.e. /60 not 100, but then the numbers are not so useful, i.e. when 10:50 shows as 10.83

[PickleRick]

1. Neat idea. You could adjust that to "scale" from x.00 to x.59.

2. With locale using comma for decimal point it will look worse (but I don't remember if Splunk can do comma).

[ITWhisperer]

This sort of chart is not possible because the y-axis has to be a number and unfortunately cannot be formatted as a time string.

[PickleRick]

See the fieldformat command. It lets you tell Splunk to process the data as it was but display in a different (usually more human-readable) form.

[kenbaugher]

I've tried the below with the fieldformat before and after the chart command, same results, the duration_U field still shows as a unix date, to the chart is technically correct, but the y axis information is not human readable.   Just shows values ranging from 70,000 to 90,000.

index= source= | strcat date "000000" BDATE | eval duration_U=strptime(end_time,"%Y-%m-%d %H:%M:%S.%N") - strptime(BDATE,"%Y%m%d%H%M%S") |fieldformat duration_U=tostring(duration_U,"duration")| chart latest(duration_U) over system by date

[PickleRick]

I misunderstood your initial question. Fieldformat can be used I think to handle X-series values. Y-series must be numeric. (You probably could try to add your own JS to a dashboard (not report) to dynamically convert the data or try to write your own visualization but that's a completely different story and - frankly - quite an overkill)

[gcusello]

Hi @kenbaugher,

did you tried with the chart command (https://docs.splunk.com/Documentation/Splunk/9.2.0/SearchReference/Chart)?

please adapt this example to your use case:

index=your_index
| chart values("End Time")AS End_Time OVER Date BY System

One additional hint: don't use spaces in the field names.

Ciao.

Giuseppe