Splunk Search

Can we create a vacation tracker in Splunk?

rpradeep
Path Finder

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.

Tags (1)
1 Solution

askkawalkar
Path Finder

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.

View solution in original post

niketn
Legend

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

alt text

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

rpradeep
Path Finder

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.

0 Karma

niketn
Legend

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

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

rpradeep
Path Finder

Perfect now.
Thanks a lot @niketnilay 🙂

askkawalkar
Path Finder

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
Ultra Champion

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

If my comment helps, please give it a thumbs up!
0 Karma

rpradeep
Path Finder

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.

0 Karma

rpradeep
Path Finder

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.

0 Karma

askkawalkar
Path Finder

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

0 Karma

rpradeep
Path Finder

@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!!

0 Karma

askkawalkar
Path Finder

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

0 Karma

rpradeep
Path Finder

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

0 Karma

niketn
Legend

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

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

nickhills
Ultra Champion

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

If my comment helps, please give it a thumbs up!
0 Karma

rpradeep
Path Finder

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.

0 Karma

nickhills
Ultra Champion

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/

If my comment helps, please give it a thumbs up!

ramgnisiv
Path Finder

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.

0 Karma

rpradeep
Path Finder

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.

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