MS Graph Security - Explore

This query allows you to view the detection details that have been received from the MS Graph Connector.

The primary table we are exploring is mdr_ms_graph_api_data.

This query takes two variables allowing to to set a filter by category and severity (HIGH, MEDIUM, LOW)

The results will display ALL cols available in the table.

-- MS Graph Security explorer

-- VARIABLE STRING $$Category$$
-- VARIABLE STRING $$Severity$$

SELECT 
   id,
   event_date_time,
   severity,
   CASE severity WHEN 'HIGH' THEN 5 WHEN 'MEDIUM' THEN 3 ELSE 1 END Sophos_Risk_Score,
   category, 
   status,
   title,
   description,
   processes,
   recommended_actions,
   incident_ids,
   malware_states,
   host_states,
   user_states,
   network_connections,
   registry_key_states,
   vulnerability_states,
   cloud_app_states,	
   additional_fields,
   security_resources,
   source_materials,

   '--------------' "Additional_fields-->",
   activity_group_name,
   assigned_to	azure_subscription_id,
   azure_tenant_id,
--   category,
   closed_date_time,
--   cloud_app_states,	
   comments,
   confidence,
   created_date_time,
--   description,
   detection_ids,
--   event_date_time,
--   feedback	file_states,
--   host_states,
--   id,
   incident_ids,
   last_modified_date_time,
--   malware_states,
--   network_connections,
--   processes,
--   recommended_actions,
--   registry_key_states,
--   security_resources,
--   severity,
--   source_materials,
--   status,
   tags,
--   title,
   triggers,
--   user_states,
   vendor_information,
--   vulnerability_states,
   message_identifier,
   customer_id,
   ingestion_timestamp,
   sensor_id,
   sensor,
   sensor_version,
   schema_version,
   record_size,
--   additional_fields,
   customer_bucket,
   ingest_date
FROM mdr_ms_graph_api_data
WHERE LOWER(category) LIKE LOWER('%$$Category$$%') AND
   LOWER(severity) LIKE LOWER('%$$Severity$$%')
ORDER BY category ASC, Sophos_Risk_Score DESC, event_date_time DESC
--LIMIT 10