All Apps and Add-ons

Timeline - Custom Visualization: How to properly graph time as duration?

Toshbar
Explorer

I'm trying to create a timeline visualization based off of the DATETIME and JOBNAME these two logs:

 DATETIME:   2017-07-11 08:04:06.99 -0700   
 JOBNAME:    CIBI825D   
 MSGTXT:     IEF404I CIBI825D - ENDED - TIME=08.04.06   


 DATETIME:   2017-07-11 06:53:40.50 -0700   
 JOBNAME:    CIBI825D   
 MSGTXT:     IEF403I CIBI825D - STARTED - TIME=06.53.40 

I can currently show start/end times as points but I'm unable to graph them as a range of time using the duration_field as noted in the documentation. The below documentation link shows that I'm trying to achieve: Row RFC, blue block

alt text

I'm able to create the timeline visualization with the simple query below to get the start and end point graphed.

index = x MSGTXT = "\*started - time\*" OR "\*ended - time\*"
| regex JOBNAME = "CIBI825D"

| table DATETIME JOBNAME

alt text

The splunk documentation for timeline visualization shows that I need the starttime and duration so here is the query I came up with to get the duration.

index = x MSGTXT = "\*started - time\*" OR "\*ended - time\*"
| regex JOBNAME = "CIBI825D"
| rex field=DATETIME "(?<time>[^\r\n]+)"
| eval time=strptime(time, "%Y-%m-%d %H:%M:%S")
| stats range(time) AS duration BY JOBNAME

| append[search 
index = x  MSGTXT = "*started - time*"
| regex JOBNAME = "CIBI825D"
| rex field=DATETIME "(?<STARTTIME>[^\r\n]+)"
| eval STIME=strptime(STARTTIME, "%Y-%m-%d %H:%M:%S")
    ]

|table STARTTIME JOBNAME duration

Here is the picture of what it looks like. I'm not sure why it isn't working. I tried to convert seconds to milliseconds like the documentation says but that doesn't work as well.

alt text

Also, as a followup question, after this I would like do combine multiple JOBNAMES to show multiple ranges on a single row. Is this possible? If yes, how would I do that?

0 Karma
1 Solution

niketn
Legend

@Toshbar, if you have ingested your data with valid timestamp recognition, ideally you should have _time field extracted from pattern DATETIME:

| makeresults
| eval _raw = "DATETIME:     2017-07-11 08:04:06.99 -0700    JOBNAME:     CIBI825D    MSGTXT:     IEF404I CIBI825D - ENDED - TIME=08.04.06"
| eval _time = strptime("2017-07-11 08:04:06.99 -0700","%Y-%m-%d %H:%M:%S")
| append [| makeresults 
          | eval _raw = "DATETIME:     2017-07-11 06:53:40.50 -0700    JOBNAME:     CIBI825D    MSGTXT:     IEF403I CIBI825D - STARTED - TIME=06.53.40 "
          | eval _time = strptime("2017-07-11 06:53:40.50 -0700","%Y-%m-%d %H:%M:%S")]

The above is to generate sample data. Following is to generate required table for plotting duration by Job Name on Timeline custom visualization.

| rex field=_raw "JOBNAME:\s+(?<JOBNAME>\w+)\s+"
| stats min(_time) as _time max(_time) as ENDTIME by JOBNAME
| eval duration=ENDTIME-_time
| table _time JOBNAME duration
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@Toshbar, if you have ingested your data with valid timestamp recognition, ideally you should have _time field extracted from pattern DATETIME:

| makeresults
| eval _raw = "DATETIME:     2017-07-11 08:04:06.99 -0700    JOBNAME:     CIBI825D    MSGTXT:     IEF404I CIBI825D - ENDED - TIME=08.04.06"
| eval _time = strptime("2017-07-11 08:04:06.99 -0700","%Y-%m-%d %H:%M:%S")
| append [| makeresults 
          | eval _raw = "DATETIME:     2017-07-11 06:53:40.50 -0700    JOBNAME:     CIBI825D    MSGTXT:     IEF403I CIBI825D - STARTED - TIME=06.53.40 "
          | eval _time = strptime("2017-07-11 06:53:40.50 -0700","%Y-%m-%d %H:%M:%S")]

The above is to generate sample data. Following is to generate required table for plotting duration by Job Name on Timeline custom visualization.

| rex field=_raw "JOBNAME:\s+(?<JOBNAME>\w+)\s+"
| stats min(_time) as _time max(_time) as ENDTIME by JOBNAME
| eval duration=ENDTIME-_time
| table _time JOBNAME duration
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Toshbar
Explorer

I forgot to reply. This worked perfectly thank you.

0 Karma

niketn
Legend

@Toshbar, glad it worked. Let me convert to answer so that you can accept and mark as answered.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...