이 문서에서는 대화형 또는 KQL 작업에서 보안 인시던트를 조사하고 Microsoft Sentinel 데이터 레이크에서 의심스러운 활동을 모니터링하는 데 사용할 수 있는 샘플 KQL 쿼리를 제공합니다.
즉시 사용 가능한 쿼리
Microsoft Sentinel에는 데이터 레이크에서 데이터를 탐색하고 분석하는 데 사용할 수 있는 기본 KQL 쿼리 집합이 포함되어 있습니다. 이러한 쿼리는 쿼리 탭 아래의 KQL 쿼리 편집기에서 사용할 수 있습니다 . 자세한 내용은 KQL 쿼리 실행을 참조하세요.
비정상적인 로그인 위치 증가
범주: 위협 활동
Entra ID 로그인 로그의 추세 분석을 분석하여 위치 다양성의 추세 줄을 계산하여 애플리케이션 전체의 사용자에게 비정상적인 위치 변경을 검색합니다. 위치 가변성이 가장 가파르게 증가하는 상위 3개 계정을 강조 표시하고 21일 기간 내에 연결된 위치를 나열합니다.
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
위치 변경에 따른 비정상적인 로그인 동작
범주: 변칙
엔트라 ID 사용자 및 앱의 위치 변경에 따라 비정상적인 로그인 동작을 식별하여 갑작스러운 동작 변경을 감지합니다.
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
앱별 희귀 작업 감사
범주: 위협 활동
조용히 권한을 만들 수 있는 드문 작업(예: 동의, 권한 부여)을 수행하는 앱을 찾습니다. 현재 날짜와 지난 14일간의 감사를 비교하여 새 감사 활동을 식별합니다. Azure Apps 및 자동화된 승인에 의한 사용자/그룹 추가 또는 제거와 관련된 악의적인 활동을 추적하는 데 유용합니다.
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 드문 구독 수준 작업
범주: 위협 활동
Azure 활동 로그를 기반으로 중요한 Azure 구독 수준 이벤트를 식별합니다. 예를 들어 백업을 만드는 데 사용되지만 공격자가 디스크에서 해시를 덤프하거나 중요한 정보를 추출하는 데 오용될 수 있는 작업 이름 "스냅샷 만들기 또는 업데이트"를 기반으로 모니터링합니다.
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])
AuditLogs의 앱별 일일 활동 추세
범주: 기준선
지난 14일 동안 사용자 또는 앱에서 발생하는 "애플리케이션에 대한 동의" 작업을 식별합니다. 이는 나열된 AzureApp에 액세스할 수 있는 권한이 악의적인 행위자에게 제공되었음을 나타낼 수 있습니다. 애플리케이션에 동의하고, 서비스 주체를 추가하고, Auth2PermissionGrant 이벤트를 추가하는 경우는 드물어야 합니다. 사용 가능한 경우 "애플리케이션에 동의"를 수행한 동일한 계정의 CorrleationId를 기반으로 AuditLogs에서 추가 컨텍스트가 추가됩니다.
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)
SignInLogs의 사용자 또는 앱당 일일 위치 추세
범주: 기준선
모든 사용자 로그인, 위치 수 및 앱 사용에 대한 일일 추세를 작성합니다.
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
대상 IP당 일일 네트워크 트래픽 추세
범주: 기준선
바이트 수와 고유 피어를 포함한 기준선을 생성하여 주기적 신호 전송 및 데이터 유출을 감지합니다.
// 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
데이터 전송 통계를 사용하는 대상 IP당 일일 네트워크 트래픽 추세
범주: 위협 활동
외부 대상으로 연결한 내부 호스트를 식별하고, 트래픽 추세를 포함하여 잠재적 영향 범위를 추정합니다.
// 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
원본 IP당 일일 네트워크 트래픽 추세
범주: 기준선
바이트 수와 고유 피어를 포함한 기준선을 생성하여 주기적 신호 전송 및 데이터 유출을 감지합니다.
// 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
데이터 전송 통계를 사용하는 원본 IP당 일일 네트워크 트래픽 추세
범주: 위협 활동
오늘의 연결 및 바이트는 호스트의 일별 기준선에 대해 평가되어 관찰된 동작이 설정된 패턴에서 크게 벗어나는지 여부를 확인합니다.
// 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
사용자 및 앱당 일일 로그인 위치 추세
범주: 기준선
일반적인 지리적 및 IP를 사용하여 각 사용자 또는 애플리케이션에 대한 로그인 기준을 만들어 대규모로 효율적이고 비용 효율적인 변칙 검색을 지원합니다.
// 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
일일 프로세스 실행 추세
범주: 기준선
새로운 프로세스 및 보급을 식별하여 "새로운 드문 프로세스" 검색을 더 쉽게 만듭니다.
// 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 ID 희귀 사용자 에이전트
범주: 이상 탐지
일반적으로 특정 애플리케이션에 사용되는 UserAgent 유형(즉, 브라우저, 사무실 응용 프로그램 등)의 기준을 며칠 동안 되돌아보면 설정합니다. 그런 다음 현재 날짜에서 이 패턴과의 차이를 검색하는데, 이는 이 애플리케이션과 결합해 이전에 본 적이 없는 사용자 에이전트 유형을 말합니다.
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
네트워크 로그 IOC 일치
범주: 위협 활동
위협 인텔리전스(TI)에서 IP 침해 지표(IOC)를 식별하고, 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
지난 24시간 동안 관찰된 새로운 프로세스
범주: 위협 활동
안정적인 환경의 새 프로세스는 악의적인 활동을 나타낼 수 있습니다. 이러한 이진 파일이 실행된 로그인 세션을 분석하면 공격을 식별하는 데 도움이 될 수 있습니다.
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
이전에 보이지 않는 IP를 통한 SharePoint 파일 작업
범주: 위협 활동
새 IP 주소에서 파일 업로드/다운로드 작업의 중요한 변경에 대한 임계값을 설정하여 사용자 동작을 사용하여 변칙을 식별합니다. 일반적인 동작의 기준을 설정하고, 최근 활동과 비교하고, 기본 임계값인 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])
Palo Alto 잠재적 네트워크 비콘
범주: 위협 활동
되풀이 시간 델타 패턴을 기반으로 Palo Alto 네트워크 트래픽 로그에서 비콘 패턴을 식별합니다. 이 쿼리는 다양한 KQL 함수를 사용하여 시간 차이를 계산한 후, 하루 동안 관찰된 전체 이벤트와 비교하여 주기적 신호 전송 비율을 계산합니다.
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
정상적인 시간 외의 Windows 의심스러운 로그인
범주: 이상 탐지
지난 14일간의 로그인 활동과 비교하여 기록 패턴에 따라 변칙에 플래그를 지정하여 사용자의 정상 시간 외에 비정상적인 Windows 로그인 이벤트를 식별합니다.
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
추가 샘플 쿼리
다음 샘플 쿼리를 사용하여 Microsoft Sentinel 데이터 레이크의 데이터를 탐색하고 분석할 수 있습니다.
가능한 내부자 위협 식별
파일 활동과 Microsoft Purview 민감도 레이블(예: 기밀, 극비 또는 제한)을 상호 연결하여 엔드포인트에서 중요한 문서 파일에 대한 기록 액세스를 검색합니다. 이 쿼리를 사용하여 원래 90-180일 기간 동안 눈에 띄지 않았을 수 있는 데이터 반출, 정책 위반 또는 의심스러운 사용자 동작의 징후를 확인할 수 있습니다.
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
잠재적인 권한 상승 또는 권한 없는 상태에서의 관리 작업 조사
90일에서 180일 사이에 "서비스 주체 추가" 또는 "인증서 및 비밀 관리"와 같은 중요한 작업을 성공적으로 로그인하고 수행한 사용자를 식별합니다. 이 쿼리는 개별 로그인 이벤트를 해당 감사 로그와 연결하여 각 작업에 대한 자세한 가시성을 제공합니다. 결과에는 사용자 ID, IP 주소 및 액세스된 애플리케이션이 포함되며 잠재적으로 위험한 동작에 대한 세분화된 조사를 가능하게 합니다.
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
느린 무차별 암호 대입 공격 조사
실패한 로그인 시도 횟수가 많은 IP 주소와 여러 고유 사용자의 특정 오류 코드를 검색합니다.
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
KQL 작업에 대한 샘플 쿼리
다음 쿼리를 KQL 작업에서 사용하여 Microsoft Sentinel 데이터 레이크에서 조사 및 모니터링 작업을 자동화할 수 있습니다.
무차별 암호 대입 공격 인시던트 조사
무차별 암호 대입 공격 인시던트 조사를 위해 네트워크 로그를 사용하여 로그인 로그를 보강합니다.
// 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
위협 인텔리전스에서 수집된 IP 주소와 관련된 역사적 활동
위협 인텔리전스의 IP 주소와 관련된 기록 네트워크 활동을 파악하여 3~6개월 전에 발생한 잠재적 노출 또는 손상 추적을 지원합니다.
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
의심스러운 여행 활동
지난 180일 동안 계정 손상 또는 의심스러운 여행 활동을 알릴 수 있는 특정 사용자에 대해 이전에 볼 수 없었던 국가 또는 지역의 성공적인 로그인을 찾습니다.
SigninLogs
| where TimeGenerated >= ago(180d)
| where ResultType == 0
| summarize CountriesAccessed = make_set(Location) by UserPrincipalName
| where array_length(CountriesAccessed) > 3 // Adjust threshold
일일 로그인 기준
모든 사용자 및 해당 로그인 위치의 일일 기준을 만듭니다.
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
사용자 및 애플리케이션당 일일 위치 추세
사용자 및 애플리케이션별 로그인 활동을 요약하는 일일 작업으로, 지난 24시간 동안 사용된 고유 지리적 위치 및 IP의 목록과 수를 보여 줌
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
일일 프로세스 실행 추세
프로세스 생성 이벤트(이벤트 ID 4688) SecurityEvents를 추적하는 일일 작업으로, 지난 24시간 동안 관찰된 고유 컴퓨터, 계정, 부모 프로세스 및 고유 명령줄의 수와 함께 프로세스 이름으로 개수를 요약합니다.
// 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