# Fetch the current tenant ID
$tenantId = (az account show --query tenantId -o tsv)
 
# Initialize variables
$MDC_Recommendations = @()
$pageSize = 1000
$iteration = 0
$skipCount = 0
 
# Define the Resource Graph query in a readable multiline format
$queryLines = @(
    "SecurityResources",
    "| where type == 'microsoft.security/assessments'",
    "| where properties.status.code == 'Unhealthy'",
    "| mvexpand Category = properties.metadata.categories",
    "| extend",
    "    AssessmentId = id,",
    "    AssessmentKey = name,",
    "    ResourceId = properties.resourceDetails.Id,",
    "    ResourceIdsplit = split(properties.resourceDetails.Id, '/'),",
    "    RecommendationName = properties.displayName,",
    "    RecommendationState = properties.status.code,",
    "    ActionDescription = properties.metadata.description,",
    "    RemediationDescription = properties.metadata.remediationDescription,",
    "    RecommendationSeverity = properties.metadata.severity,",
    "    PolicyDefinitionId = properties.metadata.policyDefinitionId,",
    "    AssessmentType = properties.metadata.assessmentType,",
    "    Threats = properties.metadata.threats,",
    "    UserImpact = properties.metadata.userImpact,",
    "    AzPortalLink = tostring(properties.links.azurePortal)",
    "| extend",
    "    ResourceSubId = tostring(ResourceIdsplit[2]),",
    "    ResourceGroupName = tostring(ResourceIdsplit[4]),",
    "    ResourceType = tostring(ResourceIdsplit[6]),",
    "    ResourceName = tostring(ResourceIdsplit[8])",
    "| join kind=leftouter (resourcecontainers",
    "    | where type == 'microsoft.resources/subscriptions'",
    "    | project SubscriptionName = name, subscriptionId) on subscriptionId",
    "| project SubscriptionName, ResourceGroupName, ResourceName, Category, RecommendationSeverity, RecommendationName, ActionDescription, RemediationDescription, AzPortalLink"
)
 
# Convert the query into a single line
$query = $queryLines -join " "
 
# Pagination logic
do {
    $iteration += 1
    Write-Verbose "Iteration #$iteration" -Verbose
 
    # Fetch a page of results
    $pageResultsJson = az graph query -q $query --first $pageSize --skip $skipCount --output json
    $pageResults = $pageResultsJson | ConvertFrom-Json
 
    # Extract data field and add to recommendations
    if ($pageResults.data) {
        $MDC_Recommendations += $pageResults.data
    }
 
    # Update skip count
    $skipCount += $pageResults.data.Count
} while ($pageResults.data.Count -eq $pageSize)
 
# Update AzPortalLink to include the tenantId in the URL
$MDC_Recommendations = $MDC_Recommendations | ForEach-Object {
    if ($_.AzPortalLink -match "portal.azure.com") {
        $_.AzPortalLink = $_.AzPortalLink -replace "portal.azure.com", "https://portal.azure.com/$tenantId"
    }
    $_
}
 
# Reorder and select columns explicitly
$OrderedRecommendations = $MDC_Recommendations | Select-Object `
    SubscriptionName, `
    ResourceGroupName, `
    ResourceName, `
    Category, `
    RecommendationSeverity, `
    RecommendationName, `
    ActionDescription, `
    RemediationDescription, `
    AzPortalLink
 
# Output summary
Write-Output "Total recommendations fetched: $($OrderedRecommendations.Count)"
 
# Export results to a CSV
$CsvPath = "UnhealthyRecommendations.csv"
$OrderedRecommendations | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8
Write-Output "Recommendations exported to $CsvPath."
 
. ./$CsvPath