如何编写essql查询实现Kibana Canvas员工在岗状态看板？

Alright, let's tackle this problem directly. Below is the ESSQL query that will pull exactly the employees who are currently on duty (their latest action in the last 10 hours was a successful entrance swipe, with no subsequent exit swipe), and only show each employee's most recent check-in time:

WITH latest_employee_events AS (
  SELECT
    employee_id,
    employee_name, -- Replace with your actual employee name field (remove if not available)
    event,
    reader,
    @timestamp,
    -- Assign a row number to each employee's events, ordered by most recent first
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY @timestamp DESC) AS event_rank
  FROM "prox-reader-*"
  -- Filter to only events from the last 10 hours
  WHERE @timestamp >= NOW() - INTERVAL 10 HOUR
)
SELECT
  employee_id,
  employee_name,
  @timestamp AS latest_checkin_time
FROM latest_employee_events
WHERE
  -- Only keep each employee's most recent event
  event_rank = 1
  -- Ensure the latest event is a successful entrance swipe
  AND event = '8'
  -- Filter to only entrance readers (replace with your actual reader identifiers)
  AND reader IN ('entrance_main', 'entrance_back') -- Adjust these values to match your system

How This Works #

• CTE latest_employee_events: This first step pulls all swipe records from the last 10 hours, then uses ROW_NUMBER() to group records by employee and sort them from newest to oldest. This lets us easily pick each employee's most recent action with event_rank = 1.
• Main Query Filter: We only keep records where the latest action is a successful entrance swipe (event='8') from an entrance reader. If an employee had an exit swipe after their entrance, their latest event would be the exit, so they'd get filtered out automatically.

Key Notes to Adjust for Your System #

• Replace employee_id and employee_name with the actual fields that identify your employees in the index.
• Update the reader IN (...) clause to match the exact identifiers for your entrance readers (you could also use reader LIKE 'entrance_%' if your readers follow a naming pattern).
• Double-check that event='8' is indeed the correct code for a successful entrance swipe—adjust this value if your system uses a different code.

内容的提问来源于stack exchange，提问作者jjfromnh