Counting how many people are in office at exact time

[kaukenas]

Hello,

I am new to Splunk, just finished beginner tutorial and my boss gave me task to count, how many employees are in the office for a specific time period.
I have CSV file containing employee ID, time and date when he went IN or OUT.
For example, I need to know how many people were in office between 12AM to 1PM, however if employee came at 8AM, he should be listed as IN at that time.

Thank you for your help,

Vaidotas

[somesoni2]

Well, sample data for an employee for it's in time and out time will help here.

Meanwhile give this a try (assuming there is a column "Type" with value either IN or OUT and column "Timestamp" which contains the time in format "%m/%d/%Y %H:%M")

| inputlookup YourData.csv | table EmployeeID Type Timestamp | eval _time=strptime(Timestamp,"%m/%d/%Y %H:%M") | chart values(_time) over EmployeeID by Type | eval time=mvrange(IN,OUT,300) | mvexpand time | eval _time=time | timechart span=1h dc(EmployeeID)

[sundareshr]

What you will need to do, if first convert your time fields to epoch times so you can do calculations. You can use strptime for this conversion. Your final search will look something like this

| inputlookup employesslist.csv | eval in=strptime(IN, "%c") | eval out=strptime(out, "%c") | eval midnight=relative_time(now(), "@d") | eval afternoon=relative_time(now(), "@d+13h") | where (in>midnight AND in<afternoon) AND out>afternoon | timechart span=1h dc(employee)