Returns a list of all unhealthy resources with their assessment counts

ResourcesHealthyUnhealthy.kql

securityresources
| where type == "microsoft.security/assessments"
| extend assessmentDisplayName = coalesce(properties.displayName, properties.metadata.displayName)
| where properties.status.code == "Unhealthy"
| where assessmentDisplayName startswith "[Publix]"
| extend assessmentStatusCode = tolower(tostring(properties.status.code)), id = tolower(id)
| extend severity = iff(assessmentStatusCode == "unhealthy", tolower(tostring(properties.metadata.severity)), assessmentStatusCode)
| extend exemptionType = iff(properties.status.cause == "Exempt", "Yes", "No")
| extend resourceDetails = properties.resourceDetails, additionalData = properties.additionalData
| extend source = tolower(tostring(resourceDetails.Source))
| extend resourceId = trim(" ", tolower(resourceDetails.Id))
| extend extractedResourceName = extract(@"(.+)/(.+)", 2, resourceId)
| extend resourceName = extractedResourceName
| 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',
                    array_length(array) == 5, 'resourcegroups',
                    strcat(providerName, mainType, extendedType, resourceType))
| extend resourceType = iff(typeFullPath == 'resourcegroups' or typeFullPath == 'subscription', typeFullPath, tolower(trim("/", resourceType)))
| extend assessmentKey = iff(type == "microsoft.security/assessments", tostring(name), "")
| extend assessmentIdentifier = case(type == "microsoft.security/assessments", strcat(assessmentKey, "," , assessmentDisplayName, ",", severity), "")
| extend subscription_0 = case(subscriptionId == "d7f4a4fc-c244-4c39-ba04-d057966fc499", "Dev Security Policy" ,subscriptionId == "99b5743f-82e1-4be5-9505-5e1a021a9d80", "Dev Security Engineering" ,subscriptionId == "08c24d17-e9de-4fc9-86c8-f515f9115775", "Publix Security" ,subscriptionId == "0010ff29-bbdc-45db-b20f-ccffaf186e74", "Tst Transportation" ,subscriptionId == "04b98ef4-7fb5-4c29-8be8-51072f46e7c9", "Stg Digital Services" ,subscriptionId == "05777095-f4e8-485a-ac36-6dcf32f2621e", "Stg Enterprise Customer Management" ,subscriptionId == "08cefb03-d0a7-4fc3-a603-556db1e11db9", "Prd Publix Support" ,subscriptionId == "0bfac841-7e18-4196-99b0-3486675ea1ad", "Prd Enterprise Application Services" ,subscriptionId == "0dae1266-c1d1-45ad-ab01-31a5dab58a08", "TBD Subscription for Use" ,subscriptionId == "1151ce36-c7ab-41b9-8296-b678e160739e", "Tst Sensitive Data Lake" ,subscriptionId == "1210eef4-cc7f-4eb1-b45c-7720af1b779c", "Prd Transportation" ,subscriptionId == "12667fd3-9ff9-4981-acfb-409408023464", "Stg Merchandising Systems" ,subscriptionId == "155d62db-88fa-4c90-9dc6-9891aeb2691a", "Stg Order Fulfilment" ,subscriptionId == "168c96a3-54ec-4441-95ce-d843135a9ed4", "Prd Customer PII Data" ,subscriptionId == "1958116a-d487-4fba-a929-6d2c61e9eb2f", "Dev Idea Spot" ,subscriptionId == "1ac6a1af-b5b9-4605-8eb3-39fafd1940a5", "Dev Corporate Compliance" ,subscriptionId == "1d873a12-ea07-43dc-b6e3-59842f91f82b", "Azure Training" ,subscriptionId == "1f845097-d628-47dc-9eac-49df2ef4b065", "Customer Digital Solutions" ,subscriptionId == "2544f514-0d43-472b-bb77-cdf666e601fd", "Publix Big Data Customer" ,subscriptionId == "25553ddd-d14c-47bc-9087-e190140bcb26", "Dev Cloud Native" ,subscriptionId == "275ace22-cabb-4559-a0a7-25c80c0dd404", "Dev Enterprise Collaboration Services" ,subscriptionId == "27a435eb-c0d5-4786-9ab4-d0354b616373", "Prd Enterprise Cloud Services" ,subscriptionId == "28a1d397-f8a4-4406-a704-880819c9cd7f", "HR Systems" ,subscriptionId == "2a53de6d-11ef-41da-ae9f-ef097b2b0998", "OMNI Channel Systems" ,subscriptionId == "361a6ad3-e3a2-4413-87e7-5046c83c99b4", "Dev Order Fulfillment" ,subscriptionId == "37184e79-01f3-4c52-adb2-c3c05001cc09", "Publix Cloud Services" ,subscriptionId == "3d24efb0-2fa3-42f4-8d04-a4072a42da92", "Dev Client Systems" ,subscriptionId == "3e714f8e-d1ea-478b-bcb8-134e2ddbe522", "Prd Publix Big Data" ,subscriptionId == "4003e801-b43c-4bfc-95bc-cbf1b26f7e84", "Dev Enterprise Cloud Services" ,subscriptionId == "44960c47-90d0-4abc-bdb9-29e62558db11", "Dev Automation" ,subscriptionId == "4617e6c4-2273-400d-a978-807a0bfb2762", "Dev Business Area Reporting" ,subscriptionId == "4a862e13-0986-45c5-8ee6-fcba8e68eee9", "Architecture Enterprise Dev/Test" ,subscriptionId == "53218ba9-8e38-429f-9487-73f117e088eb", "Stg Sensitive Data Lake" ,subscriptionId == "54560543-2ded-47ae-adc9-e94ee8ed610d", "Prd Business Area Reporting" ,subscriptionId == "56856008-d745-4982-aa2f-90ac46166902", "Prd Identity and Access Management" ,subscriptionId == "5a80d956-e288-4166-bf6c-81feb7cb567e", "Prd Enterprise Customer Management" ,subscriptionId == "5e972e6c-d5cb-42f5-b049-4ce166840fc0", "Stg Customer PII Data" ,subscriptionId == "60765744-2bf7-4536-88e9-56a8dd0addb9", "Prd Order Fulfilment" ,subscriptionId == "621fe016-e6b3-45e0-a8f5-dd2a269c0cfd", "Stg Recalls System" ,subscriptionId == "678f2e8e-9f3a-42cf-bb90-facb04bbb77a", "Prd Facilities" ,subscriptionId == "683241db-208b-48dc-820d-a71f63b3d6ef", "Prd Point of Sale" ,subscriptionId == "6f6bf7bc-a469-4793-b0b3-e795a5547191", "Dev Retail Store Systems" ,subscriptionId == "6fe28d9f-dd98-42be-be4c-2a99bfca756b", "Dev ECS Power Platform" ,subscriptionId == "700179c1-3170-4fa5-bc4d-bda62783acad", "Dev Marketing Analytics" ,subscriptionId == "744d9929-30a8-4516-8f22-dfa486f0d8ef", "Prd Remote Access" ,subscriptionId == "750b012f-9c13-469a-ba48-a30d7ebc7965", "Tst Corporate Compliance" ,subscriptionId == "7a6acb54-e0be-4473-b7ad-679826d87bcc", "Publix Big Data" ,subscriptionId == "7ac33120-39a4-400a-aa8d-9715cc79b800", "Dev Publix Support" ,subscriptionId == "7bf17a70-fce5-4995-a75d-a6bfbba769a2", "Merchandising Systems" ,subscriptionId == "844a94b2-4d8d-4350-afd1-18188d980097", "Dev Point of Sale" ,subscriptionId == "855ee462-2c87-4c15-9657-b4da45adaad9", "Prd Payments" ,subscriptionId == "858c6971-e8db-4772-a635-0f0004ecc721", "Prd Enterprise Collaboration Services" ,subscriptionId == "8b8e2106-d133-409e-8707-08c8e5f3e6c2", "Publix Security Regulated" ,subscriptionId == "938fbc5c-dbdd-461e-a0a4-39765cbc13db", "Stg Transportation" ,subscriptionId == "939ee073-8e47-4bc5-bddc-0393dbe00714", "Stg Labor Management" ,subscriptionId == "95419149-78bb-4820-ba25-8c25ceeabdf2", "Stg Payments" ,subscriptionId == "98889921-30f8-4135-8c5e-70cdcd22f0ab", "Dev Merchandising Systems" ,subscriptionId == "9a3e98fd-8075-4e45-ae62-d37397587f0b", "Prd Corporate Compliance" ,subscriptionId == "9b502153-a33e-4546-8ddc-25ca4d7bae3a", "Stg Identity and Access Management" ,subscriptionId == "9e2a4eb1-1a3f-41d0-9547-846ca275054d", "Azure Reservation Shared" ,subscriptionId == "a0d9ca58-b17a-4bca-950e-51d5890951ea", "Prd Merchandising Systems" ,subscriptionId == "a20c68ee-6138-4686-9ed8-1ecfef7acfa0", "Dev Transportation" ,subscriptionId == "a5ee436a-d174-4f6d-af7d-2bcc60d44f60", "Tst Customer PII Data" ,subscriptionId == "a6e9c3a7-2c83-44cd-a7c4-83f05e32966e", "Dev Sensitive Data Lake" ,subscriptionId == "a788636d-3909-4001-b47b-065635bd6ac4", "Dev Data Warehouse" ,subscriptionId == "a833c81b-b8c8-422d-b868-cdaef657e786", "Dev Labor Management" ,subscriptionId == "a89444da-488e-4628-bf59-88368f94f81f", "Stg Facilities" ,subscriptionId == "a99a9431-c94d-4184-8199-758f8ecb515d", "Stg Supply Chain" ,subscriptionId == "a9a63b17-b4bf-44f2-a389-b29694a76a83", "Tst Point of Sale" ,subscriptionId == "aaac8910-fd78-4783-bf45-1b20b6d5e1b3", "Publix Infrastructure" ,subscriptionId == "af09ef6a-6495-459f-8c65-d2064957c5ce", "Dev Payments" ,subscriptionId == "af4d78ce-778c-46c5-a659-4bb5850a3772", "Tst Recalls System" ,subscriptionId == "b145213f-ad95-4fea-ae6e-1fe6e966a237", "Dev Customer PII Data" ,subscriptionId == "b787961a-1cfc-4a1d-b261-e216ce0ea7d8", "Prd Digital Services" ,subscriptionId == "b8f92aab-10bf-4f79-aef4-ad33609f135c", "Prd Sensitive Data Lake" ,subscriptionId == "c022d4f9-fc09-4c0d-84be-1e3e4daf7727", "Dev Enterprise Customer Management" ,subscriptionId == "c15ca23c-5c49-45c5-b7f0-3e85e69cf294", "Dev Recalls System" ,subscriptionId == "c3bd2d5f-147c-4072-b0fd-3cd7c748b53f", "Stg Point of Sale" ,subscriptionId == "cb13c7b7-39c1-4742-b89c-84e5a3a56885", "Dev Enterprise Application Services" ,subscriptionId == "d4af33ba-ee0d-4af0-9d15-de20e3efc3d1", "Tst Publix Big Data" ,subscriptionId == "d8210ef1-8427-4ca6-91ce-e2844e48291e", "Stg Data Warehouse" ,subscriptionId == "da885ddc-c6be-4e5d-b106-235606ffc1fe", "Tst Enterprise Collaboration Services" ,subscriptionId == "da973561-09f4-4c15-8ab4-f93dd5cb4a52", "Dev Facilities" ,subscriptionId == "dd1dcde1-3336-4cac-8057-a3b2929d5a87", "Dev Publix Big Data" ,subscriptionId == "dd25d614-9f17-41b3-967e-234b70fba9e0", "Prd Marketing Analytics" ,subscriptionId == "de518c8d-86c9-41d7-92ec-558219c71f8b", "Dev Supply Chain" ,subscriptionId == "e181a830-98b0-404d-aeda-23d6c227276d", "Dev Publix Infrastructure" ,subscriptionId == "e1a4c369-8288-47d6-8da8-12744c8164bb", "Dev Digital Services" ,subscriptionId == "e21e3cd2-130e-43f6-904c-0535b4a9fed2", "Stg Enterprise Application Services" ,subscriptionId == "e9ca0bf5-a61e-4e9f-9a3d-3321d83ef0fc", "Stg Publix Support" ,subscriptionId == "ea72c74a-fb44-4977-8cbd-470d7ff58a41", "Prd Data Warehouse" ,subscriptionId == "ef0df3bc-70ca-422a-ae0b-2da6b8d9dd05", "Enterprise Application Services" ,subscriptionId == "f10308e8-d8c6-4c63-9771-e71ee8ed512c", "Dev Identity and Access Management" ,subscriptionId == "f58bf892-4676-43a0-9b2a-e445b8a29081", "Prd Supply Chain" ,subscriptionId == "f7c2ee4f-4fe7-419a-aa2e-6fab22d70c8c", "Tst Labor Management" ,subscriptionId == "f8be141b-cfc0-43c7-9bd8-671481d5790f", "Stg Corporate Compliance" ,subscriptionId == "fdceb77b-b74b-46b4-9e96-3e959be20a71", "Stg Publix Big Data" ,subscriptionId == "fe56117b-9e64-4c01-b51b-21e199227321", "Prd Recalls System" , "")
| extend subscriptionDisplayName = case(isnotempty(subscription_0), subscription_0, "")
| extend resourceName = case(resourceType == "subscription", subscriptionDisplayName, resourceName)   
| summarize
                    assessmentsCount = dcount(assessmentKey),
                    notApplicableAssessmentsCount = countif(severity == "notapplicable"),
                    lowSeverityAssessmentsCount = countif(severity == "low"),
                    mediumSeverityAssessmentsCount = countif(severity == "medium"),
                    highSeverityAssessmentsCount = countif(severity == "high"),
                    healthyAssessmentsCount = countif(severity == "healthy"),
                    totalAssessmentsCount = countif(severity!=""),
                    assessmentsIdentifier = make_list(assessmentIdentifier),
                    exemptionType = max(exemptionType)
                        by resourceId, subscriptionId, resourceName, subscriptionDisplayName, resourceType, typeFullPath
| extend assessmentsSummary = pack(
                                                "notapplicable", notApplicableAssessmentsCount,
                                                "low", lowSeverityAssessmentsCount,
                                                "medium", mediumSeverityAssessmentsCount,
                                                "high", highSeverityAssessmentsCount,
                                                "healthy", healthyAssessmentsCount)
| extend unhealthyAssessmentsCount = lowSeverityAssessmentsCount + mediumSeverityAssessmentsCount + highSeverityAssessmentsCount
| project   resourceType,
                            exemptionType,
                            typeFullPath,
                            resourceId,
                            resourceName,
                            subscriptionDisplayName,
                            subscriptionId,
                            assessmentsIdentifier,
                            assessmentsSummary,
                            unhealthyAssessmentsCount,
                            totalAssessmentsCount
| extend resourceGroup = tolower(tostring(split(resourceId, "/")[4]))
// | extend resourceName = case(resourceType == "subscription", subscriptionDisplayName, resourceName)
| order by typeFullPath desc
//| where typeFullPath in ('microsoft.apimanagement/service', 'microsoft.databricks/workspaces', 'microsoft.eventgrid/topics', 'microsoft.eventhub/namespaces', 'microsoft.keyvault/vaults', 'microsoft.servicebus/namespaces', 'microsoft.sql/servers', 'microsoft.sql/servers/databases', 'microsoft.web/sites')