False positive being returned by mssql_managed_instance_vulnerability_assessment_enabled.sql check about steampipe-mod-azure-compliance

[rajlearner17]

@durbin-git thank you for using Steampipe. Good to see this feedback, as you mentioned, Azure changing the response, which may result to change certain queries. We will look into this and fix it soon.

[khushboo9024]

Hi @durbin-git thank you for your feedback. We were able to reproduce the bug, your assumption is correct, Azure has remove the key vulnerabilityAssessmentProperties from response . We have fixed the query for now and also we are checking if the same issue exist for other queries as well.

The returned JSON structure of vulnerability assessments for an instance:

  "vulnerability_assessments": [
   {
    "id": "/subscriptions/d46d7416-f95f-4771-bbb5-529d4c76659c/resourceGroups/nist-test_group/providers/Microsoft.Sql/managedInstances/tets566/vulnerabilityAssessments/Default",
    "name": "Default",
    "recurringScans": {
     "emailSubscriptionAdmins": true,
     "isEnabled": true
    },
    "type": "Microsoft.Sql/managedInstances/vulnerabilityAssessments"
   }
  ]

The SQL query:

  > with vulnerability_assessments as (
  select
    distinct i.id as id
  from
    azure_mssql_managed_instance as i,
    jsonb_array_elements(vulnerability_assessments) a
  where
    a -> 'recurringScans' ->> 'isEnabled' = 'true'
    and a ->> 'name' = 'Default'
)
select
  -- Required Columns
  s.id as resource,
  case
    when a.id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when a.id is not null then s.title || ' vulnerability assessment enabled.'
    else s.title || ' vulnerability assessment disabled.'
  end as reason,
  -- Additional Dimensions
  resource_group,
  sub.display_name as subscription
from
  azure_mssql_managed_instance as s
  left join vulnerability_assessments as a on s.id = a.id,
  azure_subscription as sub
where
  sub.subscription_id = s.subscription_id;

The SQL query result:

[
 {
  "reason": "tets566 vulnerability assessment enabled.",
  "resource": "/subscriptions/xxxx-xxx/resourceGroups/nist-test_group/providers/Microsoft.Sql/managedInstances/tets566",
  "resource_group": "nist-test_group",
  "status": "ok",
  "subscription": "xxxx"
 }
]