Microsoft Sentinel Data Lake の KQL クエリのサンプル

この記事では、対話型または 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

場所の変更に基づく異常なサインイン動作

カテゴリ: 異常

Entra 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 サブスクリプション レベルのイベントを識別します。 たとえば、操作名 "Create or Update Snapshot" に基づく監視。バックアップの作成に使用されますが、攻撃者がハッシュをダンプしたり、ディスクから機密情報を抽出したりするために悪用される可能性があります。

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 ごとの毎日のネットワーク トラフィックの傾向

カテゴリ: 脅威アクティビティ

今日の接続とバイトは、ホストの 1 日のベースラインに対して評価され、観察された動作が確立されたパターンから大幅に逸脱しているかどうかを判断します。

// 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 の種類 (つまり、ブラウザー、Office アプリケーションなど) のベースラインを確立するには、日数を振り返ります。 次に、現在の日付で、このパターンからの逸脱 (つまり、このアプリケーションと組み合わせて前に見られない 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 の照合

カテゴリ: 脅威アクティビティ

CommonSecurityLog で一致を検索して、脅威インテリジェンス (TI) から侵害の IP インジケーター (IOC) を特定します。

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 Network トラフィック ログからビーコン パターンを識別します。 このクエリでは、さまざまな KQL 関数を使用して時間の差分を計算し、それを 1 日に観察されたイベントの合計と比較して、ビーコンの割合を見つけます。

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 秘密度ラベル (Confidential、高機密、制限付きなど) と関連付けることで、エンドポイント上の機密性の高いドキュメント ファイルへの履歴アクセスを検出します。 このクエリを使用して、元の 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

ユーザーとアプリケーションごとの毎日の場所の傾向

ユーザーとアプリケーション別のサインイン アクティビティを集計する 1 日のジョブ。過去 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

毎日のプロセス実行の傾向

SecurityEventsからのプロセス作成イベント (イベント ID 4688) を追跡する毎日のジョブ。プロセス名別の数と、過去 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