topic Re: How do I create a Gantt chart to show work hours by person in Splunk Search

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

bsteph — Mon, 20 Aug 2012 20:41:22 GMT

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.

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

DTERM — Mon, 20 Aug 2012 20:43:35 GMT

sample input would be nice....

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

kristian_kolb — Mon, 20 Aug 2012 21:08:11 GMT

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

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

bsteph — Mon, 20 Aug 2012 21:49:39 GMT

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

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

kristian_kolb — Mon, 20 Aug 2012 21:57:14 GMT

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

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

bsteph — Mon, 20 Aug 2012 22:01:10 GMT

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

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

tfletcher_splun — Tue, 21 Aug 2012 20:39:58 GMT

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

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

kristian_kolb — Tue, 21 Aug 2012 22:44:26 GMT

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

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

kristian_kolb — Tue, 21 Aug 2012 22:47:50 GMT

Hooray for your effort.