TI Map Query Generator

Query

,
            "TableGroupByColumn":
                ', NetworkMessageId'
            ,
            "TableColumns&LookUp":
                'Workload, AccountUpn, IPAddress, ActionType, IsClickedThrough, OriginalUrl, UrlChain, NetworkMessageId'
            ,
            "TableCustomEntityExtend":
                ```,
                AccountCustomEntity = AccountUpn,
                //IPCustomEntity = IPAddress```
            ,
            "TableExclusion":
                ``````
        })
        ,
        'Syslog',
        dynamic({
            "TableQueryWait":
                '0h'
            ,
            "TableAdditionalLets":
                ``````
            ,
            "TableName":
                'Syslog'
            ,
            "TableTimeColumn":
                'TimeGenerated'
            ,
            "PreTableOperators":
                ``````
            ,
            "PostTableOperators":
                ``````
            ,
            "TableGroupByColumn":
                ', HostName'
            ,
            "TableColumns&LookUp":
                'HostName, HostIP, Facility, SeverityLevel, ProcessName, SyslogMessage'
            ,
            "TableCustomEntityExtend":
                ```,
                HostCustomEntity = HostName,
                //IPCustomEntity = HostIP```
            ,
            "TableExclusion":
                ``````
        })
        ,
        'StorageFileLogs',
        dynamic({
            "TableQueryWait":
                '0h'
            ,
            "TableAdditionalLets":
                ``````
            ,
            "TableName":
                'StorageFileLogs'
            ,
            "TableTimeColumn":
                'ingestion_time()'
            ,
            "PreTableOperators":
                ``````
            ,
            "PostTableOperators":
                ``````
            ,
            "TableGroupByColumn":
                ', AccountName'
            ,
            "TableColumns&LookUp":
                '_ResourceId, Category, AccountName, CallerIpAddress, IPAddress, OperationName, LastModifiedTime, AuthenticationType, AuthenticationHash, Protocol, StatusCode, StatusText, ReferrerHeader, Uri, ConditionsUsed, UserAgentHeader, CorrelationId'
            ,
            "TableCustomEntityExtend":
                ``````
            ,
            "TableExclusion":
                ``````
        })
        ,
        'StorageBlobLogs',
        dynamic({
            "TableQueryWait":
                '0h'
            ,
            "TableAdditionalLets":
                ``````
            ,
            "TableName":
                'StorageBlobLogs'
            ,
            "TableTimeColumn":
                'ingestion_time()'
            ,
            "PreTableOperators":
                ``````
            ,
            "PostTableOperators":
                ``````
            ,
            "TableGroupByColumn":
                ', AccountName'
            ,
            "TableColumns&LookUp":
                '_ResourceId, Category, AccountName, CallerIpAddress, IPAddress, OperationName, LastModifiedTime, AuthenticationType, AuthenticationHash, Protocol, StatusCode, StatusText, ReferrerHeader, Uri, ConditionsUsed, UserAgentHeader, CorrelationId'
            ,
            "TableCustomEntityExtend":
                ``````
            ,
            "TableExclusion":
                ``````
        })
        ,
        'AADManagedIdentitySignInLogs',
        dynamic({
            "TableQueryWait":
                '0h'
            ,
            "TableAdditionalLets":
                ``````
            ,
            "TableName":
                'AADManagedIdentitySignInLogs'
            ,
            "TableTimeColumn":
                'TimeGenerated'
            ,
            "PreTableOperators":
                ``````
            ,
            "PostTableOperators":
                ``````
            ,
            "TableGroupByColumn":
                ', ServicePrincipalName'
            ,
            "TableColumns&LookUp":
                'Category, ServicePrincipalName, IPAddress, Location, ResultType, ResultDescription, AuthenticationProcessingDetails, AppId, ResourceDisplayName, ServicePrincipalId, ServicePrincipalCredentialKeyId, ServicePrincipalCredentialThumbprint, CorrelationId'
            ,
            "TableCustomEntityExtend":
                ```,
                AccountCustomEntity = ServicePrincipalName```
            ,
            "TableExclusion":
                ``````
        })
        ,
        'AADServicePrincipalSignInLogs',
        dynamic({
            "TableQueryWait":
                '0h'
            ,
            "TableAdditionalLets":
                ``````
            ,
            "TableName":
                'AADServicePrincipalSignInLogs'
            ,
            "TableTimeColumn":
                'TimeGenerated'
            ,
            "PreTableOperators":
                ``````
            ,
            "PostTableOperators":
                ``````
            ,
            "TableGroupByColumn":
                ', ServicePrincipalName'
            ,
            "TableColumns&LookUp":
                'Category, ServicePrincipalName, IPAddress, Location, ResultType, ResultDescription, AuthenticationProcessingDetails, AppId, ResourceDisplayName, ServicePrincipalId, ServicePrincipalCredentialKeyId, ServicePrincipalCredentialThumbprint, CorrelationId'
            ,
            "TableCustomEntityExtend":
                ```,
                AccountCustomEntity = ServicePrincipalName,
                //IPCustomEntity = IPAddress```
            ,
            "TableExclusion":
                ``````
        })
        ,
        'ADFSSignInLogs',
        dynamic({
            "TableQueryWait":
                '0h'
            ,
            "TableAdditionalLets":

About this query

Explanation

This query defines a function called TIMapQueryGenerator that helps create consistent analytics rules for matching threat intelligence indicators with various data tables in Azure Sentinel. Here's a simplified breakdown:

Purpose: The function is designed to streamline the process of writing analytics rules by centralizing the logic for matching different types of threat indicators (like URLs, IPs, file hashes, etc.) with corresponding data tables (like VMConnection, Syslog, SecurityAlert, etc.).
Indicator and Table Definitions:
- The function starts by defining a set of threat indicator types and their associated properties.
- It then defines a set of data tables and their associated properties.
Matching Logic:
- For each combination of indicator type and data table, the function specifies how to match the indicators with the data in the table.
- This includes defining lookback periods, filtering conditions, and how to handle specific fields in the data.
Query Generation:
- The function generates a query for each combination of indicator type and data table.
- These queries are designed to find matches between threat indicators and data table entries, taking into account various conditions and exclusions.
Usage:
- Once the function is defined, it can be called to generate the necessary queries for all combinations of indicators and tables.
- This ensures consistency and makes it easier to update the logic in one place if needed.

In essence, TIMapQueryGenerator is a powerful tool for automating and standardizing the creation of threat intelligence analytics rules in Azure Sentinel.

Details

Jose Sebastián Canós

Released: April 1, 2024

Tables

_VMConnectionUrlClickEventsSyslogStorageFileLogsStorageBlobLogsAADManagedIdentitySignInLogsAADServicePrincipalSignInLogsADFSSignInLogsAADNonInteractiveUserSignInLogsSigninLogsSecurityEventSecurityAlertPowerPlatformConnectorActivityPowerAppsActivityOfficeActivityEmailUrlInfoEmailEventsEmailAttachmentInfoDnsEventsDeviceNetworkEventsDeviceFileEventsCisco_Umbrella_dns_CLAzureActivityAzureDiagnosticsAKSAuditAKSAuditAdminAZKVAuditLogsAZFWApplicationRuleAZFWIdpsSignatureAZFWNetworkRuleAZFWThreatIntelAWSCloudTrailAuditLogsAppServiceIPSecAuditLogsAppServiceAuditLogs

Keywords

ThreatIntelligenceIndicatorsThreatIntelligenceIndicatorsURLIPFileHashEmailDomainVMConnectionUrlClickEventsSyslogStorageFileLogsStorageBlobLogsAADManagedIdentitySignInLogsAADServicePrincipalSignInLogsADFSSignInLogsAADNonInteractiveUserSignInLogsSigninLogsSecurityEventSecurityAlertPowerPlatformConnectorActivityPowerAppsActivityOfficeActivityEmailUrlInfoEmailEventsEmailAttachmentInfoDnsEventsDeviceNetworkEventsDeviceFileEventsCiscoUmbrellaAzureActivityAzureSQLKubernetesLegacyKeyVaultFirewallDataLakeAWSCloudTrailAuditLogsAppServiceIPSecAuditLogsAppServiceAuditLogs

Operators

letdatatabledynamicstringprojectextendwheresummarizecountmake_set_ifisnotemptyarray_lengthtoscalarjoinkindleftantileftouteronbag_mergemv-expandtolowertoupperiffcasesplitarray_slicearray_concattrim_endtrim_startparseproject-renameproject-awayhint.strategyshufflearg_maxminmaxbetweenagonownotmaterializeunionisfuzzytake_anyreplace_stringstrcattostringtodynamicdynamic_to_jsonextract_allextractpack_arraymake_listmake_list_ifmake_bagmake_bag_ifparse_ipv4parse_ipv6ipv4_is_privateipv4_is_in_any_rangematchesregexreplace_regexhashas_anyhas_allendswithstartswithcoalescecolumn_ifexistsbag_keystodatetime

Actions

GitHub

KQL Search