How to determine the number of unique users based on badge (prox) usage

PROBLEM/NEED

In order to understand usage data (typically for licensing), an organization would like to know how many unique users used their badges in a specific time range.

METHOD 1:

Badge/user association is stored in a SQL database. The records in the database include the last time a user tapped their badge (CacheTime column). This time can be used to determine how many users used their badge in the last X number of days.

STEPS

1. Verify the ProxCard SQL database

To determine the ProxCard database, on an XA server, open the eXactACCESS Server Configuration utility, select the Database button, and verify the name of the database in the ProxCard tab:

2. As a user that has permissions to read and write to the database , open the ProxCard SQL database with a tool that will allow you to run SQL queries (e.g. SQL Server Management Studio)

3. Run the following query to get the number of records of users that have tapped their badge in the last 90 days:

SELECT count(*) FROM RFIDtoXAName
WHERE CacheTime >= DATEADD(day, -90, GETDATE())

METHOD 2:

Badge taps are stored in the Audit SQL database. This data can be queried to not only get the number of unique users that have tapped their badge, but how many times they have tapped their badge.

STEPS

1. Verify the Audit SQL database

   To determine the ProxCard database, on an XA server, open the eXactACCESS Server Configuration utility, select the Database button, and verify the name of the database in the AuditServer tab:

2. As a user that has permissions to read and write to the database , open the Audit SQL database with a tool that will allow you to run SQL queries (e.g. SQL Server Management Studio)

3. Here are examples of queries you can run to get badge tap usage data from the audit database:

SELECT count(1)
FROM staging 
WHERE attribs_value like '%BADGE%' and 
   (time_start >= '20200400000000' and time_start <= '20200626000000')

SELECT user_uname, count(1)
FROM staging 
WHERE attribs_value like '%BADGE%' and (time_start >= '20200400000000' and time_start <= '20200626000000')
group by user_uname 

SELECT count (DISTINCT user_uname)
FROM staging
WHERE attribs_value like '%BADGE%' and (time_start >= '20200400000000' and time_start <= '20200626000000')