Project/Extract from additional data fields

RT-7199 511 Reputation points
2021-01-22T18:09:03.837+00:00

How can I project CVE titles from this. Some have more than 1 as in below example, and some don't have any.
I ultimately want server names and CVE columns either empty or ith Title value

Below is example of what I am running in log analytics to extract CVE

SecurityNestedRecommendation

| where RecommendationSeverity == 'High' or RecommendationSeverity == 'Medium'
| project CVE=AdditionalData.Cve

59718-image.png

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
502 questions
0 comments
Accepted answer
  1. RT-7199 511 Reputation points
    2021-01-24T09:11:29.843+00:00

    This is done.. Below is the complete query that works as intended.

    SecurityNestedRecommendation
    | where RecommendationSeverity == 'High' or RecommendationSeverity == 'Medium'
    | extend CVE = todynamic(AdditionalData.Cve)
    | mv-expand CVE
    | extend Patchable_ = tostring(AdditionalData.Patchable)
    | extend Type_ = tostring(AdditionalData.Type)
    | extend MonthName = case( datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 1, "Jan "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 2, "Feb "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 3, "Mar "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 4, "Apr "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 5, "May "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 6, "Jun "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 7, "Jul "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 8, "Aug "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 9, "Sep "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 10, "Oct "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 11, "Nov "
    , datetime_part("month", todatetime(AdditionalData.PublishedTime)) == 12, "Dec "
    , "Unknown Month"
    )
    | extend PublishedTime = strcat(datetime_part("day", todatetime(AdditionalData.PublishedTime)),"-", MonthName,"-",datetime_part("year", todatetime(AdditionalData.PublishedTime)))
    | extend VMName = extract(@"(.+)/(.+)", 2, AssessedResourceId)
    | extend Base_ = tostring(parse_json(tostring(parse_json(tostring(AdditionalData.Cvss)).["3.0"])).Base)
    | extend Base_ = tostring(parse_json(tostring(parse_json(tostring(AdditionalData.Cvss)).["2.0"])).Base)
    | project VMName, CVE.Title , Severity = RecommendationSeverity, Description, PublishedTime, Patchable = Patchable_, Score = Base_, Type=AdditionalData.Category, Category,RemediationDescription
    | sort by VMName,Severity,Patchable

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. RT-7199 511 Reputation points
    2021-01-22T23:35:41.523+00:00

    Ok so I got it working like this. But the problem is with this tostring(parse_json(tostring(AdditionalData.Cve))[0].Title), I don't know how to iterate through and pull various CVE numbers like are shown in original screenshot. This script will only show the result at 0.

    SecurityNestedRecommendation
    | where RecommendationSeverity == 'High' or RecommendationSeverity == 'Medium'
    | extend CVE = tostring(parse_json(tostring(AdditionalData.Cve))[0].Title)
    | extend Patchable_ = tostring(AdditionalData.Patchable)
    | extend Type_ = tostring(AdditionalData.Type)
    | extend PublishedTime_ = tostring(todatetime(AdditionalData.PublishedTime))
    | extend VMName = extract(@"(.+)/(.+)", 2, AssessedResourceId)
    | extend Base_ = tostring(parse_json(tostring(parse_json(tostring(AdditionalData.Cvss)).["3.0"])).Base)
    | extend Base_ = tostring(parse_json(tostring(parse_json(tostring(AdditionalData.Cvss)).["2.0"])).Base)
    | project VMName, strcat(CVE) , Severity = RecommendationSeverity, Description, PublishedTime = PublishedTime_, Patchable = Patchable_, Score = Base_, RemediationDescription
    | sort by VMName,Severity,Patchable

    0 comments