All Apps and Add-ons

Can Gantt chart be used for data where TimeStart and TimeEnd are in 1 event?

sssignals
Path Finder

Hi Splunk community,

My data looks something like this (see below), with TimeStart and TimeEnd in 1 event read from MySQL DB. Each event belongs to a software module with name "SWModule A", "SWModule B" etc.

As the duration of my SWModule is in term of seconds, the data does not show up nicely in Timeline app and I seriously lack Javascript skills in tuning it.

I thought of using Gantt chart. I realised Gantt groups multiple events of the same module name and use transaction to obtain the StartTime of the first event and EndTime of the last event in the same group to get the duration. Is it possible for Gantt chart to be used on my data as below? Pls advise as detailed as possible. Thanks a lot.

TimeStart="20171001 13:00:00",TimeEnd="20171001 13:05:00",SWModule="A"
TimeStart="20171001 13:10:00",TimeEnd="20171001 13:40:00",SWModule="B"
TimeStart="20171001 14:00:00",TimeEnd="20171001 14:05:00",SWModule="A"
TimeStart="20171001 15:30:00",TimeEnd="20171001 17:05:00",SWModule="A"
TimeStart="20171001 18:10:00",TimeEnd="20171001 19:40:00",SWModule="B"
TimeStart="20171001 20:10:00",TimeEnd="20171001 20:15:00",SWModule="C"

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

I haven't used the Gantt chart in a while, but I believe there's nothing really tricky about it, you just need a start time and a duration.

If you have your DBX input set up right, it's already doing the timestamp for the TimeStart as _time. If this is the case, you can skip ahead to "My Start Time is already good." You can also skip this section if whatever time _time IS seems good enough for your needs.

I need a start time first: First, let's use a strptime function to extract our start time from the field TimeStart.

 ... my search ... | eval TimeStart = strptime(TimeStart, "%Y%m%d %H:%M:%S")

After that, any event that has the field TimeStart in the appropriate format should have that field converted into a unix epoch (e.g. a big long number of seconds since 1970).

My start time is already good: If your start time is fine and you just need to add the end time...

...my search (with or without the TimeStart conversion) ...
| eval TimeEnd = strptime(TimeEnd, "%Y%m%d %H:%M:%S")

So now we have a start time (either _time or TimeStart) and an end time (TimeEnd), so let's build a duration. To the end of whatever search you have now...

... my search (with TimeStart and/or TimeEnd conversions)
| eval duration = TimeEnd - TimeStart

If you are using _time as your start time, you'll just want that to be

... my search (with TimeStart and/or TimeEnd conversions)
| eval duration = TimeEnd - _time

Now you SHOULD be able to run the Gantt chart just fine following it's instructions for which fields to use where. (Again, it's been a while and the docs online don't have instructions, but if I recall correctly it was reasonably easy to use ONCE you had a start and a duration ready to be used.)

Let us know how that works out! Happy Splunking,
Rich

View solution in original post

sssignals
Path Finder

alt text

Hi niketnilay,

Here is the Timeline screenshot I took off my testing laptop. Same at my Ops server.

Sorry for the delay in posting the screenshot. Previously the portal complains of not enough karma points to post screenshot?! Not sure why I could now.

I could not get the legend of "A-Description, B-Description, C-Description" out of the chart and had to resort to remove the legend totally.
In the end, I used Gantt chart.

Thanks for your help.

0 Karma

niketn
Legend

@sssignals, thanks for posting the screenshot. I can have a look to override the Legend placement through CSS and revert back. If you have Splunk Entitlement you can report this issue to Splunk Team for correction of Legends. Also you can add a BUG tag along with Timeline Custom Visualization to this question of yours.

Also for attaching image without karma points, you can also upload to any image sharing site like imgurl or tinypic and then attach the HTML link using the Image <img> button on Splunk Answers 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@sssignals, since I can only try this on my laptop I switched between lowest and highest resolutions and event some others at random. However, the Timeline visualization was always responsive, i.e. the Legends never overlapped on the chart. Since I could not recreate, I would not be able to fix as well.

Can you get the resolution of your Testing Laptop, Ops Server? What is the version of Timeline Custom Visualization being used? Also which browser are you seeing the issue?

Since you already have an answer that is working for you, there is no need for you to investigate this issue if time does not permit 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@sssignals, you should try Splunk TImeline Custom Visualization app, built on Splunk Custom Visualization API (https://splunkbase.splunk.com/app/3120/). The app comes with some examples illustrating the use of the Visualization. Following is a run-anywhere dashboard based query mocking the sample data provided in the question.

<dashboard>
  <label>Timeline Custom Visualization</label>
  <row>
    <panel>
      <viz type="timeline_app.timeline">
        <search>
          <query>|  makeresults
|  eval data="TimeStart=\"20171001 13:00:00\",TimeEnd=\"20171001 13:05:00\",SWModule=\"A\";TimeStart=\"20171001 13:10:00\",TimeEnd=\"20171001 13:40:00\",SWModule=\"B\";TimeStart=\"20171001 14:00:00\",TimeEnd=\"20171001 14:05:00\",SWModule=\"A\";TimeStart=\"20171001 15:30:00\",TimeEnd=\"20171001 17:05:00\",SWModule=\"A\";TimeStart=\"20171001 18:10:00\",TimeEnd=\"20171001 19:40:00\",SWModule=\"B\";TimeStart=\"20171001 20:10:00\",TimeEnd=\"20171001 20:15:00\",SWModule=\"C\""
|  makemv data delim=";"
|  mvexpand data
|  rename data as _raw
|  rex "TimeStart=\"(?<TimeStart>[^\"]+)\",TimeEnd=\"(?<TimeEnd>[^\"]+)\",SWModule=\"(?<SWModule>[^\"])\""
|  eval TimeEnd=strptime(TimeEnd,"%Y%m%d %H:%M:%S")
|  eval _time=strptime(TimeStart,"%Y%m%d %H:%M:%S")
|  eval duration_in_ms=(TimeEnd-_time)*1000
| eval SWModule_descption=SWModule." - Description"
|  table _time SWModule SWModule_descption duration_in_ms</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="timeline_app.timeline.axisTimeFormat">DAYS</option>
        <option name="timeline_app.timeline.colorMode">categorical</option>
        <option name="timeline_app.timeline.maxColor">#DA5C5C</option>
        <option name="timeline_app.timeline.minColor">#FFE8E8</option>
        <option name="timeline_app.timeline.numOfBins">6</option>
        <option name="timeline_app.timeline.tooltipTimeFormat">SECONDS</option>
        <option name="timeline_app.timeline.useColors">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>
</dashboard>

PS: Just to use Categorical Colors a new field SWModule_description has been introduced. You would not need it if you don't need Categorical colors. It would however be good if you can get this either from data or a lookup file. Please try out and confirm.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sssignals
Path Finder

Thanks niketnilay. Your query statement works and the timeline app renders nicely on my home laptop. I got to try your entire dashboard on my Ops PC tomorrow.

The reason why I ditched the timeline app was on my Ops PC, the legend overlaps with the graph which is a no-no. And as the duration of each of my events are in terms of seconds, somehow visually the timeline app turned out not pleasing to the eye. Ended up i skirted the problem by making the legend disappear which is not resolving the problem due to my limited skills. And I further escaped the problem via Gantt chart 😃

0 Karma

niketn
Legend

@sssignals, in case you try out Timeline Custom Visualization and it does not work as expected do post the screenshot with issue. The reason for me to favor Timeline Custom Visualization is that it is built by Splunk and based on Custom Visualization API which a developer with JavaScript, CSS kind of knowledge can code.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Richfez
SplunkTrust
SplunkTrust

I haven't used the Gantt chart in a while, but I believe there's nothing really tricky about it, you just need a start time and a duration.

If you have your DBX input set up right, it's already doing the timestamp for the TimeStart as _time. If this is the case, you can skip ahead to "My Start Time is already good." You can also skip this section if whatever time _time IS seems good enough for your needs.

I need a start time first: First, let's use a strptime function to extract our start time from the field TimeStart.

 ... my search ... | eval TimeStart = strptime(TimeStart, "%Y%m%d %H:%M:%S")

After that, any event that has the field TimeStart in the appropriate format should have that field converted into a unix epoch (e.g. a big long number of seconds since 1970).

My start time is already good: If your start time is fine and you just need to add the end time...

...my search (with or without the TimeStart conversion) ...
| eval TimeEnd = strptime(TimeEnd, "%Y%m%d %H:%M:%S")

So now we have a start time (either _time or TimeStart) and an end time (TimeEnd), so let's build a duration. To the end of whatever search you have now...

... my search (with TimeStart and/or TimeEnd conversions)
| eval duration = TimeEnd - TimeStart

If you are using _time as your start time, you'll just want that to be

... my search (with TimeStart and/or TimeEnd conversions)
| eval duration = TimeEnd - _time

Now you SHOULD be able to run the Gantt chart just fine following it's instructions for which fields to use where. (Again, it's been a while and the docs online don't have instructions, but if I recall correctly it was reasonably easy to use ONCE you had a start and a duration ready to be used.)

Let us know how that works out! Happy Splunking,
Rich

sssignals
Path Finder

Thanks rich7177!! It works!! 🙂

For the sake of those like me, I use a csv as I could not wait til I get to office to access MySQL.
My search: | inputlookup gantt.csv | eval TimeStart = strptime(TimeStart, \"%Y%m%d %H:%M:%S\") | eval TimeEnd = strptime(TimeEnd, \"%Y%m%d %H:%M:%S\") | eval duration = TimeEnd - TimeStart | search duration>0

Using back the same things in data options except a few fields:
"startField": "TimeStart",
"durationField": "duration",
"categoryLabel": "SWModule",
"categoryField": "SWModule",

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...