Monthly Occupancy Report with Daily Events

[spodda01da]

Hello All,

I am trying to generate a Monthly Occupancy Report of users with Daily events.

The issue is the Daily events consists of Multiple entries of a user, so I have to use "dedup user" command to get single entries every day

As running dedup command on Monthly report will give single entry of a user in a month, I am extracting reports per day and then consolidate it to get a monthly report which is time consuming.

Looking for suggestions/commands which will help to run a monthly report with Single event of a user (per day).

| lookup AD-lookup sAMAccountName as user output displayName,givenName,sn,mail,telephoneNumber,mobile,manager,department
| eval Date=strftime(_time, "%d-%m-%Y"), Time=strftime(_time, "%H:%M") | table Date,Time, user, displayName, title, department, host, Address, Subnet, Site, mail, mobile
| dedup user
| sort 0 -Date,-Time |

Thanks,

[DalJeanis · Accepted solution]

Really, you don't have to go to all that trouble.

dedup is a little more flexible than you are thinking.

  your search that gets one or more records for each user per day for the whole month
 | table _time User
 | bin _time span=1d as Day
 | dedup User Day 

The above gets you one record per User per Day.

[spodda01da]

Thank you, it worked