Splunk Search

How do I create a Gantt chart to show work hours by person

bsteph
Explorer

I want to create a gantt chart to show by day who was working when. My record layout is start timestamp (12/08/20.05:45:06) end timestamp (12/08/20.05:45:43) person(bob). The goal is to visualize everyone for example 9-11 or 10-12, etc.

Tags (1)
0 Karma

kristian_kolb
Ultra Champion

Sadly I've spent too much time thinking, guessing and testing 🙂

However, I also have a fairly ugly solution, but it sort of works, at least for my (actually your) test data, with just one addition. More on that later. Here's a line by line walkthrough;

...| rex "\s(?<QQQ>[\S]+)" max_match=2 

The idea here is to extract the timestamps as a multivalued field (QQQ). Since I didn't have the energy to make Splunk understand your timestamps, I added 2012-08-21T21:22:23 style timestamps first in each line. If I didn't Splunk ate the whole file as one event. This means that you might want to change the rex to match a whitespace after the start/end timestamps like rex "(?<QQQ>[\S]+)\s" max_match=2

| rex "\s(?<UserID>[\w]+)$" 

Extract the userId at the end of the line.

| stats list(QQQ) AS Q by UserID 
| mvexpand Q  

List the timestamps by userID and then create 'new' events based on the multivalued fields, so that each new event one userID and one timestamp (regardless of wheter it's the start or end time)

| eval _time = strptime(Q,"%Y/%m/%d.%H:%M:%S")

For each of those events, set the start- or end-time as the event's own _time

| timechart span=1h first(_time) by UserID 

Create a chart.

In order to get something even remotely like what you want, you should now take the full query and use it in the Advanced Charting wizard (Dashboards & Views -> Advanced Charting).

| rex "\s(?<QQQ>[\S]+)" max_match=2 | rex "\s(?<UserID>[\w]+)$" | stats list(QQQ) AS Q by UserID | mvexpand Q  | eval _time = strptime(Q,"%Y/%m/%d.%H:%M:%S") | timechart span=1h first(_time) by UserID  
  • Paste the query, and in the menus;
  • select Chart Type "line"
  • select Multi-Series mode "Split"
  • select Missing Values "Connected"
  • Put the Legend on either "Right" or "Left"

This is still not very beautiful as the Y-axis must show the values of _time instead of the UserID, at least that's as far as I got. Also, I have a strong suspicion that it would simply not work for multiple sets per person per day. Actually I know it wouldn't, so from now on all lunches and coffee breaks are cancelled.

Well, anyway I learned a lot of stuff that don't work... 🙂

Hope this helps a little bit,

Kristian

0 Karma

tfletcher_splun
Splunk Employee
Splunk Employee

Say you've extracted these timestamps and parsed the timestamp so you can get epoch time values. Let's also assume you only want to do this for 1 day windows, i.e. you are setting the timerange from midnight to midnight only, and no one works past midnight (if they do we are going to just chart it as if they did stop at midnight).

We also need users to evaluate to numbers so they show up in a chart. You would do this with a uid lookup, that is a csv of username to uid something like:
bob,1
justin,2
susan,3

In this example the fields are clock_in, clock_out, user. What we'll do is expand the dataset so that we can chart on those timestamps.

... | eval clock=clock_in + ":" + clock_out | lookup UserToUid user OUTPUT uid | makemv clock delim=":" | mvexpand clock | chart first(uid) as UserID by clock 

This is a hideous solution, what you really need is a custom module to get a custom visualization, but if you don't feel comfortable doing that this will work.

Run this search to get an example of what this would look like:

| stats count | eval clock="120"+":"+"130" | eval user="5" | makemv clock delim=":" | mvexpand clock | chart first(user) by clock
0 Karma

kristian_kolb
Ultra Champion

Hooray for your effort.

0 Karma

bsteph
Explorer

yeah, bob didn't really work out....

0 Karma

kristian_kolb
Ultra Champion

I really like that user4, solid trooper, really goes the extra mile, not like bob at all in fact.

0 Karma

bsteph
Explorer

Here is some sample data

12/08/20.09:00:28 12/08/20.13:05:28 user1
12/08/20.10:05:28 12/08/20.15:05:29 user2
12/08/20.08:02:34 12/08/20.14:05:51 user3
12/08/20.09:00:27 12/08/20.20:06:05 user4
12/08/20.09:06:08 12/08/20.19:06:24 user5

0 Karma

kristian_kolb
Ultra Champion

bob does not seem to be putting in a lot of work. 37 seconds 🙂

DTERM
Contributor

sample input would be nice....

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 ...