Jul 29 2021
11:04 PM
- last edited on
Apr 08 2022
10:52 AM
by
TechCommunityAP
Jul 29 2021
11:04 PM
- last edited on
Apr 08 2022
10:52 AM
by
TechCommunityAP
I want to display Defender coverage in custom application like following image::
Please help?
Jul 30 2021 08:21 AM
Solution
There is a query from the "Inventory Blade" in ASC, you can amend it (I made a start below on a recent project). This is using ARG rather than the REST api directly. You should be able to see the api info if you need to use that.
securityresources
//| where subscriptionId == "< insert you id here >"
| where type =~ "microsoft.security/assessments" or type =~ "microsoft.security/softwareInventories"
| extend assessmentStatusCode = case(type =~ "microsoft.security/assessments", tostring(properties.status.code), "")
| extend severity = case(assessmentStatusCode =~ "unhealthy", tolower(tostring(properties.metadata.severity)), tolower(assessmentStatusCode))
| extend exemptionType = case(tolower(type) != "microsoft.security/assessments","N/A", case(properties.status.cause =~ "exempt", "Yes", "No"))
| extend source = case(type =~ "microsoft.security/assessments", tostring(properties.resourceDetails.Source), "")
| extend resourceId = trim(" ", tolower(tostring(case(source =~ "azure", properties.resourceDetails.Id,
source =~ "aws", properties.resourceDetails.AzureResourceId,
source =~ "gcp", properties.resourceDetails.AzureResourceId,
type =~ "microsoft.security/assessments", extract("^(.+)/providers/Microsoft.Security/assessments/.+$",1,id),extract("^(.+)/providers/Microsoft.Security/softwareInventories/.+$",1,id)))))
| extend resourceName = extract(@"(.+)/(.+)", 2, resourceId)
| extend regexResourceId = extract_all(@"/providers/([^/]+)(?:/([^/]+)/[^/]+(?:/([^/]+)/[^/]+)?)?/([^/]+)/[^/]+$", resourceId)
| extend RegexResourceType = regexResourceId[0]
| extend mainType = RegexResourceType[1], extendedType = RegexResourceType[2], resourceType = RegexResourceType[3]
| extend providerName = RegexResourceType[0],
mainType = case(mainType !~ "", strcat("/",mainType), ""),
extendedType = case(extendedType!~ "", strcat("/",extendedType), ""),
resourceType = case(resourceType!~ "", strcat("/",resourceType), "")
| extend array = split(resourceId, '/')
| extend typeFullPath = case(array_length(array) == 3, 'subscription', strcat(providerName, mainType, extendedType, resourceType))
| extend typeFullPath = case(array_length(array) == 5, 'resourcegroups', typeFullPath)
| extend resourceType = case(typeFullPath =~ 'resourcegroups' or typeFullPath =~ 'subscription', typeFullPath, tolower(trim("/", resourceType)))
| extend assessmentKey = case(type =~ "microsoft.security/assessments", tostring(name), "")
| extend softwareVendorName = case(type =~ "microsoft.security/softwareInventories", tostring(properties.vendor), "")
| extend softwareName = case(type =~ "microsoft.security/softwareInventories", tostring(properties.softwareName), "")
| extend softwareNameIdentifier = case(type =~ "microsoft.security/softwareInventories", strcat(softwareVendorName, ",", softwareName), "")
| extend environment = case(type =~ "microsoft.security/assessments", properties.resourceDetails["Source"], "")
| extend environment = case(environment =~ "onpremise", tolower("Non-Azure"), tolower(environment))
| extend osTypeProperty = properties.additionalData["OS Type"]
| extend osType = case(isnotempty(osTypeProperty), osTypeProperty, "")
| extend hasAgent = case(assessmentKey == "d1db3318-01ff-16de-29eb-28b344515626" or assessmentKey == "45cfe080-ceb1-a91e-9743-71551ed24e94" or assessmentKey == "720a3e77-0b9a-4fa9-98b6-ddf0fd7e32c1" or assessmentKey == "27ac71b1-75c5-41c2-adc2-858f5db45b08", assessmentStatusCode, "")
| extend workspaceAzureResourceId = case(hasAgent !~ "", properties.additionalData["Reporting workspace azure id"], "")
| extend workspaceName = case(workspaceAzureResourceId !~ "", extract(@"(.+)/(.+)", 2, workspaceAzureResourceId), "")
| extend assessmentDisplayName = case(type =~ "microsoft.security/assessments", case(isnotempty(properties.displayName), properties.displayName, properties.metadata.displayName), "")
| extend assessmentIdentifier = case(type =~ "microsoft.security/assessments", strcat(assessmentKey, "," , assessmentDisplayName, ",", severity), "")
| summarize assessmentsCount = count() , assessmentsIdentifier = make_list(assessmentIdentifier), softwareNamesIdentifier = make_list(softwareNameIdentifier), hasAgent = max(hasAgent), workspaceName = max(workspaceName), environment = max(environment), osType = max(osType), exemptionType = max(exemptionType) by resourceId, subscriptionId, resourceName, resourceType, typeFullPath, severity
| extend packAssessments = pack(severity, assessmentsCount)
| summarize assessmentsSummary = make_bag(packAssessments), assessmentsIdentifier = make_set(assessmentsIdentifier), softwareNamesIdentifier = make_set(softwareNamesIdentifier), hasAgent = max(hasAgent), workspaceName= max(workspaceName), environment = max(environment), osType= max(osType), exemptionType = max(exemptionType) by resourceId, subscriptionId, resourceName, resourceType, typeFullPath
| extend agentMonitoring = case(hasAgent =~ "NotApplicable" or hasAgent =~ "", '',
hasAgent =~ "Unhealthy", "notInstalled",
"installed")
| join kind=leftouter (
securityresources
| where type =~ "microsoft.security/pricings"
| project subscriptionId, bundleName = tolower(name), freeTrialRemainingTime = properties.freeTrialRemainingTime, pricingTier = tolower(properties.pricingTier)
| extend bundlesPricing = pack(bundleName, pricingTier)
| summarize subscriptionPricing = make_bag(bundlesPricing) by subscriptionId
) on subscriptionId
| extend hasNoSoftwareData = case(array_length(softwareNamesIdentifier) == 1, case(set_has_element(softwareNamesIdentifier, ""), true, false), false)
| extend softwareNamesIdentifier = case(hasNoSoftwareData, softwareNamesIdentifier, set_difference(softwareNamesIdentifier, pack_array("")))
| extend AssessmentsHigh = case(isnull(assessmentsSummary.high), 0 , toint(assessmentsSummary.high))
| extend AssessmentsMedium = case(isnull(assessmentsSummary.medium), 0 , toint(assessmentsSummary.medium))
| extend AssessmentsLow = case(isnull(assessmentsSummary.low), 0 , toint(assessmentsSummary.low))
| extend unhealthyAssessmentsCount = AssessmentsHigh + AssessmentsMedium + AssessmentsLow
| extend virtualmachines = case(isnull(subscriptionPricing), '' , subscriptionPricing.virtualmachines)
| extend virtualmachines = case(virtualmachines == 'free', 'off', 'on')
| extend sqlservers = case(isnull(subscriptionPricing), '' , subscriptionPricing.sqlservers)
| extend sqlservers = case(sqlservers == 'free', 'off', 'on')
| extend kubernetesservice = case(isnull(subscriptionPricing), '' , subscriptionPricing.kubernetesservice)
| extend kubernetesservice = case(kubernetesservice == 'free', 'off', 'on')
| extend containerregistry = case(isnull(subscriptionPricing), '' , subscriptionPricing.containerregistry)
| extend containerregistry = case(containerregistry == 'free', 'off', 'on')
| extend connectedcontainerregistry = case(isnull(subscriptionPricing), '' , subscriptionPricing.connectedcontainerregistry)
| extend connectedcontainerregistry = case(connectedcontainerregistry == 'free', 'off', 'on')
| extend sqlservervirtualmachines = case(isnull(subscriptionPricing), '' , subscriptionPricing.sqlservervirtualmachines)
| extend sqlservervirtualmachines = case(sqlservervirtualmachines == 'free', 'off', 'on')
| extend appservices = case(isnull(subscriptionPricing), '' , subscriptionPricing.appservices)
| extend appservices = case(appservices == 'free', 'off', 'on')
| extend storageaccounts = case(isnull(subscriptionPricing), '' , subscriptionPricing.storageaccounts)
| extend storageaccounts = case(storageaccounts == 'free', 'off', 'on')
| extend keyvaults = case(isnull(subscriptionPricing), '' , subscriptionPricing.keyvaults)
| extend keyvaults = case(keyvaults == 'free', 'off', 'on')
| extend opensourcerelationaldatabases = case(isnull(subscriptionPricing), '' , subscriptionPricing.opensourcerelationaldatabases)
| extend opensourcerelationaldatabases = case(opensourcerelationaldatabases == 'free', 'off', 'on')
| extend calculatedSubscriptionPricing = case(resourceType =~ "subscription" and isempty(subscriptionPricing) == false , iff(subscriptionPricing has "free" and subscriptionPricing has "standard", "partial", iff(subscriptionPricing has "free", "off", "on")), "")
| extend resourcePricing = case(typeFullPath =~ "microsoft.classiccompute/virtualmachines", virtualmachines, typeFullPath =~ "microsoft.compute/virtualmachines", virtualmachines, typeFullPath =~ "microsoft.hybridcompute/machines", virtualmachines, typeFullPath =~ "microsoft.sql/servers", sqlservers, typeFullPath =~ "microsoft.containerservice/managedclusters", kubernetesservice, typeFullPath =~ "microsoft.kubernetes/connectedclusters", kubernetesservice, typeFullPath =~ "microsoft.containerregistry/registries", containerregistry, typeFullPath =~ "microsoft.security/connectedcontainerregistries", connectedcontainerregistry, typeFullPath =~ "microsoft.sqlvirtualmachine/sqlvirtualmachines", sqlservervirtualmachines, typeFullPath =~ "microsoft.web/sites", appservices, typeFullPath =~ "microsoft.storage/storageaccounts", storageaccounts, typeFullPath =~ "microsoft.compute/virtualmachinescalesets", virtualmachines, typeFullPath =~ "microsoft.keyvault/vaults", keyvaults, typeFullPath =~ "microsoft.dbforpostgresql/servers", opensourcerelationaldatabases, typeFullPath =~ "microsoft.dbformysql/servers", opensourcerelationaldatabases, typeFullPath =~ "microsoft.dbformariadb/servers", opensourcerelationaldatabases, calculatedSubscriptionPricing)
| extend pricing = case(resourceType =~ "subscription" , calculatedSubscriptionPricing , resourcePricing)
| project resourceType, exemptionType, typeFullPath, resourceId, resourceName, subscriptionId, environment, osType, workspaceName, agentMonitoring, assessmentsIdentifier, assessmentsSummary, subscriptionPricing, unhealthyAssessmentsCount, pricing, softwareNamesIdentifier
| extend resourceGroup = tolower(tostring(split(resourceId, "/")[4]))
| order by unhealthyAssessmentsCount, subscriptionId, resourceType, resourceId
| where isnotempty(resourceId)
| extend resourceType = iff(resourceType == 'servers','SQL Server',resourceType)
| extend resourceType = iff(resourceType == 'machines','Hybrid Server',resourceType)
| summarize on_=countif(pricing == "on"), off_=countif(isempty(pricing)), part_=countif(pricing == "partial") by resourceType | order by on_ desc
Aug 04 2021 04:37 AM - edited Aug 04 2021 04:39 AM
Aug 04 2021 08:53 AM
Aug 05 2021 03:34 AM
Jul 30 2021 08:21 AM
Solution
There is a query from the "Inventory Blade" in ASC, you can amend it (I made a start below on a recent project). This is using ARG rather than the REST api directly. You should be able to see the api info if you need to use that.
securityresources
//| where subscriptionId == "< insert you id here >"
| where type =~ "microsoft.security/assessments" or type =~ "microsoft.security/softwareInventories"
| extend assessmentStatusCode = case(type =~ "microsoft.security/assessments", tostring(properties.status.code), "")
| extend severity = case(assessmentStatusCode =~ "unhealthy", tolower(tostring(properties.metadata.severity)), tolower(assessmentStatusCode))
| extend exemptionType = case(tolower(type) != "microsoft.security/assessments","N/A", case(properties.status.cause =~ "exempt", "Yes", "No"))
| extend source = case(type =~ "microsoft.security/assessments", tostring(properties.resourceDetails.Source), "")
| extend resourceId = trim(" ", tolower(tostring(case(source =~ "azure", properties.resourceDetails.Id,
source =~ "aws", properties.resourceDetails.AzureResourceId,
source =~ "gcp", properties.resourceDetails.AzureResourceId,
type =~ "microsoft.security/assessments", extract("^(.+)/providers/Microsoft.Security/assessments/.+$",1,id),extract("^(.+)/providers/Microsoft.Security/softwareInventories/.+$",1,id)))))
| extend resourceName = extract(@"(.+)/(.+)", 2, resourceId)
| extend regexResourceId = extract_all(@"/providers/([^/]+)(?:/([^/]+)/[^/]+(?:/([^/]+)/[^/]+)?)?/([^/]+)/[^/]+$", resourceId)
| extend RegexResourceType = regexResourceId[0]
| extend mainType = RegexResourceType[1], extendedType = RegexResourceType[2], resourceType = RegexResourceType[3]
| extend providerName = RegexResourceType[0],
mainType = case(mainType !~ "", strcat("/",mainType), ""),
extendedType = case(extendedType!~ "", strcat("/",extendedType), ""),
resourceType = case(resourceType!~ "", strcat("/",resourceType), "")
| extend array = split(resourceId, '/')
| extend typeFullPath = case(array_length(array) == 3, 'subscription', strcat(providerName, mainType, extendedType, resourceType))
| extend typeFullPath = case(array_length(array) == 5, 'resourcegroups', typeFullPath)
| extend resourceType = case(typeFullPath =~ 'resourcegroups' or typeFullPath =~ 'subscription', typeFullPath, tolower(trim("/", resourceType)))
| extend assessmentKey = case(type =~ "microsoft.security/assessments", tostring(name), "")
| extend softwareVendorName = case(type =~ "microsoft.security/softwareInventories", tostring(properties.vendor), "")
| extend softwareName = case(type =~ "microsoft.security/softwareInventories", tostring(properties.softwareName), "")
| extend softwareNameIdentifier = case(type =~ "microsoft.security/softwareInventories", strcat(softwareVendorName, ",", softwareName), "")
| extend environment = case(type =~ "microsoft.security/assessments", properties.resourceDetails["Source"], "")
| extend environment = case(environment =~ "onpremise", tolower("Non-Azure"), tolower(environment))
| extend osTypeProperty = properties.additionalData["OS Type"]
| extend osType = case(isnotempty(osTypeProperty), osTypeProperty, "")
| extend hasAgent = case(assessmentKey == "d1db3318-01ff-16de-29eb-28b344515626" or assessmentKey == "45cfe080-ceb1-a91e-9743-71551ed24e94" or assessmentKey == "720a3e77-0b9a-4fa9-98b6-ddf0fd7e32c1" or assessmentKey == "27ac71b1-75c5-41c2-adc2-858f5db45b08", assessmentStatusCode, "")
| extend workspaceAzureResourceId = case(hasAgent !~ "", properties.additionalData["Reporting workspace azure id"], "")
| extend workspaceName = case(workspaceAzureResourceId !~ "", extract(@"(.+)/(.+)", 2, workspaceAzureResourceId), "")
| extend assessmentDisplayName = case(type =~ "microsoft.security/assessments", case(isnotempty(properties.displayName), properties.displayName, properties.metadata.displayName), "")
| extend assessmentIdentifier = case(type =~ "microsoft.security/assessments", strcat(assessmentKey, "," , assessmentDisplayName, ",", severity), "")
| summarize assessmentsCount = count() , assessmentsIdentifier = make_list(assessmentIdentifier), softwareNamesIdentifier = make_list(softwareNameIdentifier), hasAgent = max(hasAgent), workspaceName = max(workspaceName), environment = max(environment), osType = max(osType), exemptionType = max(exemptionType) by resourceId, subscriptionId, resourceName, resourceType, typeFullPath, severity
| extend packAssessments = pack(severity, assessmentsCount)
| summarize assessmentsSummary = make_bag(packAssessments), assessmentsIdentifier = make_set(assessmentsIdentifier), softwareNamesIdentifier = make_set(softwareNamesIdentifier), hasAgent = max(hasAgent), workspaceName= max(workspaceName), environment = max(environment), osType= max(osType), exemptionType = max(exemptionType) by resourceId, subscriptionId, resourceName, resourceType, typeFullPath
| extend agentMonitoring = case(hasAgent =~ "NotApplicable" or hasAgent =~ "", '',
hasAgent =~ "Unhealthy", "notInstalled",
"installed")
| join kind=leftouter (
securityresources
| where type =~ "microsoft.security/pricings"
| project subscriptionId, bundleName = tolower(name), freeTrialRemainingTime = properties.freeTrialRemainingTime, pricingTier = tolower(properties.pricingTier)
| extend bundlesPricing = pack(bundleName, pricingTier)
| summarize subscriptionPricing = make_bag(bundlesPricing) by subscriptionId
) on subscriptionId
| extend hasNoSoftwareData = case(array_length(softwareNamesIdentifier) == 1, case(set_has_element(softwareNamesIdentifier, ""), true, false), false)
| extend softwareNamesIdentifier = case(hasNoSoftwareData, softwareNamesIdentifier, set_difference(softwareNamesIdentifier, pack_array("")))
| extend AssessmentsHigh = case(isnull(assessmentsSummary.high), 0 , toint(assessmentsSummary.high))
| extend AssessmentsMedium = case(isnull(assessmentsSummary.medium), 0 , toint(assessmentsSummary.medium))
| extend AssessmentsLow = case(isnull(assessmentsSummary.low), 0 , toint(assessmentsSummary.low))
| extend unhealthyAssessmentsCount = AssessmentsHigh + AssessmentsMedium + AssessmentsLow
| extend virtualmachines = case(isnull(subscriptionPricing), '' , subscriptionPricing.virtualmachines)
| extend virtualmachines = case(virtualmachines == 'free', 'off', 'on')
| extend sqlservers = case(isnull(subscriptionPricing), '' , subscriptionPricing.sqlservers)
| extend sqlservers = case(sqlservers == 'free', 'off', 'on')
| extend kubernetesservice = case(isnull(subscriptionPricing), '' , subscriptionPricing.kubernetesservice)
| extend kubernetesservice = case(kubernetesservice == 'free', 'off', 'on')
| extend containerregistry = case(isnull(subscriptionPricing), '' , subscriptionPricing.containerregistry)
| extend containerregistry = case(containerregistry == 'free', 'off', 'on')
| extend connectedcontainerregistry = case(isnull(subscriptionPricing), '' , subscriptionPricing.connectedcontainerregistry)
| extend connectedcontainerregistry = case(connectedcontainerregistry == 'free', 'off', 'on')
| extend sqlservervirtualmachines = case(isnull(subscriptionPricing), '' , subscriptionPricing.sqlservervirtualmachines)
| extend sqlservervirtualmachines = case(sqlservervirtualmachines == 'free', 'off', 'on')
| extend appservices = case(isnull(subscriptionPricing), '' , subscriptionPricing.appservices)
| extend appservices = case(appservices == 'free', 'off', 'on')
| extend storageaccounts = case(isnull(subscriptionPricing), '' , subscriptionPricing.storageaccounts)
| extend storageaccounts = case(storageaccounts == 'free', 'off', 'on')
| extend keyvaults = case(isnull(subscriptionPricing), '' , subscriptionPricing.keyvaults)
| extend keyvaults = case(keyvaults == 'free', 'off', 'on')
| extend opensourcerelationaldatabases = case(isnull(subscriptionPricing), '' , subscriptionPricing.opensourcerelationaldatabases)
| extend opensourcerelationaldatabases = case(opensourcerelationaldatabases == 'free', 'off', 'on')
| extend calculatedSubscriptionPricing = case(resourceType =~ "subscription" and isempty(subscriptionPricing) == false , iff(subscriptionPricing has "free" and subscriptionPricing has "standard", "partial", iff(subscriptionPricing has "free", "off", "on")), "")
| extend resourcePricing = case(typeFullPath =~ "microsoft.classiccompute/virtualmachines", virtualmachines, typeFullPath =~ "microsoft.compute/virtualmachines", virtualmachines, typeFullPath =~ "microsoft.hybridcompute/machines", virtualmachines, typeFullPath =~ "microsoft.sql/servers", sqlservers, typeFullPath =~ "microsoft.containerservice/managedclusters", kubernetesservice, typeFullPath =~ "microsoft.kubernetes/connectedclusters", kubernetesservice, typeFullPath =~ "microsoft.containerregistry/registries", containerregistry, typeFullPath =~ "microsoft.security/connectedcontainerregistries", connectedcontainerregistry, typeFullPath =~ "microsoft.sqlvirtualmachine/sqlvirtualmachines", sqlservervirtualmachines, typeFullPath =~ "microsoft.web/sites", appservices, typeFullPath =~ "microsoft.storage/storageaccounts", storageaccounts, typeFullPath =~ "microsoft.compute/virtualmachinescalesets", virtualmachines, typeFullPath =~ "microsoft.keyvault/vaults", keyvaults, typeFullPath =~ "microsoft.dbforpostgresql/servers", opensourcerelationaldatabases, typeFullPath =~ "microsoft.dbformysql/servers", opensourcerelationaldatabases, typeFullPath =~ "microsoft.dbformariadb/servers", opensourcerelationaldatabases, calculatedSubscriptionPricing)
| extend pricing = case(resourceType =~ "subscription" , calculatedSubscriptionPricing , resourcePricing)
| project resourceType, exemptionType, typeFullPath, resourceId, resourceName, subscriptionId, environment, osType, workspaceName, agentMonitoring, assessmentsIdentifier, assessmentsSummary, subscriptionPricing, unhealthyAssessmentsCount, pricing, softwareNamesIdentifier
| extend resourceGroup = tolower(tostring(split(resourceId, "/")[4]))
| order by unhealthyAssessmentsCount, subscriptionId, resourceType, resourceId
| where isnotempty(resourceId)
| extend resourceType = iff(resourceType == 'servers','SQL Server',resourceType)
| extend resourceType = iff(resourceType == 'machines','Hybrid Server',resourceType)
| summarize on_=countif(pricing == "on"), off_=countif(isempty(pricing)), part_=countif(pricing == "partial") by resourceType | order by on_ desc