topic Re: column chart of the range between 2 times in Splunk Enterprise

column chart of the range between 2 times

spisiakmi — Tue, 05 Dec 2023 10:36:29 GMT

Hi, I have

StartTime,EndTime
"2023-12-05 05:30:00.0000000","2023-12-05 08:00:00.0000000"
"2023-12-05 08:00:00.0000000","2023-12-05 09:30:00.0000000"
"2023-12-05 10:28:00.0000000","2023-12-05 13:30:00.0000000"

I need to visualize a column chart, with 3 columns (in this case) with height 1 (y axis). The width of the first column is between "2023-12-05 05:30:00.0000000","2023-12-05 08:00:00.0000000", second one between "2023-12-05 08:00:00.0000000","2023-12-05 09:30:00.0000000", the third between "2023-12-05 10:28:00.0000000","2023-12-05 13:30:00.0000000". The x axis should be the time. Attached example. Any idea, please?

Re: column chart of the range between 2 times

ITWhisperer — Tue, 05 Dec 2023 12:04:08 GMT

Try something like this

| makeresults format=csv data="StartTime,EndTime 2023-12-05 05:30:00.0000000,2023-12-05 08:00:00.0000000 2023-12-05 08:00:00.0000000,2023-12-05 09:30:00.0000000 2023-12-05 10:28:00.0000000,2023-12-05 13:30:00.0000000" | eval row=mvrange(0,4) | mvexpand row | eval _time=case(row=0,strptime(StartTime,"%F %T.%6N"),row=1,strptime(StartTime,"%F %T.%6N"),row=2,strptime(EndTime,"%F %T.%6N"),row=3,strptime(EndTime,"%F %T.%6N")) | eval value=case(row=0,0,row=1,1,row=2,1,row=3,0) | table _time value

Then use an area chart viz

Re: column chart of the range between 2 times

spisiakmi — Tue, 05 Dec 2023 15:13:19 GMT

Hi ITWhisperer, thx for sharing it. Unfortunately, if I run your code I receive no results.

Re: column chart of the range between 2 times

ITWhisperer — Tue, 05 Dec 2023 15:21:28 GMT

As you can see from my runanywhere example, it does work. How have you actually implemented my suggestion? What results do you get? What do your actual events look like?

Re: column chart of the range between 2 times

spisiakmi — Tue, 05 Dec 2023 17:20:55 GMT

From your code I recived this:
"_time",value
,0
,1
,1
,0

Re: column chart of the range between 2 times

ITWhisperer — Tue, 05 Dec 2023 17:33:19 GMT

Looks like you haven't evaluated _time

| eval _time=case(row=0,strptime(StartTime,"%F %T.%6N"),row=1,strptime(StartTime,"%F %T.%6N"),row=2,strptime(EndTime,"%F %T.%6N"),row=3,strptime(EndTime,"%F %T.%6N"))

Re: column chart of the range between 2 times

spisiakmi — Tue, 05 Dec 2023 17:54:12 GMT

But, if I use this code on the content, which I mentioned in the main describtion, I receive these results (see attch 1.jpg). And this is quiet good for me, except the triangel step. Any idea, how to fix it?
| eval row=mvrange(0,4)
| mvexpand row
| eval _time=case(row=0,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"))
| eval value=case(row=0,1,row=1,1,row=2,1,row=3,0)
| table _time value

Re: column chart of the range between 2 times

spisiakmi — Tue, 05 Dec 2023 17:53:02 GMT

Hi ITWhisperer, I fixed it. Thank you very very much for your help,

with this, it is working properly (look attached 2.jpg):

| sort StartTime
| eval row=mvrange(0,4)
| mvexpand row
| eval _time=case(row=0,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"))
| eval value=case(row=0,1,row=1,1,row=2,1,row=2,0) ´here is the difference
| table _time value

Re: column chart of the range between 2 times

spisiakmi — Tue, 05 Dec 2023 17:56:13 GMT

Attached result 2.jpg

Re: column chart of the range between 2 times

spisiakmi — Wed, 06 Dec 2023 07:25:22 GMT

I have 1 question. The solution shows the time range in restricted _time. It is possible to expand it into/show in selected time range, which is defined in the time range picker? To the range addinfo.info_max_time, addinfo.info_min_time?

Re: column chart of the range between 2 times

ITWhisperer — Wed, 06 Dec 2023 08:42:48 GMT

I am not sure how your solutions works since you are not setting _time when row=3, and it is not clear what "restricted" _time is, nor what your expected result should look like.

Re: column chart of the range between 2 times

spisiakmi — Wed, 06 Dec 2023 13:05:14 GMT

1. there is a time range picker object on the dashboard. If I select any range, e.G. the whole day 05.12.2023, this time range I would like to have on x-axis in area chart.
2. in this case,

| eval _time=case(row=0,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S")) | eval value=case(row=0,0,row=1,1,row=2,1,row=3,0)

the time range of the x-axis in area chart is from the first StartTime (05:30) ... last EndTime (13:30).

Re: column chart of the range between 2 times

spisiakmi — Wed, 06 Dec 2023 09:24:51 GMT

This is, what I want to achieve. 3.jpg. Time range from the time range picker. In this case the day 05.12.2023.

Re: column chart of the range between 2 times

spisiakmi — Wed, 06 Dec 2023 13:05:43 GMT

And here is the solution

| eval row=mvrange(0,6) | mvexpand row | addinfo | eval _time=case(row=0,info_min_time,row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=4,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=5,info_max_time) | eval value=case(row=0,0,row=1,0,row=2,1,row=3,1,row=4,0,row=5,0) | table _time, value