How create line chart using Time (Hour) in Y-axis and Date (Days) in X-axis

[julio19]

Hi Guys,

Well, I have this structured log:
MEMBER, JOBNAME, JOBID, DATE_START, HOUR_START, DATE_END, HOUR_END, DURATION, GROUP, CPUTIME
PLCZBA20,PLCZBA20,21010,07/26/17,17:00,07/26/17,17:00,00:11,UAT,00:00.3
PLCADA20,PLCADA20,21008,07/26/17,17:00,07/26/17,17:00,00:11,UAT,00:00.4

And I'm trying to create next chart (for example):

I comment on them, the field DURATION is in the following format %M:%S and fields HOUR_START & HOUR_END in %H:M%,
now the field _time take correctly start date and time.

I was trying with this query, but it didn't convert to display time in the Y-axis and I'm not sure if this is the right way.

MYSEARCH
| bucket _time span=1d
| rex field=DURATION "(?<Mins>.{2}+):(?<Sec>.{2}+)" 
| fillnull value=0
| eval secs=Mins*60+Sec
| eval duration = secs * 1000 
| stats count by _time, duration, "JOBNAME"
| table _time "JOBNAME"  duration
| convert timeformat="%H:%M" ctime(duration) AS duration
| chart max(duration) over _time by JOBNAME

Can someone help me please!!

regards..

[DalJeanis · Accepted solution]

Interesting use case.

Okay, so you have dates across the bottom, and you have jobs that always run in sequence, and you need to know when they ran, so you're making the equivalent of a stacked bar chart and visually conforming it to a line chart...

Okay, you can GET the graph, that's not a problem. Strip your problem back to understanding what will put the points on the graph...

Your target events basically have this format...

_time   (the display date), 
RunTime (the number of seconds after midnight they ran)
JobStep (the title for that series)

...but really, your production window looks like 10PM to 11AM, so that format needs to be modified to...

_time   (the display date... the date of the prior night)
RunTime (the number of seconds after window-start that they ran)
JobStep (the title for that series)

When you put it into that format, you can get your desired line graphs. The main problem is the lack of being able to format the y axis, especially when you're rolling over from p to a.

My temptation on this one would be to bump the date up to the next day and calculate the decimal hours, so that the left axis starts at -2 for 10pm and goes up to +12 for noon.

[kingwaras]

Hi @julio19,

can you paste the complete code of your query graph?
I have the same problem.

Thanks in advance.

[tmarlette]

I'm a little fuzzy on what you're trying to do, however you may what to use the |timechart command

| timechart span=1h will give you hourly buckets of things

so maybe

 <my_root_search> 
 | rex field=DURATION "(?<Mins>.{2}+):(?<Sec>.{2}+)" 
 | fillnull value=0
 | eval secs=Mins*60+Sec
 | eval duration = secs * 1000 
 | timechart span=1h max(duration) AS duration by JOBNAME

[julio19]

First thanks for yout time, is correctly query but, i can show time in Y-axis in one format similar to the image %H:%M:%S

This is possible? or exist any app that can help me?