Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=Type

[RossJ]

Hi community

I have a Desktop report that calls a web API provided by Tenable, a security vulnerability scanner, to retrieve a list of IT assets and associated vulnerabilities. The report calls one endpoint to get a list of assets, then three other endpoints to append columns for the identified vulnerabilities for each asset. The preview works correctly, but the following error is thrown when applying changes:

Failed to save modifications to the server. Error returned: "OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from [URL] (500): Internal Server Error."

I used the method descriped by Reza Rad (2018) to create two refrences to my original query, one removing error rows and one keeping error rows:

let
    Source = #"Tenable - Original",
    #"Removed Errors" = Table.RemoveRowsWithErrors(Source)
in
    #"Removed Errors"

However, this doesn't prevent the error from occuring, so this method must not move all errors. When I look at my reference where I have kept the error rows, I see the following error:

Expression.Error: We cannot convert the value null to type Text.
Details:
    Value=
    Type=Type

I worked backwards, and identified the error is being thrown by the #"Expand vulnerability details" step in my query:

let
    #"Get JSON object" = fnGetJsonFromTenable("https://cloud.tenable.com/workbenches/assets?date_range=30&filter.0.quality=eq&filter.0.filter=has_plugin_results&filter.0.value=true"),
    #"Drill down list" = #"Get JSON object"[assets],
    #"Convert to table" = Table.FromList(#"Drill down list", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand column" = Table.ExpandRecordColumn(#"Convert to table", "Column1",
        {"ipv4", "fqdn", "netbios_name", "operating_system", "has_agent", "id", "last_seen"},
        {"IPv4", "DNS", "NetBIOS", "OS", "Tenable Agent Installed", "Tenable Asset UUID", "Asset Last Seen"}
    ),
    #"Expand IPv4" = Table.ExpandListColumn(#"Expand column", "IPv4"),
    #"Expand DNS" = Table.ExpandListColumn(#"Expand IPv4", "DNS"),
    #"Expand NetBIOS" = Table.ExpandListColumn(#"Expand DNS", "NetBIOS"),
    #"Expand OS" = Table.ExpandListColumn(#"Expand NetBIOS", "OS"),
    #"Get asset location" = Table.AddColumn(#"Expand OS", "Location", each fnGetAssetTagByName([Tenable Asset UUID], "Location")),
    #"Expand location" = Table.ExpandTableColumn(#"Get asset location", "Location", {"Tag Value"}, {"Location"}),
    #"Get asset vulnerabilities" = Table.AddColumn(#"Expand location", "Vulnerabilities", each fnGetAssetVulnerabilities([Tenable Asset UUID])),
    #"Expand vulnerabilities" = Table.ExpandTableColumn(#"Get asset vulnerabilities", "Vulnerabilities", {"Plugin ID", "Plugin Name", "Plugin Family"}),
    #"Change Plugin ID to text" = Table.TransformColumnTypes(#"Expand vulnerabilities",{{"Plugin ID", type text}}),
    #"Get vulnerability details" = Table.AddColumn(#"Change Plugin ID to text", "VulnerabilityDetails", each fnGetAssetVulnerabilityDetails([Tenable Asset UUID], [Plugin ID])),
    #"Expand vulnerability details" = Table.ExpandTableColumn(#"Get vulnerability details", "VulnerabilityDetails", {"Vulnerability First Seen", "Vulnerability Last Seen", "Patch Published", "CVSSv3 Base Score", "CVSSv3 Temporal Score", "CPE", "Exploitable", "Exploit Ease"}),
    #"Change data types" = Table.TransformColumnTypes(#"Expand vulnerability details",{{"IPv4", type text}, {"DNS", type text}, {"NetBIOS", type text}, {"OS", type text}, {"Tenable Agent Installed", type logical}, {"Tenable Asset UUID", type text}, {"Asset Last Seen", type datetime}, {"Location", type text}, {"Plugin Name", type text}, {"Plugin Family", type text}, {"Vulnerability First Seen", type datetime}, {"Vulnerability Last Seen", type datetime}, {"Patch Published", type datetime}, {"CVSSv3 Base Score", type number}, {"CVSSv3 Temporal Score", type number}, {"CPE", type text}, {"Exploitable", type logical}, {"Exploit Ease", type text}})

in
    #"Change data types"

Because the preview works correctly, I don't know which row is causing the error, and therefore how to resolve it. I believe the fnGetAssetVulnerabilityDetails custom function called in step #"Get vulnerability details" isn't always returning a valid structure, but I don't know how to fix that. The function is defined as:

let
    fnGetAssetVulnerabilityDetails = (assetId as text, pluginId as text) =>
    let
        JsonObject = fnGetJsonFromTenable("https://cloud.tenable.com/workbenches/assets/" & assetId & "/vulnerabilities/" & pluginId & "/info"),
        ConvertToTable = Record.ToTable(JsonObject),
        DeleteNameColumn = Table.RemoveColumns(ConvertToTable, {"Name"}),
        ExpandValue = Table.ExpandRecordColumn(DeleteNameColumn, "Value",
            {"discovery", "plugin_details", "risk_information", "vulnerability_information"},
            {"discovery", "plugin_details", "risk_information", "vulnerability_information"}
        ),
        ExpandDiscovery = Table.ExpandRecordColumn(ExpandValue, "discovery",
            {"seen_first", "seen_last"},
            {"Vulnerability First Seen", "Vulnerability Last Seen"}
        ),
        ExpandPluginDetails = Table.ExpandRecordColumn(ExpandDiscovery, "plugin_details",
            {"publication_date"},
            {"Patch Published"}
        ),
        ExpandRiskInformation = Table.ExpandRecordColumn(ExpandPluginDetails, "risk_information",
            {"cvss3_base_score", "cvss3_temporal_score"},
            {"CVSSv3 Base Score", "CVSSv3 Temporal Score"}
        ),
        ExpandVulnerabilityInformation = Table.ExpandRecordColumn(ExpandRiskInformation, "vulnerability_information",
            {"cpe", "exploit_available", "exploitability_ease"},
            {"CPE", "Exploitable", "Exploit Ease"}
        ),
        ReplaceNullCpe = Table.TransformColumns(ExpandVulnerabilityInformation, {"CPE", each if _ is null then {""} else _}),
        ExtractCpe = Table.TransformColumns(ReplaceNullCpe, {"CPE", each Text.Combine(List.Transform(_, Text.From), ","), type text})
    in
        ExtractCpe 
in
    fnGetAssetVulnerabilityDetails

For context. the following table provides sample data from the report: 

IPv4

Netbios

DNS

OS

Tenable Agent Installed

Tenable UUID

Asset Last Seen

Location Tags

Plugin ID

Plugin Name

Plugin Family

CPE

Severity

CVSSv3 Base Score

CVSSv3 Temporal Score

Exploitable

Exploit Ease

Vulnerability First Seen

Vulnerability Last Seen

Patch Published

Date

[Redacted]

[Redacted]

[Redacted]

Microsoft Windows 10 Enterprise

TRUE

[Redacted]

2020-04-09T11:58:20.342Z

63155

Microsoft Windows Unquoted Service Path Enumeration

Windows

Medium

7.8

7

TRUE

Exploits are available

2019-04-12T15:36:11.283Z

2020-04-09T11:58:20.342Z

2012-12-05T00:00:00Z

28/04/2020

I'd be grateful for any suggestions or advice.

[RossJ · Accepted solution]

I managed to work this out. I first thought the null error was being thrown inside the custom function fnGetAssetVulnerabilityDetails, so I tried to sanitise nulls before concatenating the string, but that didn't work. I then thought the null error was being thrown by the function header, so I changed the function declaration from

let fnGetAssetVulnerabilityDetails = (assetId as text, pluginId as text) =>

to

let fnGetAssetVulnerabilityDetails = (assetId as any, pluginId as any) =>

to allow nulls to come in, but that didn't work either.

I then discovered the error was being thrown in my main procedure while calling the function. I don't understand why Power BI wasn't passing null parameters, but added a step to remove the nulls before calling the function.

    #"Change Plugin ID data type" = Table.TransformColumnTypes(#"Expand vulnerabilities",{{"Plugin ID", type text}}),
    #"Replace null Plugin IDs" = Table.TransformColumns(#"Change Plugin ID data type", {"Plugin ID", each if _ is null then "0" else _}),
    #"Get vulnerability details" = Table.AddColumn(#"Replace null Plugin IDs", "VulnerabilityDetails", each fnGetAssetVulnerabilityDetails([Tenable Asset UUID], [Plugin ID])),

I hope this helps someone else.

[Stylebender89]

Hi RossJ, can you please share how did you manage to connect to Tenable rest api, with PowerBi in the first place ? What parameters did you use ? Thanks!

[RossJ]

Hi Stylebender

I wrote a custom function called fnGetJsonFromTenable:

let
    fnGetJsonFromTenable = (url as text, accessKey as text, secretKey as text) =>
    let
        KeyString = "accessKey=" & accessKey & "; secretKey=" & secretKey & ";",
        Source = Web.Contents(url,
            [
                Headers = [#"X-ApiKeys" = KeyString]
            ]
        ),
        TextString = Text.FromBinary(Source),
        JsonObject = Json.Document(TextString)
    in
        JsonObject
in
    fnGetJsonFromTenable

Then called it like this:

let
   #"Define API URL" = "https://cloud.tenable.com/assets",
   #"Define access key" = "[redacted]",
   #"Define secret key" = "[redacted]",
   #"Get JSON object" = fnGetJsonFromTenable(#"Define API URL", #"Define access key", #"Define secret key"),
   #"Drill down list" = #"Get JSON object"[assets],
   #"Convert to table" = Table.FromList(#"Drill down list", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
   #"Convert to table"

Hope this helps!

Ross

[aj1973]

Hi @RossJ 

I hope you are still active in the community and you can help me with this : I did exactly what you described in your last message but I always get a Time Out

Could it be the size of the DB? if so how can add a filter to your M code to reduce the size?

Thanks

[RossJ]

Salut @aj1973 

It's been two years since I was working on this, so my memory is hazy. I was retrieving 23 160 assets from Tenable and the process was taking a long time, so if you have more assets than that, it's possible that it is timing out.

In the first instance, I suggest you change the request to retrieve a single asset using endpoint https://cloud.tenable.com/workbenches/assets/{asset_id}/info [API doc]. That will elminate network connection timeouts as the problem.

If that proof of concept works, then you have two options: increase the timeout or decrease the payload.

a) Increase the service timeout. I've had a look, and can't see where to increase timeout for webservice calls. There's some discussion here How-to-extend-Power-BI-service-timeout-limit, but that seems to be limited to database connections.

b) Decrease the payload. Tenable allows filters [API doc]. I haven't used them, but I believe you could filter by an attribute to reduce the number of records returned. For example, retrieve assets with IP address starting with 192.168.1.x, and then iterate that from 1 to 255. I haven't tried this and my PowerBI skills are limited, so I can't suggest how to do this in a loop.

Bonne chance.

Ross

[Stylebender89]

Hi Ross
Thank you very much for your answer , it really helped , it got me closer to a solution.

The issue I'm facing right now, after I run the 2nd function, I get an error: "Access to the resource is forbidden"

If I set the authentification to anything else except Anonymous , I get the following errror : 
"The 'X-ApiKeys' header is only supported when connecting anonymously"

Your help on this one is much appreciated !

[RossJ · Accepted solution]

I managed to figure this out. I first thought that the null error was being thrown inside the fnGetAssetVulnerabilityDetailscustom function, so I tried to disinfect the nulls before concatenating the string, but that didn't work. So I thought the function header threw a null error, so I changed the declaration of the function

let fnGetAssetVulnerabilityDetails = (assetId as text, pluginId as text) =>

a

let fnGetAssetVulnerabilityDetails = (assetId as any, pluginId as any) =>

to allow nulls, but it didn't work either.

Then I discovered that the error was being generated in my main procedure when calling the function. I don't understand why Power BI wasn't passing null parameters, but added a step to remove nulls before calling the function.

    #"Change Plugin ID data type" = Table.TransformColumnTypes(#"Expand vulnerabilities",{{"Plugin ID", type text}}),
    #"Replace null Plugin IDs" = Table.TransformColumns(#"Change Plugin ID data type", {"Plugin ID", each if _ is null then "0" else _}),
    #"Get vulnerability details" = Table.AddColumn(#"Replace null Plugin IDs", "VulnerabilityDetails", each fnGetAssetVulnerabilityDetails([Tenable Asset UUID], [Plugin ID])),

I hope this helps someone else.

[RossJ · Accepted solution]

I managed to work this out. I first thought the null error was being thrown inside the custom function fnGetAssetVulnerabilityDetails, so I tried to sanitise nulls before concatenating the string, but that didn't work. I then thought the null error was being thrown by the function header, so I changed the function declaration from

let fnGetAssetVulnerabilityDetails = (assetId as text, pluginId as text) =>

to

let fnGetAssetVulnerabilityDetails = (assetId as any, pluginId as any) =>

to allow nulls to come in, but that didn't work either.

I then discovered the error was being thrown in my main procedure while calling the function. I don't understand why Power BI wasn't passing null parameters, but added a step to remove the nulls before calling the function.

    #"Change Plugin ID data type" = Table.TransformColumnTypes(#"Expand vulnerabilities",{{"Plugin ID", type text}}),
    #"Replace null Plugin IDs" = Table.TransformColumns(#"Change Plugin ID data type", {"Plugin ID", each if _ is null then "0" else _}),
    #"Get vulnerability details" = Table.AddColumn(#"Replace null Plugin IDs", "VulnerabilityDetails", each fnGetAssetVulnerabilityDetails([Tenable Asset UUID], [Plugin ID])),

I hope this helps someone else.