How to work out Total Staff numbers Entering Buildiing Each Day and Weekly Total

[DDewarSplunk]

Afternoon Splunk Community

Can you help me solve a problem?

I have been asked to supply a report showing numbers of staff entering our 2 office buildings (Building A, Building B)
Mon-Fri broken down with totals for each day and overall totals per building.

I am looking at using the addtotals command but finding it tricky as I don't have numeric fields just names and door names
I have had to | dedup FirstName | dedup Surname for building A and now have a credible number over a 24hr period.

How do I convert this to a table, with totals at bottom for each day and total at end for whole week ?

Many thanks for anyone who can solve my riddle.

D

[niketn · Accepted solution]

[UPDATE]
Added time formatted as weekday number and weekday name abbreviation.
Try the following search.

<YourBaseSearch>
|  bin _time span=1d
|  stats count as TotalLogins by _time FirstName LastName Building
|  eval FullName=FirstName." ".LastName, Time=strftime(_time,"%w  %a")
|  chart dc(FullName) as LoginCount by Time Building
|  addcoltotals labelfield="Time" label="Total"

If you want to reverse the table rows and columns

<YourBaseSearch>
|  bin _time span=1d
|  stats count as TotalLogins by _time FirstName LastName Building
|  eval FullName=FirstName." ".LastName
|  eval Time=strftime(_time,"%w  %a")
|  chart dc(FullName) as LoginCount by Building Time
|  addcoltotals labelfield="Building" label="Total"

Following will give the coefficient table i.e. Total of Rows and Total of columns in matrix like format.

 <YourBaseSearch>
 |  bin _time span=1d
 |  stats count as TotalLogins by _time FirstName LastName Building
 |  eval FullName=FirstName." ".LastName, Time=strftime(_time,"%w  %a")
 | ctable Time Building

Or if you want inverse try | ctable Building Time as your final pipe (Try this first as this will be closest to what you need😞

 <YourBaseSearch>
 |  bin _time span=1d
 |  stats count as TotalLogins by _time FirstName LastName Building
 |  eval FullName=FirstName." ".LastName, Time=strftime(_time,"%w  %a")
 | ctable Building Time 

---

Assuming you have three fields in your events FirstName, LastName and Building(if not replace the same accordingly in the query below), try the following search:

<YourBaseSearch>
|  stats count as TotalLogins by FirstName LastName Building
|  eval FullName=FirstName." ".LastName
|  chart dc(FullName) as LoginCount sum(TotalLogins) as TotalLogins by  FullName Building
|  fillnull value=0
|  addcoltotals labelfield="FullName"

Breakdown of the query:

• First stats command will be similar in effect to dedup however it will keep a count of every time a person enters a building as TotalLogins.
• Since your event does no have single field containing FullName, you should perform eval (after stats command) to combine FirstName and LastName.
• Add another chart command, dc(FullName) as LoginCount will give how many people entered the building. The sum(TotalLogins) as TotalLogins give every time a person entered the building. (As per your question this is not required, but this will account for scenario when a person went out and came back in.
• If you are not using sum(TotalLogins) you will not require fillnull as it puts value as 0 where the same is null.
• addcoltotals command in the final pipe sums all numeric fields and displays the Total value under FullName field.

[DDewarSplunk]

Thanks I was able to change the values of your search and got it running. What I'm being asked for though is a breakdown by day of week totals not each member of staff.

Week Day----Mon---Tues----Wed----Thurs----Fri----Sat----Sun
OfficeA--------200
OfficeB---------33
Totals-----------233-----333----555------666----765---434-----233

And a Grand total for each office for whole week.
Thanks for your time and help Nik

[DDewarSplunk]

Many thanks for your help last week it was very much appreciated.

David

[niketn]

Anytime. Do let us know if you need help with anything else 🙂

[thiagodede]

DDewarSplunk, please provide more information about your actual search and how the data is being showed today with you actual search

[cmerriman]

first, i would |dedup FIrstName Surname in the same command, not separately, since you could have multiple people with either the same first or last name. You may even run into people having the same full name, but that's a discrepancy you'll have to acknowledge.

do you have _time available? or what type of reference do you have in your data to the day?

after the dedup, try something like |stats count by day or |timechart span=1d count and you can maybe add |addtotals or |eventstats sum(count) as total for summaries

[DDewarSplunk]

Thanks What I'm being asked for though is a breakdown by day of week totals not each member of staff.

Week Day----Mon---Tues----Wed----Thurs----Fri----Sat----Sun
OfficeA--------200
OfficeB---------33
Totals-----------233-----333----555------666----765---434-----233

And a Grand total for each office for whole week.
Thanks for your time and help Cmerriman

[DDewarSplunk]

Many thanks for your help last week

D