KQL Search

Query Details

HomeAI ToolsDevice Query

Summarizing User Searches Outside Of Normal Working Hours That Contains Sensitive Keywords CISA

Query

//Query is from CISA Playbook https://www.cisa.gov/sites/default/files/2025-01/microsoft-expanded-cloud-logs-implementation-playbook-508c.pdf
let keywords = dynamic(['secret','password','vpn']); //replace with orgspecific keywords or remove
let utc_working_hours = range(2,13); //replace with org specific working hours
CloudAppEvents
| extend client_ip = tostring(RawEventData.ClientIP)
| extend query_text = tostring(RawEventData.QueryText)
| where ActionType == "SearchQueryInitiatedExchange" or ActionType ==
"SearchQueryInitiatedSharePoint"
| where not (datetime_part("Hour",Timestamp) in (utc_working_hours))
| where query_text has_any (keywords)
| take 100
| summarize search_number=count(), make_set(query_text), make_set(client_ip)
by AccountDisplayName

Explanation

This query is designed to analyze cloud application events, specifically focusing on search queries initiated in Exchange or SharePoint. Here's a simplified breakdown of what the query does:

  1. Define Keywords and Working Hours:

    • It sets up a list of keywords (secret, password, vpn) that are of interest. These can be customized to fit the organization's needs.
    • It specifies a range of working hours in UTC (from 2 AM to 1 PM) which can also be adjusted according to the organization's standard working hours.

  2. Extract and Filter Data:

    • It extracts the ClientIP and QueryText from the raw event data for further analysis.
    • It filters the events to only include those where the action type is either "SearchQueryInitiatedExchange" or "SearchQueryInitiatedSharePoint".
    • It further narrows down the results to include only those events that occur outside of the specified working hours.

  3. Keyword Matching:

    • It checks if the search query text contains any of the specified keywords.

  4. Limit and Summarize Results:

    • It limits the results to the first 100 matching events.
    • It summarizes the data by counting the number of searches and compiling a list of unique query texts and client IPs for each account display name.

In essence, this query helps identify potentially suspicious search activities involving sensitive keywords that occur outside of normal working hours, which could indicate unauthorized access or data leakage attempts.

Details

Jay Kerai profile picture

Jay Kerai

Released: January 20, 2025

Tables

CloudAppEvents

Keywords

CloudAppEventsAccountDisplayNameClientIPQueryTextActionTypeTimestamp

Operators

letdynamicrangeextendtostringwhereornotdatetime_partinhas_anytakesummarizecountmake_setby

Actions