Greetings,
I need some help crafting a query to populate an employee status board using a Canvas Data Table. The index (prox-reader-*) is receiving logs from a badge reader which sends events when an employee badges-in or badges-out. The idea is that the status board should only show a list of employees that are currently present. My goal is to end up with something like this:
| Employee | Time In |
--------------------------------
| Bob | 8:00 AM |
| Alice | 8:03 AM |
| Jim | 8:06 AM |
I can write a query that creates a Data Table of the most recent badge-in events (see below), but I don't know how to add logic so that only the emplyoee's most recent badge-in will be listed. And how do I avoid listing employees who have since badged-out?
filters
| essql
query="SELECT
full_name AS Employee,
(\"@timestamp\" - INTERVAL 5 HOUR) AS Time
FROM \"prox-reader-*\"
WHERE \"reader\" LIKE 'Entry Door Reader'
AND \"event\" LIKE '8'
"
| mapColumn "Time" fn={getCell "Time" | formatdate format="hh:mm A"}
| table perPage=15
| render
The code above results in a table like this:
| Employee | Time |
--------------------------------
| Bob | 8:00 AM |
| Alice | 8:03 AM |
| Jim | 8:06 AM |
| Jim | 10:06 AM |
| Bob | 11:15 AM |
The value of "reader" could be several different things, but I'm just interested if they've entered or exited the building...so the value could be 'Entry Door Reader' or 'Exit Door Reader'. The 'event' field captures whether or not they were successful...'8' is a successful badge-in/badge-out, but there are a variety of other codes for different types of failures.
How do I write a query that provides a list of all employees that are badged-in, who have not since badged-out, and that only looks at events over the past 10 hours? (The reason I want to limit it to the last 10 hours is in case an employee forgets to badge-out at the end of the day, that way by the next day we'll clear out any false positives from the day before.)
Thanks in advance!