Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
En este artículo se proporcionan consultas de KQL de ejemplo que puede usar de forma interactiva o en trabajos de KQL para investigar incidentes de seguridad y supervisar la actividad sospechosa en el lago de datos de Microsoft Sentinel.
Consultas listas para usar
Microsoft Sentinel incluye un conjunto de consultas KQL integradas que puede usar para explorar y analizar datos en el lago de datos. Estas consultas están disponibles en el editor de consultas de KQL en la pestaña Consultas . Para obtener más información, consulte Ejecución de consultas KQL.
Aumento de las ubicaciones de inicio de sesión anómalas
Categoría: Actividades de amenazas
Analice el análisis de tendencias de los registros de inicio de sesión de Entra id. para detectar cambios de ubicación inusuales para los usuarios en todas las aplicaciones mediante la computación de las líneas de tendencia de la diversidad de ubicación. Resalta las tres cuentas principales con el mayor aumento en la variabilidad de ubicación y enumera sus ubicaciones asociadas en ventanas de 21 días.
SigninLogs
| where TimeGenerated > ago(1d)
// Forces Log Analytics to recognize that the query should be run over full time range
| extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| project TimeGenerated, AppDisplayName, UserPrincipalName, locationString
// Create time series
| make-series dLocationCount = dcount(locationString) on TimeGenerated step 1d by UserPrincipalName, AppDisplayName
// Compute best fit line for each entry
| extend (RSquare, Slope, Variance, RVariance, Interception, LineFit) = series_fit_line(dLocationCount)
// Chart the 3 most interesting lines
// A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application
| top 3 by Slope desc
// Extract the set of locations for each top user:
| join kind=inner (
SigninLogs
| extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| summarize locationList = makeset(locationString), threeDayWindowLocationCount = dcount(locationString) by AppDisplayName, UserPrincipalName, timerange = bin(TimeGenerated, 21d)
) on AppDisplayName, UserPrincipalName
| order by UserPrincipalName, timerange asc
| project timerange, AppDisplayName, UserPrincipalName, threeDayWindowLocationCount, locationList
| order by AppDisplayName, UserPrincipalName, timerange asc
| extend timestamp = timerange, AccountCustomEntity = UserPrincipalName
Comportamiento anómalo de inicio de sesión en función de los cambios de ubicación
Categoría: Anomalías
Identifique el comportamiento anómalo del inicio de sesión en función de los cambios de ubicación de Entra usuarios de id. y aplicaciones para detectar cambios repentinos en el comportamiento.
SigninLogs
| where TimeGenerated > ago(1d)
// Forces Log Analytics to recognize that the query should be run over full time range
| extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| project TimeGenerated, AppDisplayName, UserPrincipalName, locationString
// Create time series
| make-series dLocationCount = dcount(locationString) on TimeGenerated step 1d by UserPrincipalName, AppDisplayName
// Compute best fit line for each entry
| extend (RSquare, Slope, Variance, RVariance, Interception, LineFit) = series_fit_line(dLocationCount)
// Chart the 3 most interesting lines
// A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application
| top 3 by Slope desc
// Extract the set of locations for each top user:
| join kind=inner (
SigninLogs
| extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| summarize locationList = makeset(locationString), threeDayWindowLocationCount = dcount(locationString) by AppDisplayName, UserPrincipalName, timerange = bin(TimeGenerated, 21d)
) on AppDisplayName, UserPrincipalName
| order by UserPrincipalName, timerange asc
| project timerange, AppDisplayName, UserPrincipalName, threeDayWindowLocationCount, locationList
| order by AppDisplayName, UserPrincipalName, timerange asc
| extend timestamp = timerange, AccountCustomEntity = UserPrincipalName
Auditoría de actividad poco frecuente por aplicación
Categoría: Actividades de amenazas
Busque aplicaciones que realicen acciones poco frecuentes (por ejemplo, consentimiento, concesiones) que puedan crear privilegios silenciosamente. Compare el día actual con los últimos 14 días de auditorías para identificar nuevas actividades de auditoría. Útil para realizar un seguimiento de actividades malintencionadas relacionadas con adiciones o eliminaciones de usuarios o grupos por Azure Apps y aprobaciones automatizadas.
let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let auditLookback = starttime - 14d;
let propertyIgnoreList = dynamic(["TargetId.UserType", "StsRefreshTokensValidFrom", "LastDirSyncTime", "DeviceOSVersion", "CloudDeviceOSVersion", "DeviceObjectVersion"]);
let appIgnoreList = dynamic(["Microsoft Azure AD Group-Based Licensing"]);
let AuditTrail = AuditLogs
| where TimeGenerated between(auditLookback..starttime)
| where isnotempty(tostring(parse_json(tostring(InitiatedBy.app)).displayName))
| extend InitiatedByApp = tostring(parse_json(tostring(InitiatedBy.app)).displayName)
| extend ModProps = TargetResources[0].modifiedProperties
| extend InitiatedByIpAddress = tostring(parse_json(tostring(InitiatedBy.app)).ipAddress)
| extend TargetUserPrincipalName = tolower(tostring(TargetResources[0].userPrincipalName))
| extend TargetResourceName = tolower(tostring(TargetResources[0].displayName))
| mv-expand ModProps
| where isnotempty(tostring(parse_json(tostring(ModProps.newValue))[0]))
| extend PropertyName = tostring(ModProps.displayName), newValue = tostring(parse_json(tostring(ModProps.newValue))[0])
| where PropertyName !in~ (propertyIgnoreList) and (PropertyName !~ "Action Client Name" and newValue !~ "DirectorySync") and (PropertyName !~ "Included Updated Properties" and newValue !~ "LastDirSyncTime")
| where InitiatedByApp !in~ (appIgnoreList) and OperationName !~ "Change user license"
| summarize by OperationName, InitiatedByApp, TargetUserPrincipalName, InitiatedByIpAddress, TargetResourceName, PropertyName;
let AccountMods = AuditLogs
| where TimeGenerated >= starttime
| where isnotempty(tostring(parse_json(tostring(InitiatedBy.app)).displayName))
| extend InitiatedByApp = tostring(parse_json(tostring(InitiatedBy.app)).displayName)
| extend ModProps = TargetResources[0].modifiedProperties
| extend InitiatedByIpAddress = tostring(parse_json(tostring(InitiatedBy.app)).ipAddress)
| extend TargetUserPrincipalName = tolower(tostring(TargetResources[0].userPrincipalName))
| extend TargetResourceName = tolower(tostring(TargetResources[0].displayName))
| mv-expand ModProps
| where isnotempty(tostring(parse_json(tostring(ModProps.newValue))[0]))
| extend PropertyName = tostring(ModProps.displayName), newValue = tostring(parse_json(tostring(ModProps.newValue))[0])
| where PropertyName !in~ (propertyIgnoreList) and (PropertyName !~ "Action Client Name" and newValue !~ "DirectorySync") and (PropertyName !~ "Included Updated Properties" and newValue !~ "LastDirSyncTime")
| where InitiatedByApp !in~ (appIgnoreList) and OperationName !~ "Change user license"
| extend ModifiedProps = pack("PropertyName", PropertyName, "newValue", newValue, "Id", Id, "CorrelationId", CorrelationId)
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), Activity = make_bag(ModifiedProps) by Type, InitiatedByApp, TargetUserPrincipalName, InitiatedByIpAddress, TargetResourceName, Category, OperationName, PropertyName;
let RareAudits = AccountMods
| join kind=leftanti (
AuditTrail
) on OperationName, InitiatedByApp, InitiatedByIpAddress, TargetUserPrincipalName; //, PropertyName; //uncomment if you want to see Rare Property changes.
RareAudits
| summarize StartTime = min(StartTimeUtc), EndTime = max(EndTimeUtc), make_set(Activity), make_set(PropertyName) by InitiatedByApp, OperationName, TargetUserPrincipalName, InitiatedByIpAddress, TargetResourceName
| order by TargetUserPrincipalName asc, StartTime asc
| extend timestamp = StartTime, AccountCustomEntity = TargetUserPrincipalName, HostCustomEntity = iff(set_PropertyName has_any ('DeviceOSType', 'CloudDeviceOSType'), TargetResourceName, ''), IPCustomEntity = InitiatedByIpAddress
Azure operaciones de nivel de suscripción poco frecuentes
Categoría: Actividades de amenazas
Identifique eventos confidenciales Azure de nivel de suscripción en función de los registros de actividad de Azure. Por ejemplo, la supervisión basada en el nombre de la operación "Crear o actualizar instantánea", que se usa para crear copias de seguridad, pero que los atacantes podrían usar de forma incorrecta para volcar hashes o extraer información confidencial del disco.
let starttime = 14d;
let endtime = 1d;
// The number of operations above which an IP address is considered an unusual source of role assignment operations
let alertOperationThreshold = 5;
// Add or remove operation names below as per your requirements. For operations lists, please refer to https://learn.microsoft.com/en-us/Azure/role-based-access-control/resource-provider-operations#all
let SensitiveOperationList = dynamic(["microsoft.compute/snapshots/write", "microsoft.network/networksecuritygroups/write", "microsoft.storage/storageaccounts/listkeys/action"]);
let SensitiveActivity = AzureActivity
| where OperationNameValue in~ (SensitiveOperationList) or OperationNameValue hassuffix "listkeys/action"
| where ActivityStatusValue =~ "Success";
SensitiveActivity
| where TimeGenerated between (ago(starttime) .. ago(endtime))
| summarize count() by CallerIpAddress, Caller, OperationNameValue, bin(TimeGenerated, 1d)
| where count_ >= alertOperationThreshold
// Returns all the records from the right side that don't have matches from the left
| join kind=rightanti (
SensitiveActivity
| where TimeGenerated >= ago(endtime)
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ActivityTimeStamp = make_list(TimeGenerated), ActivityStatusValue = make_list(ActivityStatusValue), CorrelationIds = make_list(CorrelationId), ResourceGroups = make_list(ResourceGroup), SubscriptionIds = make_list(SubscriptionId), ActivityCountByCallerIPAddress = count() by CallerIpAddress, Caller, OperationNameValue
| where ActivityCountByCallerIPAddress >= alertOperationThreshold
) on CallerIpAddress, Caller, OperationNameValue
| extend Name = tostring(split(Caller, '@', 0)[0]), UPNSuffix = tostring(split(Caller, '@', 1)[0])
Tendencia de actividad diaria por aplicación en AuditLogs
Categoría: Líneas base
Desde los últimos 14 días, identifique cualquier operación de "Consentimiento a la aplicación" que un usuario o aplicación realice. Esto podría indicar que se proporcionaron permisos para acceder a la instancia de AzureApp de la lista a un actor malintencionado. Dar su consentimiento a la aplicación, agregar una entidad de servicio y agregar eventos Auth2PermissionGrant debe ser poco frecuente. Si está disponible, se agrega contexto adicional desde auditlogs basados en CorrleationId desde la misma cuenta que realizó "Consentimiento para la aplicación".
let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let auditLookback = starttime - 14d;
// Setting threshold to 3 as a default, change as needed. Any operation that has been initiated by a user or app more than 3 times in the past 30 days will be exluded
let threshold = 3;
// Helper function to extract relevant fields from AuditLog events
let auditLogEvents = (startTimeSpan:datetime) {
AuditLogs
| where TimeGenerated >= startTimeSpan
| extend ModProps = TargetResources[0].modifiedProperties
| extend IpAddress = iff(isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)),
tostring(parse_json(tostring(InitiatedBy.user)).ipAddress),
tostring(parse_json(tostring(InitiatedBy.app)).ipAddress)
)
| extend InitiatedBy = iff(isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)),
tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName),
tostring(parse_json(tostring(InitiatedBy.app)).displayName)
)
| extend TargetResourceName = tolower(tostring(TargetResources[0].displayName))
| mv-expand ModProps
| extend PropertyName = tostring(ModProps.displayName), newValue = replace('"', "", tostring(ModProps.newValue))
};
// Get just the InitiatedBy and CorrleationId so we can look at associated audit activity
// 2 other operations that can be part of malicious activity in this situation are
// "Add OAuth2PermissionGrant" and "Add service principal", replace the below if you are interested in those as starting points for OperationName
let HistoricalConsent = auditLogEvents(auditLookback)
| where OperationName == "Consent to application"
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), OperationCount = count()
by InitiatedBy, IpAddress, TargetResourceName, Category, OperationName, PropertyName, newValue, CorrelationId, Id
// Remove comment below to only include operations initiated by a user or app that is above the threshold for the last 30 days
//| where OperationCount > threshold
;
let Correlate = HistoricalConsent
| summarize by InitiatedBy, CorrelationId;
// 2 other operations that can be part of malicious activity in this situation are
// "Add OAuth2PermissionGrant" and "Add service principal", replace the below if you changed the starting OperationName above
let allOtherEvents = auditLogEvents(auditLookback)
| where OperationName != "Consent to application";
// Gather associated activity based on audit activity for "Consent to application" and InitiatedBy and CorrleationId
let CorrelatedEvents = Correlate
| join (allOtherEvents) on InitiatedBy, CorrelationId
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated)
by InitiatedBy, IpAddress, TargetResourceName, Category, OperationName, PropertyName, newValue, CorrelationId, Id
;
// Union the results
let Results = (union isfuzzy=true HistoricalConsent, CorrelatedEvents);
// newValues that are simple semi-colon separated, make those dynamic for easy viewing and Aggregate into the PropertyUpdate set based on CorrelationId and Id(DirectoryId)
Results
| extend newValue = split(newValue, ";")
| extend PropertyUpdate = pack(PropertyName, newValue, "Id", Id)
// Extract scope requested
| extend perms = tostring(parse_json(tostring(PropertyUpdate.["ConsentAction.Permissions"]))[0])
| extend scope = extract('Scope:\\s*([^,\\]]*)', 1, perms)
// Filter out some common openid, and low privilege request scopes - uncomment line below to filter out where no scope is requested
//| where isnotempty(scope)
| where scope !contains 'openid' and scope !in ('user_impersonation', 'User.Read')
| summarize StartTime = min(StartTimeUtc), EndTime = max(EndTimeUtc), PropertyUpdateSet = make_bag(PropertyUpdate), make_set(scope)
by InitiatedBy, IpAddress, TargetResourceName, OperationName, CorrelationId
| extend timestamp = StartTime, AccountCustomEntity = InitiatedBy, IPCustomEntity = IpAddress
// uncommnet below to summarize by app if many results
//| summarize make_set(InitiatedBy), make_set(IpAddress), make_set(PropertyUpdateSet) by TargetResourceName, tostring(set_scope)
Tendencia de ubicación diaria por usuario o aplicación en SignInLogs
Categoría: Línea base
Cree tendencias diarias para todos los inicios de sesión de usuario, el número de ubicaciones y el uso de su aplicación.
SigninLogs
| where TimeGenerated > ago(1d)
| extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize LocationList = make_set(locationString), LocationCount = dcount(locationString), DistinctSourceIp = dcount(IPAddress), LogonCount = count() by Day, AppDisplayName, UserPrincipalName
Tendencia diaria del tráfico de red por IP de destino
Categoría: Línea base
Cree una línea base que incluya bytes y pares distintos para detectar la señalización y la filtración.
// Daily Network traffic trend Per destination IP along with data transfer stats
CommonSecurityLog
| where TimeGenerated > ago(1d)
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize Count = count(), DistinctDestinationIps = dcount(DestinationIP), NoofByesTransferred = sum(SentBytes), NoofBytesReceived = sum(ReceivedBytes) by Day, SourceIP, DeviceVendor
Tendencia diaria del tráfico de red por IP de destino con estadísticas de transferencia de datos
Categoría: Actividades de amenazas
Identifique el host interno que alcanzó el destino saliente, incluidas las tendencias de volumen y la estimación del radio de explosión.
// Daily Network traffic trend Per Destination IP along with Data transfer stats
// Frequency - Daily - Maintain 30 days or more history.
CommonSecurityLog
| where TimeGenerated > ago(1d)
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize Count = count(), DistinctDestinationIps = dcount(DestinationIP), NoofByesTransferred = sum(SentBytes), NoofBytesReceived = sum(ReceivedBytes) by Day, SourceIP, DeviceVendor
Tendencia diaria del tráfico de red por ip de origen
Categoría: Línea base
Cree una línea base que incluya bytes y pares distintos para detectar la señalización y la filtración.
// Daily Network traffic trend Per source IP along with data transfer stats
CommonSecurityLog
| where TimeGenerated > ago(1d)
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize Count = count(), DistinctSourceIps = dcount(SourceIP), NoofByesTransferred = sum(SentBytes), NoofBytesReceived = sum(ReceivedBytes) by Day, DestinationIP, DeviceVendor
Tendencia diaria del tráfico de red por IP de origen con estadísticas de transferencia de datos
Categoría: Actividades de amenazas
Las conexiones y bytes actuales se evalúan con respecto a la línea base día a día del host para determinar si los comportamientos observados se desvía significativamente del patrón establecido.
// Daily Network traffic trend Per Destination IP along with Data transfer stats
// Frequency - Daily - Maintain 30 days or more history.
CommonSecurityLog
| where TimeGenerated > ago(1d)
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize Count = count(), DistinctDestinationIps = dcount(DestinationIP), NoofByesTransferred = sum(SentBytes), NoofBytesReceived = sum(ReceivedBytes) by Day, SourceIP, DeviceVendor
Tendencia diaria de ubicación de inicio de sesión por usuario y aplicación
Categoría: Línea base
Cree una línea base de inicio de sesión para cada usuario o aplicación con la dirección IP y geográfica típicas, lo que permite una detección de anomalías eficaz y rentable a escala.
// Daily Location Trend per User, App in SigninLogs
// Frequency - Daily - Maintain 30 days or more history.
SigninLogs
| where TimeGenerated > ago(1d)
| extend locationString = strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize LocationList = make_set(locationString), LocationCount = dcount(locationString), DistinctSourceIp = dcount(IPAddress), LogonCount = count() by Day, AppDisplayName, UserPrincipalName
Tendencia diaria de ejecución de procesos
Categoría: Línea base
Identifique los nuevos procesos y la prevalencia, lo que facilita las detecciones de "nuevos procesos poco frecuentes".
// Daily ProcessExecution Trend in SecurityEvents
// Frequency - Daily - Maintain 30 days or more history.
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4688
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize Count = count(), DistinctComputers = dcount(Computer), DistinctAccounts = dcount(Account), DistinctParent = dcount(ParentProcessName), NoofCommandLines = dcount(CommandLine) by Day, NewProcessName
Entra agente de usuario poco frecuente por aplicación
Categoría: Detección de anomalías
Establezca una línea base del tipo de UserAgent (es decir, explorador, aplicación de office, etc.) que se usa normalmente para una aplicación determinada examinando durante varios días. A continuación, busca en el día actual cualquier desviación de este patrón, es decir, los tipos de UserAgents que no se han visto antes en combinación con esta aplicación.
let minimumAppThreshold = 100;
let timeframe = 1d;
let lookback_timeframe = 7d;
let ExtractBrowserTypeFromUA = (ua:string) {
// Note: these are in a specific order since, for example, Edge contains "Chrome/" and "Edge/" strings.
case(
ua has "Edge/", dynamic({"AgentType": "Browser", "AgentName": "Edge"}),
ua has "Edg/", dynamic({"AgentType": "Browser", "AgentName": "Edge"}),
ua has "Trident/", dynamic({"AgentType": "Browser", "AgentName": "Internet Explorer"}),
ua has "Chrome/" and ua has "Safari/", dynamic({"AgentType": "Browser", "AgentName": "Chrome"}),
ua has "Gecko/" and ua has "Firefox/", dynamic({"AgentType": "Browser", "AgentName": "Firefox"}),
not(ua has "Mobile/") and ua has "Safari/" and ua has "Version/", dynamic({"AgentType": "Browser", "AgentName": "Safari"}),
ua startswith "Dalvik/" and ua has "Android", dynamic({"AgentType": "Browser", "AgentName": "Android Browser"}),
ua startswith "MobileSafari//", dynamic({"AgentType": "Browser", "AgentName": "Mobile Safari"}),
ua has "Mobile/" and ua has "Safari/" and ua has "Version/", dynamic({"AgentType": "Browser", "AgentName": "Mobile Safari"}),
ua has "Mobile/" and ua has "FxiOS/", dynamic({"AgentType": "Browser", "AgentName": "IOS Firefox"}),
ua has "Mobile/" and ua has "CriOS/", dynamic({"AgentType": "Browser", "AgentName": "IOS Chrome"}),
ua has "Mobile/" and ua has "WebKit/", dynamic({"AgentType": "Browser", "AgentName": "Mobile Webkit"}),
//
ua startswith "Excel/", dynamic({"AgentType": "OfficeApp", "AgentName": "Excel"}),
ua startswith "Outlook/", dynamic({"AgentType": "OfficeApp", "AgentName": "Outlook"}),
ua startswith "OneDrive/", dynamic({"AgentType": "OfficeApp", "AgentName": "OneDrive"}),
ua startswith "OneNote/", dynamic({"AgentType": "OfficeApp", "AgentName": "OneNote"}),
ua startswith "Office/", dynamic({"AgentType": "OfficeApp", "AgentName": "Office"}),
ua startswith "PowerPoint/", dynamic({"AgentType": "OfficeApp", "AgentName": "PowerPoint"}),
ua startswith "PowerApps/", dynamic({"AgentType": "OfficeApp", "AgentName": "PowerApps"}),
ua startswith "SharePoint/", dynamic({"AgentType": "OfficeApp", "AgentName": "SharePoint"}),
ua startswith "Word/", dynamic({"AgentType": "OfficeApp", "AgentName": "Word"}),
ua startswith "Visio/", dynamic({"AgentType": "OfficeApp", "AgentName": "Visio"}),
ua startswith "Whiteboard/", dynamic({"AgentType": "OfficeApp", "AgentName": "Whiteboard"}),
ua =~ "Mozilla/5.0 (compatible; MSAL 1.0)", dynamic({"AgentType": "OfficeApp", "AgentName": "Office Telemetry"}),
//
ua has ".NET CLR", dynamic({"AgentType": "Custom", "AgentName": "Dotnet"}),
ua startswith "Java/", dynamic({"AgentType": "Custom", "AgentName": "Java"}),
ua startswith "okhttp/", dynamic({"AgentType": "Custom", "AgentName": "okhttp"}),
ua has "Drupal/", dynamic({"AgentType": "Custom", "AgentName": "Drupal"}),
ua has "PHP/", dynamic({"AgentType": "Custom", "AgentName": "PHP"}),
ua startswith "curl/", dynamic({"AgentType": "Custom", "AgentName": "curl"}),
ua has "python-requests", dynamic({"AgentType": "Custom", "AgentName": "Python"}),
pack("AgentType", "Other", "AgentName", extract(@"^([^/]*)/", 1, ua))
)
};
// Query to obtain 'simplified' user agents in a given timespan.
let QueryUserAgents = (start_time:timespan, end_time:timespan) {
union withsource=tbl_name AADNonInteractiveUserSignInLogs, SigninLogs
| where TimeGenerated >= ago(start_time)
| where TimeGenerated < ago(end_time)
| where ResultType == 0 // Only look at succesful logins
| extend ParsedUserAgent = ExtractBrowserTypeFromUA(UserAgent)
| extend UserAgentType = tostring(ParsedUserAgent.AgentType)
| extend UserAgentName = tostring(ParsedUserAgent.AgentName)
//| extend SimpleUserAgent=strcat(UserAgentType,"_",UserAgentName)
| extend SimpleUserAgent = UserAgentType
| where not(isempty(UserAgent))
| where not(isempty(AppId))
};
// Get baseline usage per application.
let BaselineUserAgents = materialize(
QueryUserAgents(lookback_timeframe + timeframe, timeframe)
| summarize RequestCount = count() by AppId, AppDisplayName, SimpleUserAgent
);
let BaselineSummarizedAgents = (
BaselineUserAgents
| summarize BaselineUAs = make_set(SimpleUserAgent), BaselineRequestCount = sum(RequestCount) by AppId, AppDisplayName
);
QueryUserAgents(timeframe, 0d)
| summarize count() by AppId, AppDisplayName, UserAgent, SimpleUserAgent
| join kind=leftanti BaselineUserAgents on AppId, AppDisplayName, SimpleUserAgent
| join BaselineSummarizedAgents on AppId, AppDisplayName
| where BaselineRequestCount > minimumAppThreshold // Search only for actively used applications.
// Get back full original requests.
| join (QueryUserAgents(timeframe, 0d)) on AppId, UserAgent
| project-away ParsedUserAgent, UserAgentName
| project-reorder TimeGenerated, AppDisplayName, UserPrincipalName, UserAgent, BaselineUAs
// Begin allow-list.
// End allow-list.
| summarize count() by UserPrincipalName, AppDisplayName, AppId, UserAgentType, SimpleUserAgent, UserAgent
Coincidencia de IOC de registro de red
Categoría: Actividades de amenazas
Identifique los indicadores ip de peligro (IOC) de inteligencia sobre amenazas (TI) mediante la búsqueda de coincidencias en CommonSecurityLog.
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let dt_lookBack = 1h; // Look back 1 hour for CommonSecurityLog events
let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
// Fetch threat intelligence indicators related to IP addresses
let IP_Indicators = ThreatIntelIndicators
//extract key part of kv pair
| extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
| where IndicatorType in ("ipv4-addr", "ipv6-addr", "network-traffic")
| extend NetworkSourceIP = toupper(ObservableValue)
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| where TimeGenerated >= ago(ioc_lookBack)
| extend TI_ipEntity = iff(isnotempty(NetworkSourceIP), NetworkSourceIP, NetworkSourceIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| where ipv4_is_private(TI_ipEntity) == false and TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127."
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
| where IsActive and (ValidUntil > now() or isempty(ValidUntil));
// Perform a join between IP indicators and CommonSecurityLog events
IP_Indicators
| project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, TI_ipEntity
// Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
| join kind=innerunique (
CommonSecurityLog
| where TimeGenerated >= ago(dt_lookBack)
| extend MessageIP = extract(IPRegex, 0, Message)
| extend CS_ipEntity = iff((not(ipv4_is_private(SourceIP)) and isnotempty(SourceIP)), SourceIP, DestinationIP)
| extend CS_ipEntity = iff(isempty(CS_ipEntity) and isnotempty(MessageIP), MessageIP, CS_ipEntity)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.TI_ipEntity == $right.CS_ipEntity
// Filter out logs that occurred after the expiration of the corresponding indicator
| where CommonSecurityLog_TimeGenerated < ValidUntil
// Group the results by IndicatorId and CS_ipEntity, and keep the log entry with the latest timestamp
| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by Id, CS_ipEntity
// Select the desired output fields
| project timestamp = CommonSecurityLog_TimeGenerated, SourceIP, DestinationIP, MessageIP, Message, DeviceVendor, DeviceProduct, Id, ValidUntil, Confidence, TI_ipEntity, CS_ipEntity, LogSeverity, DeviceAction
Nuevos procesos observados en las últimas 24 horas
Categoría: Actividades de amenazas
Los nuevos procesos en entornos estables pueden indicar actividad malintencionada. Analizar las sesiones de inicio de sesión en las que se ejecutaron estos archivos binarios puede ayudar a identificar ataques.
let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let lookback = starttime - 14d;
let ProcessCreationEvents = () {
SecurityEvent
| where TimeGenerated between(lookback..endtime)
| where EventID == 4688
| project
TimeGenerated,
Computer,
Account,
FileName = tostring(split(NewProcessName, '\\')[-1]),
NewProcessName,
ProcessCommandLine = CommandLine,
InitiatingProcessFileName = ParentProcessName
};
ProcessCreationEvents()
| where TimeGenerated between(lookback..starttime)
| summarize HostCount = dcount(Computer) by FileName
| join kind=rightanti (
ProcessCreationEvents()
| where TimeGenerated between(starttime..endtime)
| summarize
StartTime = min(TimeGenerated),
EndTime = max(TimeGenerated),
Computers = make_set(Computer, 1000),
HostCount = dcount(Computer)
by Account, NewProcessName, FileName, ProcessCommandLine, InitiatingProcessFileName
) on FileName
| extend timestamp = StartTime
| extend NTDomain = tostring(split(Account, '\\', 0)[0]), Name = tostring(split(Account, '\\', 1)[0])
| extend Account_0_Name = Name
| extend Account_0_NTDomain = NTDomain
Operación de archivo de SharePoint a través de direcciones IP no vista anteriormente
Categoría: Actividades de amenazas
Identifique anomalías mediante el comportamiento del usuario estableciendo un umbral para cambios significativos en las actividades de carga y descarga de archivos desde nuevas direcciones IP. Establece una línea base de comportamiento típico, la compara con la actividad reciente y marca las desviaciones que superan un umbral predeterminado de 25.
// Define a threshold for significant deviations
let threshold = 25;
// Define the name for the SharePoint File Operation record type
let szSharePointFileOperation = "SharePointFileOperation";
// Define an array of SharePoint operations of interest
let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
// Define the start and end time for the analysis period
let starttime = 14d;
let endtime = 1d;
// Define a baseline of normal user behavior
let userBaseline = OfficeActivity
| where TimeGenerated between(ago(starttime) .. ago(endtime))
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| where isnotempty(UserAgent)
| summarize Count = count() by UserId, Operation, Site_Url, ClientIP
| summarize AvgCount = avg(Count) by UserId, Operation, Site_Url, ClientIP;
// Get recent user activity
let recentUserActivity = OfficeActivity
| where TimeGenerated > ago(endtime)
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| where isnotempty(UserAgent)
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), RecentCount = count() by UserId, UserType, Operation, Site_Url, ClientIP, OfficeObjectId, OfficeWorkload, UserAgent;
// Join the baseline and recent activity, and calculate the deviation
let UserBehaviorAnalysis = userBaseline
| join kind=inner (recentUserActivity) on UserId, Operation, Site_Url, ClientIP
| extend Deviation = abs(RecentCount - AvgCount) / AvgCount;
// Filter for significant deviations
UserBehaviorAnalysis
| where Deviation > threshold
| project StartTimeUtc, EndTimeUtc, UserId, UserType, Operation, ClientIP, Site_Url, OfficeObjectId, OfficeWorkload, UserAgent, Deviation, Count = RecentCount
| order by Count desc, ClientIP asc, Operation asc, UserId asc
| extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
Señalización de red potencial de Palo Alto
Categoría: Actividades de amenazas
Identifique los patrones de señalización de los registros de tráfico de Palo Alto Network en función de los patrones de diferencia de tiempo recurrentes. La consulta usa varias funciones de KQL para calcular diferencias de tiempo y, a continuación, la compara con el total de eventos observados en un día para encontrar el porcentaje de balizamiento.
let starttime = 2d;
let endtime = 1d;
let TimeDeltaThreshold = 25;
let TotalEventsThreshold = 30;
let MostFrequentTimeDeltaThreshold = 25;
let PercentBeaconThreshold = 80;
CommonSecurityLog
| where DeviceVendor == "Palo Alto Networks" and Activity == "TRAFFIC"
| where TimeGenerated between (startofday(ago(starttime)) .. startofday(ago(endtime)))
| where ipv4_is_private(DestinationIP) == false
| project TimeGenerated, DeviceName, SourceUserID, SourceIP, SourcePort, DestinationIP, DestinationPort, ReceivedBytes, SentBytes
| sort by SourceIP asc, TimeGenerated asc, DestinationIP asc, DestinationPort asc
| serialize
| extend nextTimeGenerated = next(TimeGenerated, 1), nextSourceIP = next(SourceIP, 1)
| extend TimeDeltainSeconds = datetime_diff('second', nextTimeGenerated, TimeGenerated)
| where SourceIP == nextSourceIP
//Allowlisting criteria/ threshold criteria
| where TimeDeltainSeconds > TimeDeltaThreshold
| summarize count(), sum(ReceivedBytes), sum(SentBytes) by TimeDeltainSeconds, bin(TimeGenerated, 1h), DeviceName, SourceUserID, SourceIP, DestinationIP, DestinationPort
| summarize (MostFrequentTimeDeltaCount, MostFrequentTimeDeltainSeconds) = arg_max(count_, TimeDeltainSeconds), TotalEvents = sum(count_), TotalSentBytes = sum(sum_SentBytes), TotalReceivedBytes = sum(sum_ReceivedBytes) by bin(TimeGenerated, 1h), DeviceName, SourceUserID, SourceIP, DestinationIP, DestinationPort
| where TotalEvents > TotalEventsThreshold and MostFrequentTimeDeltaCount > MostFrequentTimeDeltaThreshold
| extend BeaconPercent = MostFrequentTimeDeltaCount / toreal(TotalEvents) * 100
| where BeaconPercent > PercentBeaconThreshold
Inicio de sesión sospechoso de Windows fuera del horario normal
Categoría: Detección de anomalías
Identifique eventos de inicio de sesión de Windows inusuales fuera del horario normal de un usuario comparando con la actividad de inicio de sesión de los últimos 14 días, marcando anomalías basadas en patrones históricos.
let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let lookback = starttime - 14d;
let AllLogonEvents = materialize(
SecurityEvent
| where TimeGenerated between (lookback..starttime)
| where EventID in (4624, 4625)
| where LogonTypeName in~ ('2 - Interactive', '10 - RemoteInteractive')
| where AccountType =~ 'User'
| extend HourOfLogin = hourofday(TimeGenerated), DayNumberofWeek = dayofweek(TimeGenerated)
| extend DayofWeek = case(
DayNumberofWeek == "00:00:00", "Sunday",
DayNumberofWeek == "1.00:00:00", "Monday",
DayNumberofWeek == "2.00:00:00", "Tuesday",
DayNumberofWeek == "3.00:00:00", "Wednesday",
DayNumberofWeek == "4.00:00:00", "Thursday",
DayNumberofWeek == "5.00:00:00", "Friday",
DayNumberofWeek == "6.00:00:00", "Saturday", "InvalidTimeStamp"
)
// map the most common ntstatus codes
| extend StatusDesc = case(
Status =~ "0x80090302", "SEC_E_UNSUPPORTED_FUNCTION",
Status =~ "0x80090308", "SEC_E_INVALID_TOKEN",
Status =~ "0x8009030E", "SEC_E_NO_CREDENTIALS",
Status =~ "0xC0000008", "STATUS_INVALID_HANDLE",
Status =~ "0xC0000017", "STATUS_NO_MEMORY",
Status =~ "0xC0000022", "STATUS_ACCESS_DENIED",
Status =~ "0xC0000034", "STATUS_OBJECT_NAME_NOT_FOUND",
Status =~ "0xC000005E", "STATUS_NO_LOGON_SERVERS",
Status =~ "0xC000006A", "STATUS_WRONG_PASSWORD",
Status =~ "0xC000006D", "STATUS_LOGON_FAILURE",
Status =~ "0xC000006E", "STATUS_ACCOUNT_RESTRICTION",
Status =~ "0xC0000073", "STATUS_NONE_MAPPED",
Status =~ "0xC00000FE", "STATUS_NO_SUCH_PACKAGE",
Status =~ "0xC000009A", "STATUS_INSUFFICIENT_RESOURCES",
Status =~ "0xC00000DC", "STATUS_INVALID_SERVER_STATE",
Status =~ "0xC0000106", "STATUS_NAME_TOO_LONG",
Status =~ "0xC000010B", "STATUS_INVALID_LOGON_TYPE",
Status =~ "0xC000015B", "STATUS_LOGON_TYPE_NOT_GRANTED",
Status =~ "0xC000018B", "STATUS_NO_TRUST_SAM_ACCOUNT",
Status =~ "0xC0000224", "STATUS_PASSWORD_MUST_CHANGE",
Status =~ "0xC0000234", "STATUS_ACCOUNT_LOCKED_OUT",
Status =~ "0xC00002EE", "STATUS_UNFINISHED_CONTEXT_DELETED",
EventID == 4624, "Success",
"See - https://docs.microsoft.com/openspecs/windows_protocols/ms-erref/596a1078-e883-4972-9bbc-49e60bebca55"
)
| extend SubStatusDesc = case(
SubStatus =~ "0x80090325", "SEC_E_UNTRUSTED_ROOT",
SubStatus =~ "0xC0000008", "STATUS_INVALID_HANDLE",
SubStatus =~ "0xC0000022", "STATUS_ACCESS_DENIED",
SubStatus =~ "0xC0000064", "STATUS_NO_SUCH_USER",
SubStatus =~ "0xC000006A", "STATUS_WRONG_PASSWORD",
SubStatus =~ "0xC000006D", "STATUS_LOGON_FAILURE",
SubStatus =~ "0xC000006E", "STATUS_ACCOUNT_RESTRICTION",
SubStatus =~ "0xC000006F", "STATUS_INVALID_LOGON_HOURS",
SubStatus =~ "0xC0000070", "STATUS_INVALID_WORKSTATION",
SubStatus =~ "0xC0000071", "STATUS_PASSWORD_EXPIRED",
SubStatus =~ "0xC0000072", "STATUS_ACCOUNT_DISABLED",
SubStatus =~ "0xC0000073", "STATUS_NONE_MAPPED",
SubStatus =~ "0xC00000DC", "STATUS_INVALID_SERVER_STATE",
SubStatus =~ "0xC0000133", "STATUS_TIME_DIFFERENCE_AT_DC",
SubStatus =~ "0xC000018D", "STATUS_TRUSTED_RELATIONSHIP_FAILURE",
SubStatus =~ "0xC0000193", "STATUS_ACCOUNT_EXPIRED",
SubStatus =~ "0xC0000380", "STATUS_SMARTCARD_WRONG_PIN",
SubStatus =~ "0xC0000381", "STATUS_SMARTCARD_CARD_BLOCKED",
SubStatus =~ "0xC0000382", "STATUS_SMARTCARD_CARD_NOT_AUTHENTICATED",
SubStatus =~ "0xC0000383", "STATUS_SMARTCARD_NO_CARD",
SubStatus =~ "0xC0000384", "STATUS_SMARTCARD_NO_KEY_CONTAINER",
SubStatus =~ "0xC0000385", "STATUS_SMARTCARD_NO_CERTIFICATE",
SubStatus =~ "0xC0000386", "STATUS_SMARTCARD_NO_KEYSET",
SubStatus =~ "0xC0000387", "STATUS_SMARTCARD_IO_ERROR",
SubStatus =~ "0xC0000388", "STATUS_DOWNGRADE_DETECTED",
SubStatus =~ "0xC0000389", "STATUS_SMARTCARD_CERT_REVOKED",
EventID == 4624, "Success",
"See - https://docs.microsoft.com/openspecs/windows_protocols/ms-erref/596a1078-e883-4972-9bbc-49e60bebca55"
)
| project StartTime = TimeGenerated, DayofWeek, HourOfLogin, EventID, Activity, IpAddress, WorkstationName, Computer, TargetUserName, TargetDomainName, ProcessName, SubjectUserName, PrivilegeList, LogonTypeName, StatusDesc, SubStatusDesc
);
AllLogonEvents
| where TargetDomainName !in ("Window Manager", "Font Driver Host")
| summarize max(HourOfLogin), min(HourOfLogin), historical_DayofWeek = make_set(DayofWeek, 10) by TargetUserName
| join kind=inner (
AllLogonEvents
| where StartTime between(starttime..endtime)
) on TargetUserName
// Filtering for logon events based on range of max and min of historical logon hour values seen
| where HourOfLogin > max_HourOfLogin or HourOfLogin < min_HourOfLogin
// Also populating additional column showing historical days of week when logon was seen
| extend historical_DayofWeek = tostring(historical_DayofWeek)
| summarize Total = count(), max(HourOfLogin), min(HourOfLogin), current_DayofWeek = make_set(DayofWeek, 10), StartTime = max(StartTime), EndTime = min(StartTime), SourceIP = make_set(IpAddress, 10000), SourceHost = make_set(WorkstationName, 10000), SubjectUserName = make_set(SubjectUserName, 10000), HostLoggedOn = make_set(Computer, 10000) by EventID, Activity, TargetDomainName, TargetUserName, ProcessName, LogonTypeName, StatusDesc, SubStatusDesc, historical_DayofWeek
| extend historical_DayofWeek = todynamic(historical_DayofWeek)
| extend timestamp = StartTime, NTDomain = split(TargetUserName, '\\', 0)[0], Name = split(TargetUserName, '\\', 1)[0]
| extend Account_0_NTDomain = NTDomain
| extend Account_0_Name = Name
Consultas de ejemplo adicionales
Las siguientes consultas de ejemplo se pueden usar para explorar y analizar datos en el lago de datos de Microsoft Sentinel.
Identificación de posibles amenazas internas
Detecte el acceso histórico a archivos de documentos confidenciales en puntos de conexión mediante la correlación de la actividad de archivos con la etiqueta de confidencialidad de Microsoft Purview, por ejemplo Confidencial, Altamente confidencial o Restringido. Use esta consulta para descubrir signos de filtración de datos, infracciones de directivas o comportamientos sospechosos del usuario que pueden haber pasado desapercibidos durante el período de tiempo original de 90 a 180 días.
DeviceFileEvents
| where Timestamp between (datetime_add("day", -180, now()) .. datetime_add("day", -90, now()))
| where FileName endswith ".docx" or FileName endswith ".pdf" or FileName endswith ".xlsx"
| where FolderPath contains "Confidential" or FolderPath contains "Sensitive" or FolderPath contains "Restricted"
| where ActionType in ("FileAccessed", "FileRead", "FileModified", "FileCopied", "FileMoved")
| extend User = tostring(InitiatingProcessAccountName)
| summarize AccessCount = count(), FirstAccess = min(Timestamp), LastAccess = max(Timestamp) by FileName, FolderPath, User
| sort by AccessCount desc
Investigación de posibles escalados de privilegios o acciones administrativas no autorizadas
Identifique a los usuarios que iniciaron sesión correctamente y realizaron operaciones confidenciales, como "agregar entidad de servicio" o "administración de certificados y secretos" hace entre 90 y 180 días. Esta consulta vincula eventos de inicio de sesión individuales con los registros de auditoría correspondientes para proporcionar visibilidad detallada de cada acción. Los resultados incluyen la identidad del usuario, la dirección IP y las aplicaciones a las que se accede, lo que permite una investigación pormenorizada del comportamiento potencialmente peligroso.
AuditLogs
| where TimeGenerated between(ago(180d)..ago(90d))
| where OperationName has_any ("Add service principal", "Certificates and secrets management")
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project AuditTime = TimeGenerated, Actor, OperationName
| join kind=inner (
SigninLogs
| where ResultType == 0 and TimeGenerated between(ago(180d)..ago(90d))
| project LoginTime = TimeGenerated, Identity, IPAddress, AppDisplayName
) on $left.Actor == $right.Identity
| project AuditTime, Actor, OperationName, LoginTime, IPAddress, AppDisplayName
| sort by Actor asc, LoginTime desc
Investigación del ataque por fuerza bruta lenta
Detecte direcciones IP con un gran número de intentos de inicio de sesión con errores y códigos de error específicos procedentes de varios usuarios únicos.
let relevantErrorCodes = dynamic([50053, 50126, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
SigninLogs
| where TimeGenerated >= ago(180d)
| where ResultType in (relevantErrorCodes)
| extend OS = tostring(parse_json(DeviceDetail).operatingSystem)
| project TimeGenerated, IPAddress, Location, OS, UserPrincipalName, ResultType, ResultDescription
| summarize FailedAttempts = count(), UniqueUsers = dcount(UserPrincipalName) by IPAddress, Location, OS
| where FailedAttempts > 5 and UniqueUsers > 5
| order by FailedAttempts desc
Consultas de ejemplo para trabajos de KQL
Las siguientes consultas se pueden usar en trabajos de KQL para automatizar las investigaciones y las tareas de supervisión en el lago de datos Microsoft Sentinel.
Investigación de incidentes de ataque por fuerza bruta
Enriquezca los registros de inicio de sesión con registros de red para la investigación de incidentes de ataque por fuerza bruta.
// Attacker IPs from signin failures (enriched with domains)
let relevantErrorCodes = dynamic([50053, 50126, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
let attackerSigninData = SigninLogs
| where ResultType in (relevantErrorCodes)
| summarize FailedAttempts = count(), Domains = make_set(UserPrincipalName, 50) by IPAddress
| where FailedAttempts > 5;
// Extract firewall logs where src or dst IP matches attacker IPs
let matchedFirewall = CommonSecurityLog
| extend
src_ip = SourceIP,
dst_ip = DestinationIP
| extend EventIP = coalesce(src_ip, dst_ip)
| project EventTime = TimeGenerated, EventIP, DeviceName, MessageID = DeviceEventClassID, Message = AdditionalExtensions;
// Join to enrich firewall logs with domain data
matchedFirewall
| join kind=leftouter (attackerSigninData) on $left.EventIP == $right.IPAddress
| project FirewallTime = EventTime, EventIP, DeviceName, MessageID, Message, SigninDomains = tostring(Domains)
| order by FirewallTime desc
Actividad histórica que implica direcciones IP de inteligencia sobre amenazas
Descubra la actividad histórica de red que implica direcciones IP de la inteligencia sobre amenazas, lo que ayuda a realizar un seguimiento de la posible exposición o riesgo que se produjo hace entre 3 y 6 meses.
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let dt_start = ago(180d);
let dt_end = ago(90d);
let ioc_lookBack = 180d;
let IP_Indicators = ThreatIntelIndicators
| extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
| where IndicatorType in ("ipv4-addr", "ipv6-addr", "network-traffic")
| extend NetworkSourceIP = toupper(ObservableValue)
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| where TimeGenerated >= dt_start
| extend TI_ipEntity = iff(isnotempty(NetworkSourceIP), NetworkSourceIP, NetworkSourceIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| where ipv4_is_private(TI_ipEntity) == false
and TI_ipEntity !startswith "fe80"
and TI_ipEntity !startswith "::"
and TI_ipEntity !startswith "127."
| where IsActive and (ValidUntil > dt_start or isempty(ValidUntil));
IP_Indicators
| project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, Type, TI_ipEntity
| join kind=innerunique (
CommonSecurityLog
| where TimeGenerated between (dt_start .. dt_end)
| extend MessageIP = extract(IPRegex, 0, Message)
| extend CS_ipEntity = iff((not(ipv4_is_private(SourceIP)) and isnotempty(SourceIP)), SourceIP, DestinationIP)
| extend CS_ipEntity = iff(isempty(CS_ipEntity) and isnotempty(MessageIP), MessageIP, CS_ipEntity)
| extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.TI_ipEntity == $right.CS_ipEntity
| where CommonSecurityLog_TimeGenerated < ValidUntil
| project
timestamp = CommonSecurityLog_TimeGenerated,
SourceIP, DestinationIP, MessageIP, Message,
DeviceVendor, DeviceProduct, Id, ValidUntil, Confidence,
TI_ipEntity, CS_ipEntity, LogSeverity, DeviceAction, Type
Actividad de viaje sospechosa
Busque inicios de sesión correctos desde países o regiones que no se hayan visto previamente para un usuario determinado, lo que puede indicar un riesgo de cuenta o una actividad de viaje sospechosa en los últimos 180 días.
SigninLogs
| where TimeGenerated >= ago(180d)
| where ResultType == 0
| summarize CountriesAccessed = make_set(Location) by UserPrincipalName
| where array_length(CountriesAccessed) > 3 // Adjust threshold
Línea base de inicio de sesión diario
Cree una línea base diaria de todos los usuarios y sus ubicaciones de inicio de sesión.
SigninLogs
| where ResultType == 0
| where TimeGenerated between (ago(180d)..ago(1d)) // Historical window excluding today
| summarize HistoricalCountries = make_set(Location) by UserPrincipalName
| join kind=inner (
SigninLogs
| where ResultType == 0
| where TimeGenerated between (startofday(ago(0d))..now()) // Today’s sign-ins
| summarize TodayCountries = make_set(Location) by UserPrincipalName
) on UserPrincipalName
| extend NewLocations = set_difference(TodayCountries, HistoricalCountries)
| project UserPrincipalName, HistoricalCountries, TodayCountries, NewLocations
| where array_length(NewLocations) > 0
Tendencia de ubicación diaria por usuario y aplicación
Un trabajo diario para resumir la actividad de inicio de sesión por parte del usuario y la aplicación, que muestra la lista y el recuento de ubicaciones geográficas e direcciones IP distintas usadas en las últimas 24 horas.
SigninLogs
| where TimeGenerated > ago(1d)
| extend locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/",
tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize LocationList = make_set(locationString), LocationCount=dcount(locationString),
DistinctSourceIp = dcount(IPAddress), LogonCount = count() by Day, AppDisplayName, UserPrincipalName
Tendencia diaria de ejecución de procesos
Un trabajo diario para realizar un seguimiento de los eventos de creación de procesos (id. de evento 4688) de SecurityEvents, que resume los recuentos por nombre del proceso junto con el número de equipos, cuentas, procesos primarios y líneas de comandos únicos distintos observados en las últimas 24 horas.
// Frequency - Daily - Maintain 30 day or 60 Day History.
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID==4688
| extend Day = format_datetime(TimeGenerated, "yyyy-MM-dd")
| summarize Count= count(), DistinctComputers = dcount(Computer), DistinctAccounts = dcount(Account),
DistinctParent = dcount(ParentProcessName), NoofCommandLines = dcount(CommandLine) by Day, NewProcessName