Dashboards & Visualizations
Highlighted

Add and subtract dashboard results

Engager

This project is for our badge system. I currently have a dashboard panel that shows how many badges have been used in a day giving us a ballpark figure on how many people are in the office.

I am now working on showing how many people are on the different floors, which I have. The one feature that I need is to have the count change as the person moves from floor to floor.

For an example:
If there are 2 people on the first floor and 5 people on the second floor and on of the 5 people on the second floor badges in on the first floor, I want the dashboard to deduct 1 from 5 and add the person to the latest badged floor which in this case it is the first floor.

I can get a count but don’t know how to link.

Any thought?

Thanks

0 Karma
Highlighted

Re: Add and subtract dashboard results

SplunkTrust
SplunkTrust

Hi @Becherer,

From what you're saying I'm assuming two things :
1- People need to badge to enter a floor, no badging for exit.
2- Multiple people can't exit if only one person badges.

If that's the case then all you need to do is to take the "latest" action any user did over the current day. This should look something like this in SPL :

index=YourBadgesIndex | stats latest(_time) AS _time by UserID, action | dedup UserID

This will give you a table of the latest action per user which means any person that left the building is out. Any person that has his last action as "entered" a floor is currently at that floor. From there all you need to do is count and you get the number of people per floor.

No need to subtract and add as people move from floor to floor, just count over a specific time frame.

Let me know if that helps!

Cheers,
David

0 Karma
Highlighted

Re: Add and subtract dashboard results

Engager

Hi @DavidHourani

First, thank you for responding!

Lets say we have ten floors, we are needing to keep an almost exact count of how many people are on floors for fire reasons. If I am the only person on the first floor the dashboard will show "1" for floor one. If I badge on the sixth floor, the count goes from "0" to "1" on the sixth floor but I need that can cancel out the "1" on the first floor.

What is the best way to do this?

0 Karma
Highlighted

Re: Add and subtract dashboard results

SplunkTrust
SplunkTrust

Hi @Becherer,

As I mentioned, If you take the Latest action any user did it will show where that user is in real time. So all you have to do is to find the last action for all your users and then run a count it would give you exactly how many people there is on each floor.

Let me know if that works for you.

If the search is slow you can consider scheduling it or accelerating it.

Cheers,
David

0 Karma
Highlighted

Re: Add and subtract dashboard results

Engager

Hi @DavidHourani,

I was able to get what you talked about to work.. I am able to display rows and columns with the results.

My next question is how can I use the |timechart to display only one row.

Let me explain..

for the header of the rows is CONTROLLER and CARD. Under CONTROLLER it has a row that displays "first floor" results and another row that displays second floor column.. This is perfect! But I am now needing to display a "|timechart" number count for only one of the rows. What is the easiest way.

i.e. The results currently are: If I display a |timechart of CARD it will add the 4, 7, 2. All I want to do is to see the results for "First Floor", "Second Floor" and "Third Floor".

CONTROLLER...........................................................................................................CARD
First Floor.......................................................................................................................4
Second Floor..................................................................................................................7
Third Floor......................................................................................................................2

I was thinking..
index=YourBadgesIndex | stats latest(_time) AS _time by CONTROLLER, CARD | dedup CARD |timechart count CONTROLLER="First Floor"

Thanks.

0 Karma
Highlighted

Re: Add and subtract dashboard results

SplunkTrust
SplunkTrust

Hi @Bercherer! Glad to hear its working for you !

As for the timechart, it will not be possible to do it if you are using | stats latest(_time) AS _time by CONTROLLER, CARD as this only keeps one time point per card.

If you are trying to get the distinct number of people that accessed each floor over time then you can use this:

index=YourBadgesIndex | timechart dc(CARD) as total by CONTROLLER

You can use span to define the time frame during which you would like to have the count :

 index=YourBadgesIndex | timechart dc(CARD) as total by CONTROLLER span=1h

The results of the timechart will not match the count from your original question because they might include the same user on multiple floors since you're not only taking the latest action per user but all actions.

To avoid that you can run something like this which allows you to keep only the latest card access over a defined timespan :

 index=YourBadgesIndex | bucket _time span=1h |  stats count by _time, CARD, CONTROLLER | dedup _time, CARD |  timechart dc(CARD) as total by CONTROLLER 

Hope this helps ! And again don't forget to upvote and accept the answer if it was helpful 🙂

0 Karma
Highlighted

Re: Add and subtract dashboard results

Esteemed Legend

Just do this::

index="YouShouldAlwaysSpecifyIndex" AND sourcetype="AndSourcetypeToo"
| stats first(floor) AS floor BY UserID
| stats count AS users BY floor

DO NOT CHANGE first(floor) TO ANYTHING ELSE!!!!

0 Karma