Query Details
EV Hunt Dormant Account Tickets
Query
id: a7b8c9d0-e1f2-4a3b-4c5d-6e7f80911223
name: EasyVista - Dormant Accounts Creating Tickets
description: |
Hunts for employees whose contracts have ended but who still appear as requestors on recent tickets.
May indicate compromised credentials or improper offboarding.
requiredDataConnectors:
- connectorId: EasyVistaITSM
dataTypes:
- EasyVista_Employees_CL
- EasyVista_Tickets_CL
tactics:
- InitialAccess
- Persistence
relevantTechniques:
- T1078
query: |
EasyVista_Employees_CL
| where TimeGenerated > ago(30d)
| where isnotempty(END_OF_CONTRACT) and END_OF_CONTRACT < now()
| join kind=inner (
EasyVista_Tickets_CL
| where TimeGenerated > ago(30d)
| summarize TicketCount = count(), LastTicket = max(SUBMIT_DATE_UT), Tickets = make_set(RFC_NUMBER, 5) by REQUESTOR_EMAIL
) on $left.E_MAIL == $right.REQUESTOR_EMAIL
| project EMPLOYEE_ID, LAST_NAME, E_MAIL, END_OF_CONTRACT, DEPARTMENT_PATH, LOCATION_PATH, TicketCount, LastTicket, Tickets
| sort by TicketCount desc
Explanation
This query is designed to identify potential security issues related to employees whose contracts have ended but are still creating tickets in the EasyVista IT Service Management system. Here's a simple breakdown of what the query does:
-
Data Sources: It uses two data sources:
EasyVista_Employees_CL: Contains employee information.EasyVista_Tickets_CL: Contains ticket information.
-
Time Frame: The query looks at data from the past 30 days.
-
Employee Filtering: It filters for employees who have an "END_OF_CONTRACT" date that is in the past, indicating their contract has ended.
-
Ticket Matching: It joins this filtered employee data with ticket data to find employees who have submitted tickets after their contract ended.
-
Data Projection: The query selects specific fields to display, including employee ID, last name, email, contract end date, department, location, number of tickets submitted, the date of the last ticket, and a set of ticket numbers.
-
Sorting: The results are sorted by the number of tickets submitted, in descending order.
The purpose of this query is to detect potential security risks, such as compromised credentials or improper offboarding processes, by identifying former employees who are still active in the ticketing system.
Details
David Alonso
Released: April 16, 2026
Tables
Keywords
Operators