Can we create a vacation tracker in Splunk?

[rpradeep]

We use Splunk for many of our project dashboards & want to see if I can use the same setup to host a Vacation Tracker of my team.

[niketn]

@rpradeep on a different note try the Splunk Timeline Custom Visualization

Following is a run anywhere query which you can try.

| makeresults
| eval data="abc 2019-01-15 2019-01-20;cde 2019-02-04 2019-02-14;efg 2019-03-05 2019-03-10;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval "employee"=mvindex(data,0), start_date=mvindex(data,1), end_date=mvindex(data,2)
| eval _time = strptime(start_date, "%Y-%m-%d") 
| eval end_time = strptime(end_date, "%Y-%m-%d") 
| eval duration = (end_time - _time) * 1000
| stats count by _time, duration, employee 
| table _time employee duration

[rpradeep]

Thanks @niketnilay , this looks amazing too.
Although, I am not able to understand the logic behind 1000(random()%5). Without this I am not getting exact results.

[niketn]

@rpradeep, I have corrected the query. I had taken it from run anywhere example which comes with the app. Please refer to the updated query above.

[rpradeep]

Perfect now.
Thanks a lot @niketnilay 🙂

[askkawalkar · Accepted solution]

Hi @rpradeep,

I have tried with some different data, It is able to show number of employees on leave for a specific day. I don't know is it going to help you or not.

Step 1: Data Generation : I have used below search query to generate Dummy Data.
Changes:
I have used 2 columns - Employee Name(Emp_Name) and Date on which he will be on leave (OnVacation).

| makeresults 
 | eval data="abc 2019-01-15;abc 2019-01-16;abc 2019-01-17;abc 2019-01-18;abc 2019-01-19;abc 2019-01-20;abc 2019-01-21;prq 2019-01-12;prq 2019-01-13;prq 2019-01-14;xyz 2019-01-02;mnp 2019-01-17;" 
 | makemv data delim=";" 
 | mvexpand data 
 | makemv data delim=" " 
 | eval Emp_name=mvindex(data,0),OnVacation=mvindex(data,1)
 | table Emp_name,OnVacation

Step 2:
1. Convert the OnVacation date to _time (event Time)
2. Draw timechart on the span of 1 day over a distinct count of Emp_name.

 | eval _time=strptime(OnVacation,"%Y-%m-%d")
 | timechart span=1d dc(Emp_name) as "Emp on Leave"

PS: To use calendar visualization, you need to use timechart commnad over a span. Please refer below documentation for timechart.

[nickhills]

I started to work on something like this too, but I think you nailed it!

If you want to see who (not just how many) is on leave, replace the last line in @askkawalkar's step 2 with | timechart span=1d count by Emp_name

[rpradeep]

Thanks a lot askhkawalkar & nickhillscpl. I am now able to see the calendar with names on.
Now, the next challenge for me is to find a way to mark all the dates from Start_date to End_date as vacation. Please share inputs if you already have any.

[rpradeep]

Hi Guys,
No luck yet in marking all the dates from Start_date to End_date as vacation in the calendar.
I know it got something to do with span function in timechart but not able to decode.
Please help.

[askkawalkar]

@rpradeep can you please share screen shot with error, it will be easy to understand.

[rpradeep]

@askkawalkar , there is no error as such.
When I give input as "Pradeep,01-Apr-2019,05-Apr-2019,Vacation", my requirement is all the dates from 1st April to 5th April be marked in the calendar. Currently, I am able to mark only the start date(1st April) in my calendar using below query.
eval _time=strptime(Start_date,"%d-%b-%Y")
| timechart span=1d count by Emp_Name

Right now I am unable to attach any file/screenshot, it says I need more karma points!!

[askkawalkar]

@rpradeep , To use above solution, if you are trying to show vacation from 01-Apr-2019 to 05-Apr-2019, you need to have 5 rows in your data as below -
Pradeep,01-Apr-2019
Pradeep,02-Apr-2019
Pradeep,03-Apr-2019
Pradeep,04-Apr-2019
Pradeep,05-Apr-2019

this will show you required details over a dashboard.
I am not sure how to help you out in changing the data format.

[rpradeep]

Thanks @askkawalkar .
Yes, I thought of this option & already working parallelly on it. 🙂
But I just wanted to get rid of this additional step.

[niketn]

@rpradeep after ensuring that you mask out any sensitive information from the image, you can upload the same to any image sharing site such as imgur and post a link to image using Image button or shortcut Ctrl+G.

[nickhills]

Where does your vacation information come from - do you have the data already in Splunk?

[rpradeep]

No, I do not already have data in Splunk. Sorry for not giving this information already.
Currently I am planning to feed the data from a tab-separated-file, with example format below:

Emp_name Vacation_Start_date Vacation_End_date
abc 2019-01-15 2019-01-29

I can either get this data updated by manual entries to the file or via some interactive UI.

[nickhills]

Should be relativly easy.
Take a look at the following two visualizations to see if either fits your requirements:
https://splunkbase.splunk.com/app/3372/
https://splunkbase.splunk.com/app/3162/

[ramgnisiv]

I installed Calendar Heat Map app on my local, it works like a charm. Installing it on the central server soon, this will surely help visualising time based events, especially for management who are often looking for something visually resembling the old world.

[rpradeep]

Thanks Nickhill.
I have downloaded the app & installed it. I am still trying to figure out how to feed data to it.
Will be great if you can guide me to any such tutorial.