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!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

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