AzureActivity | summarize LastActivity = max(TimeGenerated) by ResourceProvider, ResourceGroup | join kind = innerunique( AzureActivity | summarize Operations = count() by ResourceGroup, ResourceProvider) on ResourceGroup, ResourceProvider |project ResourceProvider, ResourceGroup, Operations, LastActivity |sort by Operations The above KQL is used to print 4 columnsI need to print the fifth column as well that highlights the percentage of operations per Resource Group and Resource provider. There have to 5 columns in the result Resource Provider, Resource Group,Number of Operations (Activities), Last activity time, Percentage Can someone help me with this?

Hello uditk14I will stress this is just a sample, its not very optimized and there is probably a better way to do this (I just cant think of one currently - so I need to take a break from it, to help me think!) // source idea: https://techcommunity.microsoft.com/t5/azure-sentinel/approximate-partial-and-combined-lookups-in-azure-sentinel/ba-p/1393795 // get lookup data let geoData = externaldata (network:string,geoname_id:string,continent_code:string, continent_name:string,country_iso_code:string, country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool) [@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"] with(format="csv", ignoreFirstRecord=true); // now turn remote data to scalar let lookup = toscalar( geoData | summarize list_CIDR=make_set(network) ); // link to Azure Activity and specifically CallerIpAddress AzureActivity // get a small time range (this REALLY helps perf!!!!) | where TimeGenerated > ago(2h) | mv-apply list_CIDR=lookup to typeof(string) on ( // Match each IP from 'CallerIpAddress' with the remote 'network' column where ipv4_is_match (CallerIpAddress, list_CIDR) //== false ) // summarize to remove any duplicates | summarize by CallerIpAddress, list_CIDR | join kind=inner ( // join to remote data again, to add enrichments geoData ) on $left.list_CIDR == $right.network // build final display | summarize by CallerIpAddress, network, country_name, country_iso_code

Are you bringing in TI feeds? https://docs.microsoft.com/en-us/azure/sentinel/whats-new#enriched-threat-intelligence-with-geolocation-and-whois-data-public-preview These are now enriched with geo location and whois.Below this there is a REST api https://docs.microsoft.com/en-us/azure/sentinel/geolocation-data-api

SocInABox This Workbook I quickly created will demo the REST api, provide the geo details and map it for you Source: KQLpublic/geoLocation.workbook at master · clivewatson/KQLpublic (github.com)Demo

SocInABox I just realised the original query was before we had ipv4_lookup(), so does this change improve things (its less code at least)? let IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool) ['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv']; let IPs = CommonSecurityLog |where DeviceVendor == "Fortinet" //filter out private networks |where not(ipv4_is_private(SourceIP)) and not(ipv4_is_private(DestinationIP)) |summarize by SourceIP ; IPs | evaluate ipv4_lookup(IP_Data, SourceIP, network, return_unmatched = true)

uditk14 there may be a better solution, but this approach should work: let TotalOperations = todouble(toscalar(AzureActivity | summarize count())); AzureActivity | summarize LastActivity = max(TimeGenerated), Operations = count() by ResourceProvider, ResourceGroup | extend Percentage = round(todouble(Operations) / TotalOperations * 100, 1) | project ResourceProvider, ResourceGroup, Operations, Percentage, LastActivity | sort by Operations

KQL question | Microsoft Community Hub

16 Replies

hspinto
Microsoft
Jun 22, 2020
uditk14

there may be a better solution, but this approach should work:

let TotalOperations = todouble(toscalar(AzureActivity | summarize count()));

AzureActivity

| summarize LastActivity = max(TimeGenerated), Operations = count() by ResourceProvider, ResourceGroup

| extend Percentage = round(todouble(Operations) / TotalOperations * 100, 1)

| project ResourceProvider, ResourceGroup, Operations, Percentage, LastActivity

| sort by Operations
- uditk14
  Copper Contributor
  Jun 25, 2020
  hspinto - Thanks a lot
  
  I have one more query with the exteraldata operator
  I have used externaldata operator to fetch data from a CSV having a few columns namely, IP ranges, country code, country name, continent name etc.
  In Azure Activity table there is a CallerIP value.
  I need to print the location for each caller Ip.
  
  CSV file - https://datahub.io/core/geoip2-ipv4#premium-data-2
  
  hspinto Can you help me with the KQL
  - hspinto
    Microsoft
    Jun 26, 2020
    uditk14,
    
    something like this would respond to your needs. However, due to a restriction of user-defined functions, you cannot call functions sending parameters that depend on row-context.
    
    let GeoData = externaldata (network:string,geoname_id:string,continent_code:string,continent_name:string,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool) [ @"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv" ] with(format="csv", ignoreFirstRecord=true); let GetCountryName = (CallerIp:string) { toscalar( GeoData | extend AddressMask = split(network,'/')[1] | where ipv4_compare(CallerIp, tostring(split(network,'/')[0]), toint(tostring(split(network,'/')[1]))) == 0 | project country_name ) }; //this works, because the parameter is hardcoded //print GetCountryName('94.45.78.16') // this will fail with a "Unresolved reference binding" error AzureActivity | extend CountryName = GetCountryName(CallerIpAddress)
    
    Deleted, do you have a solution for this one?

Forum Discussion

KQL question

16 Replies

Resources