Secure Score Review Dashboard

Dataimport

Ladda review-underlag

CSV 1 och CSV 2 krävs. CSV 3 är valfri.

CSV 1 – Secure Score

CSV 2 – Förändringar

CSV 3 – Unhealthy duration

Manual

Hämta CSV-filer från Azure Portal

Följ stegen nedan för att exportera de tre CSV-filerna som dashboarden använder.

Öppna rätt Log Analytics workspace

Öppna Azure Portal och gå till Log Analytics workspaces. Välj workspace logs-security och öppna sedan Logs.

Kör KQL-frågorna

Kopiera en KQL-fråga nedan, uppdatera SubscriptionMap med subscriptions som ska ingå och klicka på Run.

Ändra Show results till 5,000

Innan export: klicka på Show results ovanför resultatet och välj 5,000. Det minskar risken att exporten saknar rader.

Exportera CSV

Klicka på Share och välj Export to CSV (all columns). Spara filerna med tydliga namn, till exempel KQL1.csv, KQL2.csv och KQL3.csv.

Ladda filerna i Dataimport

Gå tillbaka till fliken Dataimport och ladda CSV 1, CSV 2 och valfritt CSV 3. CSV 3 används för Unhealthy duration.

Viktigt: Använd alltid Show results: 5,000 innan CSV-export. Om en fråga har fler än 5,000 rader behöver den begränsas eller filtreras i KQL för att inte missa data.

KQL 1 – Secure Score per subscription

Exportera som KQL1.csv.

let SubscriptionMap =
    datatable(
        SubscriptionId:string,
        SubscriptionName:string,
        Environment:string
    )
    [
        "YOUR-SUBSCRIPTION-ID", "YOUR-SUBSCRIPTION-NAME", "Prod"
    ]
    | extend SubscriptionId = tolower(SubscriptionId);

let CurrentMonthStart = startofmonth(now());
let PreviousMonthStart = startofmonth(now(), -1);

let ScoreHistory = materialize(
    SecureScores
    | where TimeGenerated >= PreviousMonthStart
    | where DisplayName == "ASC score"
    | project
        TimeGenerated,
        SubscriptionId = tolower(tostring(SecureScoresSubscriptionId)),
        PercentageScore = todouble(PercentageScore)
    | where SubscriptionId in (SubscriptionMap | project SubscriptionId)
);

let Current =
    ScoreHistory
    | where TimeGenerated >= CurrentMonthStart
    | summarize arg_max(TimeGenerated, PercentageScore) by SubscriptionId
    | project
        SubscriptionId,
        CurrentScorePercent = round(PercentageScore * 100.0, 2),
        CurrentTime = TimeGenerated;

let Previous =
    ScoreHistory
    | where TimeGenerated >= PreviousMonthStart
    | where TimeGenerated < CurrentMonthStart
    | summarize arg_max(TimeGenerated, PercentageScore) by SubscriptionId
    | project
        SubscriptionId,
        PreviousScorePercent = round(PercentageScore * 100.0, 2),
        PreviousTime = TimeGenerated;

SubscriptionMap
| join kind=leftouter Current on SubscriptionId
| join kind=leftouter Previous on SubscriptionId
| extend ScoreChangePercentagePoints =
    round(CurrentScorePercent - PreviousScorePercent, 2)
| extend Finding = case(
    isnull(CurrentScorePercent), "No current data",
    isnull(PreviousScorePercent), "No previous-month baseline",
    ScoreChangePercentagePoints <= -1, "Regression",
    ScoreChangePercentagePoints >= 1, "Improvement",
    "No material change"
)
| project
    SubscriptionName,
    Environment,
    SubscriptionId,
    CurrentScorePercent,
    PreviousScorePercent,
    ScoreChangePercentagePoints,
    Finding,
    CurrentTime,
    PreviousTime
| order by ScoreChangePercentagePoints asc

KQL 2 – Recommendation changes

Exportera som KQL2.csv. Den inkluderar både statusändringar och kvarstående Unhealthy-rekommendationer.

let SubscriptionMap =
    datatable(
        SubscriptionId:string,
        SubscriptionName:string,
        Environment:string
    )
    [
        "YOUR-SUBSCRIPTION-ID", "YOUR-SUBSCRIPTION-NAME", "Prod"
    ]
    | extend SubscriptionId = tolower(SubscriptionId);

let CurrentMonthStart = startofmonth(now());
let PreviousMonthStart = startofmonth(now(), -1);

let RecommendationHistory = materialize(
    SecurityRecommendation
    | where TimeGenerated >= PreviousMonthStart
    | extend SubscriptionId = tolower(tostring(split(AssessedResourceId, "/")[2]))
    | where SubscriptionId in (SubscriptionMap | project SubscriptionId)
    | project
        TimeGenerated,
        SubscriptionId,
        RecommendationId,
        RecommendationName,
        RecommendationState,
        RecommendationSeverity,
        AssessedResourceId
);

let Current =
    RecommendationHistory
    | where TimeGenerated >= CurrentMonthStart
    | summarize arg_max(
        TimeGenerated,
        RecommendationName,
        RecommendationState,
        RecommendationSeverity
    ) by SubscriptionId, RecommendationId, AssessedResourceId
    | project
        SubscriptionId,
        RecommendationId,
        RecommendationName,
        AssessedResourceId,
        CurrentState = RecommendationState,
        CurrentSeverity = RecommendationSeverity,
        CurrentTime = TimeGenerated;

let Previous =
    RecommendationHistory
    | where TimeGenerated < CurrentMonthStart
    | summarize arg_max(
        TimeGenerated,
        RecommendationState,
        RecommendationSeverity
    ) by SubscriptionId, RecommendationId, AssessedResourceId
    | project
        SubscriptionId,
        RecommendationId,
        AssessedResourceId,
        PreviousState = RecommendationState,
        PreviousSeverity = RecommendationSeverity,
        PreviousTime = TimeGenerated;

Current
| join kind=leftouter Previous on SubscriptionId, RecommendationId, AssessedResourceId
| extend PreviousStateDisplay =
    iff(isempty(PreviousState), "Not observed last month", PreviousState)
| extend StatusChange = strcat(PreviousStateDisplay, " -> ", CurrentState)
| where CurrentState != PreviousStateDisplay
    or CurrentState =~ "Unhealthy"
| join kind=inner (
    SubscriptionMap
    | project SubscriptionId, SubscriptionName, Environment
) on SubscriptionId
| extend SeverityRank = case(
    CurrentSeverity =~ "Critical", 1,
    CurrentSeverity =~ "High", 2,
    CurrentSeverity =~ "Medium", 3,
    CurrentSeverity =~ "Low", 4,
    5
)
| order by SeverityRank asc, SubscriptionName asc, RecommendationName asc
| project
    SubscriptionName,
    Environment,
    SubscriptionId,
    RecommendationName,
    RecommendationId,
    StatusChange,
    CurrentSeverity,
    PreviousSeverity,
    AssessedResourceId,
    CurrentTime,
    PreviousTime

KQL 3 – Unhealthy duration

Valfri. Exportera som KQL3.csv för Unhealthy dagar och första observerade datum.

let SubscriptionMap =
    datatable(
        SubscriptionId:string,
        SubscriptionName:string,
        Environment:string
    )
    [
        "YOUR-SUBSCRIPTION-ID", "YOUR-SUBSCRIPTION-NAME", "Prod"
    ]
    | extend SubscriptionId = tolower(SubscriptionId);

let CurrentStateLookback = 31d;

let CurrentUnhealthy =
    materialize(
        SecurityRecommendation
        | where TimeGenerated >= ago(CurrentStateLookback)
        | extend SubscriptionId = tolower(tostring(split(AssessedResourceId, "/")[2]))
        | where SubscriptionId in (SubscriptionMap | project SubscriptionId)
        | project
            TimeGenerated,
            SubscriptionId,
            RecommendationId,
            AssessedResourceId,
            RecommendationState
        | summarize arg_max(TimeGenerated, RecommendationState)
            by SubscriptionId, RecommendationId, AssessedResourceId
        | where RecommendationState =~ "Unhealthy"
        | project
            SubscriptionId,
            RecommendationId,
            AssessedResourceId,
            CurrentTime = TimeGenerated
    );

let RelevantHistory =
    materialize(
        CurrentUnhealthy
        | join kind=inner (
            SecurityRecommendation
            | extend SubscriptionId = tolower(tostring(split(AssessedResourceId, "/")[2]))
            | where SubscriptionId in (SubscriptionMap | project SubscriptionId)
            | project
                TimeGenerated,
                SubscriptionId,
                RecommendationId,
                RecommendationName,
                RecommendationState,
                AssessedResourceId
        ) on SubscriptionId, RecommendationId, AssessedResourceId
        | project
            SubscriptionId,
            RecommendationId,
            RecommendationName,
            RecommendationState,
            AssessedResourceId,
            CurrentTime,
            TimeGenerated
    );

let LastNonUnhealthyBeforeCurrent =
    RelevantHistory
    | where TimeGenerated < CurrentTime
    | where RecommendationState !~ "Unhealthy"
    | summarize LastNonUnhealthyTime = max(TimeGenerated)
        by SubscriptionId, RecommendationId, AssessedResourceId;

RelevantHistory
| join kind=leftouter LastNonUnhealthyBeforeCurrent
    on SubscriptionId, RecommendationId, AssessedResourceId
| where isempty(LastNonUnhealthyTime) or TimeGenerated > LastNonUnhealthyTime
| where RecommendationState =~ "Unhealthy"
| summarize
    FirstUnhealthyInCurrentPeriod = min(TimeGenerated),
    LastUnhealthy = max(TimeGenerated),
    Samples = count(),
    RecommendationName = any(RecommendationName)
    by SubscriptionId, RecommendationId, AssessedResourceId
| extend UnhealthyDays =
    datetime_diff("day", LastUnhealthy, FirstUnhealthyInCurrentPeriod)
| join kind=inner (
    SubscriptionMap
    | project SubscriptionId, SubscriptionName, Environment
) on SubscriptionId
| project
    SubscriptionName,
    Environment,
    SubscriptionId,
    RecommendationName,
    RecommendationId,
    FirstUnhealthyInCurrentPeriod,
    LastUnhealthy,
    UnhealthyDays,
    Samples,
    AssessedResourceId
| order by UnhealthyDays desc, SubscriptionName asc

Subscriptions i scope

–

Efter filter

Regressioner

–

Finding = Regression

Genomsnittlig score

–

CurrentScorePercent

Visade risker

–

Efter riskfilter

Sök subscription

Subscription overview

Subscription	Environment	Current	Previous	Change	Finding

Recommendation-filter Påverkar endast detaljer för vald subscription.

Visa

Exkludera rekommendationer som börjar med Update

Sök recommendation eller subscription

Recommendation-filter Påverkar endast Gemensamma rekommendationer.

Visa

Exkludera rekommendationer som börjar med Update

Gemensamma rekommendationer

Recommendation	Severity	Subscriptions	Ändringar	Miljöer

Update-miljö

Subscriptions

Update-typ

Update-status

Sortera updates

Update-vyn är helt fristående från de övriga filtren. Den använder bara sina egna filter ovan och visar rekommendationer vars namn börjar med Update. Äldsta Unhealthy dagar är högsta värdet bland berörda enheter för samma update.

Sök update, subscription eller enhet

Updates som saknas

Update	Typ	Enheter	Subscriptions	Äldsta Unhealthy	Äldsta första Unhealthy

Dataimport

Ladda review-underlag

Exempel på vad du kan få fram

Varför fick en subscription regression?

Vilka risker finns kvar?

Hur stort är ett gemensamt problem?

Är det en gammal eller ny patch?

Kunden säger att en server inte har patchats

Hämta CSV-filer från Azure Portal

Öppna rätt Log Analytics workspace

Kör KQL-frågorna

Ändra Show results till 5,000

Exportera CSV

Ladda filerna i Dataimport

KQL 1 – Secure Score per subscription

KQL 2 – Recommendation changes

KQL 3 – Unhealthy duration