Exemplo de consultas KQL para data lake do Microsoft Sentinel

Este artigo fornece consultas KQL de exemplo que você pode usar interativamente ou em trabalhos KQL para investigar incidentes de segurança e monitorar atividades suspeitas no data lake do Microsoft Sentinel.

Consultas sobre uso imediato

O Microsoft Sentinel inclui um conjunto de consultas KQL prontas para uso que você pode usar para explorar e analisar dados no data lake. Essas consultas estão disponíveis no editor de consultas KQL na guia Consultas . Para obter mais informações, consulte Executar consultas KQL.

As localizações de login anômalas aumentam

Categoria: Atividades de ameaça

Analise as tendências dos logs de autenticação do Entra ID para detectar alterações de localização incomuns para usuários entre diferentes aplicativos, computando linhas de tendência de diversidade de localização. Ele destaca as três principais contas com o aumento mais acentuado na variabilidade de localização e lista seus locais associados em janelas de 21 dias.

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

Comportamento de entrada anômalo com base em alterações de localização

Categoria: Anomalias

Identifique o comportamento anômalo de autenticação com base em alterações de localização para usuários e aplicativos do Entra ID, visando detectar mudanças repentinas no comportamento.

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

Audite atividades raras por aplicativo

Categoria: Atividades de ameaça

Encontre aplicativos executando ações raras (por exemplo, consentimento, concessões) que podem criar privilégios silenciosamente. Compare o dia atual com os últimos 14 dias de auditorias para identificar novas atividades de auditoria. Útil para acompanhar atividades mal-intencionadas relacionadas a adições ou remoções de usuário/grupo por aplicativos do Azure e aprovações 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

Operações raras no nível da assinatura do Azure

Categoria: Atividades de ameaça

Identifique eventos confidenciais no nível da assinatura do Azure com base nos Logs de Atividades do Azure. Por exemplo, o monitoramento baseado no nome da operação "Criar ou Atualizar Instantâneo", que é usado para criar backups, mas pode ser usado indevidamente por invasores para despejar hashes ou extrair informações confidenciais do 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])

Tendência de atividade diária por aplicativo em AuditLogs

Categoria: Linhas de base

Nos últimos 14 dias, identifique qualquer operação de "Consentimento para aplicativo" ocorre por um usuário ou aplicativo. Isso pode indicar que as permissões para acessar o AzureApp listado foram fornecidas a um ator mal-intencionado. O consentimento para o aplicativo, a adição da entidade de serviço e os eventos de Auth2PermissionGrant devem ser raros. Se disponível, o contexto extra é adicionado a partir dos registros de auditoria com base no CorrelationId da mesma conta que realizou a "Consentimento para o aplicativo".

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)

Tendência diária de localização por usuário ou aplicativo em SignInLogs

Categoria: Linha de Base

Crie tendências diárias para todas as entradas do usuário, contagem de locais e uso do aplicativo.

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

Tendência diária de tráfego de rede por IP de destino

Categoria: Linha de Base

Crie uma linha de base incluindo bytes e pares distintos para detectar sinalização e exfiltração.

// 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

Tendência diária de tráfego de rede por IP de destino com estatísticas de transferência de dados

Categoria: Atividades de ameaça

Identificar o host interno que contatou o destino externo, incluindo tendências de volume e estimativa do raio de impacto.

// 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

Tendência diária de tráfego de rede por IP de origem

Categoria: Linha de base

Crie uma linha de base incluindo bytes e pares distintos para detectar sinalização e exfiltração.

// 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

Tendência diária de tráfego de rede por IP de origem com estatísticas de transferência de dados

Categoria: Atividades de ameaça

As conexões e os bytes de hoje são avaliados em comparação com a linha de base diária do host para determinar se os comportamentos observados se desviam significativamente do padrão estabelecido.

// 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

Tendência diária de localização de login por usuário e aplicativo

Categoria: Linha de Base

Crie uma linha de base de login para cada usuário ou aplicativo com informações geográficas e de IP típicas, permitindo a detecção de anomalias de forma eficiente e econômica em 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

Tendência de execução diária do processo

Categoria: Linha de Base

Identifique novos processos e prevalências, facilitando as detecções de "novo processo raro".

// 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

Agente de usuário raro do Entra ID por aplicativo

Categoria: Detecção de anomalias

Estabeleça uma linha de base do tipo de UserAgent (ou seja, navegador, aplicativo de escritório, etc.) que normalmente é usado para um aplicativo específico, analisando um histórico de vários dias. Em seguida, o sistema pesquisa no dia atual quaisquer desvios desse padrão, ou seja, tipos de UserAgents não vistos anteriormente em combinação com este aplicativo.

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

Correspondência de IOC do log de rede

Categoria: Atividades de ameaça

Identifique quaisquer indicadores de comprometimento (IOCs) de IP provenientes de inteligência de ameaças (TI), pesquisando correspondências no 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

Novos processos observados nas últimas 24 horas

Categoria: Atividades de ameaça

Novos processos em ambientes estáveis podem indicar atividade mal-intencionada. Analisar sessões de entrada em que esses binários foram executados pode ajudar 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

Operação de arquivo do SharePoint por meio de IPs não vistos anteriormente

Categoria: Atividades de ameaça

Identifique anomalias usando o comportamento do usuário definindo um limite para alterações significativas nas atividades de upload/download de arquivos de novos endereços IP. Ele estabelece uma linha de base de comportamento típico, compara-a com a atividade recente e sinaliza desvios que excedem um limite padrão 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])

Baliza de rede potencial do Palo Alto

Categoria: Atividades de ameaça

Identificar padrões de sinalizadores a partir de logs de tráfego da Palo Alto Networks com base em padrões recorrentes de variação de tempo. A consulta utiliza diversas funções KQL para calcular as diferenças de tempo e, em seguida, compara-as com o total de eventos observados em um dia para encontrar a porcentagem de sinalização.

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

Logon suspeito do Windows fora do horário normal

Categoria: Detecção de anomalias

Identifique eventos de entrada incomuns do Windows fora do horário normal de um usuário comparando-se com a atividade de entrada dos últimos 14 dias, sinalizando anomalias com base em padrões 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 exemplo adicionais

As consultas de exemplo a seguir podem ser usadas para explorar e analisar dados no data lake do Microsoft Sentinel.

Identificar possíveis ameaças internas

Detecte o acesso histórico a arquivos de documentos sensíveis em endpoints correlacionando a atividade de arquivo com o rótulo de confidencialidade do Microsoft Purview, por exemplo, Confidencial, Altamente Confidencial ou Restrito. Use essa consulta para descobrir sinais de exfiltração de dados, violações de política ou comportamento suspeito do usuário que pode ter passado despercebido durante a janela de tempo original de 90 a 180 dias.

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

Investigar possível escalonamento de privilégios ou ações administrativas não autorizadas

Identifique os usuários que entraram com êxito e realizaram operações confidenciais, como "adicionar entidade de serviço" ou "gerenciamento de certificados e segredos" entre 90 e 180 dias atrás. Essa consulta vincula eventos de entrada individuais com logs de auditoria correspondentes para fornecer visibilidade detalhada de cada ação. Os resultados incluem a identidade do usuário, o endereço IP e os aplicativos acessados, permitindo uma investigação granular de comportamento potencialmente arriscado.

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

Investigar ataque de força bruta lento

Detecte endereços IP com um alto número de tentativas de entrada com falha e códigos de erro específicos provenientes de vários usuários exclusivos.

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 exemplo para trabalhos KQL

As consultas a seguir podem ser usadas em trabalhos KQL para automatizar investigações e tarefas de monitoramento no data lake do Microsoft Sentinel.

Investigação de incidentes de ataque de força bruta

Enriqueça os logs de autenticação com logs de rede para investigação de incidentes de ataque de força 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

Atividade histórica envolvendo endereços IP da inteligência de ameaças

Descubra a atividade de rede histórica envolvendo endereços IP da inteligência de ameaças, que ajuda a rastrear possíveis exposições ou comprometimentos que ocorreram há 3 a 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

Atividade de viagem suspeita

Procure logins bem-sucedidos de países ou regiões nunca vistos para um determinado usuário, o que pode sinalizar comprometimento da conta ou movimentação de viagem suspeita nos últimos 180 dias.

SigninLogs
| where TimeGenerated >= ago(180d)
| where ResultType == 0
| summarize CountriesAccessed = make_set(Location) by UserPrincipalName
| where array_length(CountriesAccessed) > 3  // Adjust threshold

Linha de base de login diário

Crie uma linha de base diária de todos os usuários e seus locais de login.

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

Tendência diária de localização por usuário e aplicativo

Um trabalho diário para resumir a atividade de entrada por usuário e aplicativo, mostrando a lista e a contagem de locais geográficos distintos e IPs usados nas ú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

Tendência de execução diária do processo

Um trabalho diário para acompanhar os eventos de criação de processo (ID do Evento 4688) do SecurityEvents, resumindo as contagens por nome do processo, juntamente com o número de computadores distintos, contas, processos pai e linhas de comando únicas observadas nas ú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