- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

[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 asTotalLogins
. - 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. Thesum(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 theTotal
value underFullName
field.
| makeresults | eval message= "Happy Splunking!!!"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

[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 asTotalLogins
. - 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. Thesum(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 theTotal
value underFullName
field.
| makeresults | eval message= "Happy Splunking!!!"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Many thanks for your help last week it was very much appreciated.
David
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Anytime. Do let us know if you need help with anything else 🙂
| makeresults | eval message= "Happy Splunking!!!"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DDewarSplunk, please provide more information about your actual search and how the data is being showed today with you actual search
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Many thanks for your help last week
D
