Authenticating to the Sophos Central API with Power Query

Hi,

thanks for sharing, I'm new to Power Bi and had some issues getting your examples running, so i'd like to share for others and need some more help.

I want this to update on powerbi.com and had multiple issues with privacy settings and an error like this:

Formula.Firewall: Query 'SophosEndpoints' (step 'XXX') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

For the query to run online you can not ignor the privacy settings.

What I ended up doing:

Setup you API Credentials: https://developer.sophos.com/getting-started-tenant

And set these two parameters as shown in Sophos Central:

SophosClientID

SophosClientSecret

Run this query to get the Tenant Id

And set it to a parameter: SophosTenantId

let
    // Get an Auth Toke
    BearerToken = (Json.Document(Web.Contents("https://id.sophos.com/api/v2/oauth2/token",
        [
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
            Content = Text.ToBinary("grant_type=client_credentials&client_id=" & #"SophosClientID" & "&client_secret=" & #"SophosClientSecret" & "&scope=token")
        ]
    )) [access_token]),

    // Get the Tenant-ID
    PartnerIDQuery = Json.Document(Web.Contents("https://api.central.sophos.com/whoami/v1", [Headers = [#"Authorization"="Bearer " & BearerToken]])),
    id = PartnerIDQuery[id]
in
    id

To get theApiUrl of your regional API Host run this Query

And set it to a parameter: SophosApiUrl

let
    // Get an Auth Toke
    BearerToken = (Json.Document(Web.Contents("https://id.sophos.com/api/v2/oauth2/token",
        [
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
            Content = Text.ToBinary("grant_type=client_credentials&client_id=" & #"SophosClientID" & "&client_secret=" & #"SophosClientSecret" & "&scope=token")
        ]
    )) [access_token]),

    // Get the Tenant-ID
    PartnerIDQuery = Json.Document(Web.Contents("https://api.central.sophos.com/whoami/v1", [Headers = [#"Authorization"="Bearer " & BearerToken]])),
    // Get the Tenant-ID
    apiHosts = PartnerIDQuery[apiHosts],
    RegionalHost = apiHosts[dataRegion]
in
    RegionalHost

Now you have 4 parameters set:

SophosClientID

SophosClientSecret

SophosTenantId

SophosApiUrl

With these you can start calling the Api and modify the data:

let
    // Get an Auth Toke
    BearerToken = (Json.Document(Web.Contents("https://id.sophos.com/api/v2/oauth2/token",
        [
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
            Content = Text.ToBinary("grant_type=client_credentials&client_id=" & #"SophosClientID" & "&client_secret=" & #"SophosClientSecret" & "&scope=token")
        ]
    )) [access_token]),
    
    GetTotalItemsQuery = Json.Document(Web.Contents(#"SophosApiUrl", [RelativePath="endpoint/v1/endpoints?&pageSize=500&pageTotal=true", Headers = [#"X-Tenant-ID"=#"SophosTenantId",#"Authorization"="Bearer " & BearerToken]])),
    EndpointItems = GetTotalItemsQuery[items],
    ToTable = Table.FromList(EndpointItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandRecords = Table.ExpandRecordColumn(ToTable, "Column1", {"id", "type", "tenant", "hostname", "health", "os", "ipv4Addresses", "ipv6Addresses", "macAddresses", "group", "associatedPerson", "tamperProtectionEnabled", "assignedProducts", "lastSeenAt"}, {"id", "type", "tenant", "hostname", "health", "os", "ipv4Addresses", "ipv6Addresses", "macAddresses", "group", "associatedPerson", "tamperProtectionEnabled", "assignedProducts", "lastSeenAt"}),

    ExpandTenant = Table.ExpandRecordColumn(ExpandRecords, "tenant", {"id"}, {"id.1"}),
    ExpandHealth = Table.ExpandRecordColumn(ExpandTenant, "health", {"overall", "threats", "services"}, {"health.overall", "health.threats", "health.services"}),
    ExpandHealthThreats = Table.ExpandRecordColumn(ExpandHealth, "health.threats", {"status"}, {"status"}),
    ExpandHealthServices = Table.ExpandRecordColumn(ExpandHealthThreats, "health.services", {"status"}, {"health.services.status"}),
    ExpandOS = Table.ExpandRecordColumn(ExpandHealthServices, "os", {"isServer", "platform", "name", "majorVersion", "minorVersion", "build"}, {"os.isServer", "os.platform", "os.name", "os.majorVersion", "os.minorVersion", "os.build"}),
    ExpandGroup = Table.ExpandRecordColumn(ExpandOS, "group", {"name"}, {"group.name"}),
    ExpandAssociatedPerson = Table.ExpandRecordColumn(ExpandGroup, "associatedPerson", {"viaLogin"}, {"associatedPerson.viaLogin"}),
    ExtractValues = Table.TransformColumns(ExpandAssociatedPerson, {"ipv4Addresses", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RemoveColumns = Table.RemoveColumns(ExtractValues,{"ipv6Addresses", "macAddresses", "assignedProducts"})
in
    RemoveColumns

I'm not using an second Query as you described, to get the BearerToken, as this resultet in privacy errors, like the one mentioned above.

The only problem now is, that the Sophos Api uses paging and the max page size is 500.

When you have more then 500 endpoints you will get a nextKey value from the first call to you need to provide this value in the next call.

Every time I try to do this dynamically, I end up with privacy errors.

This is my current state, it is working, but only for 1000 or less clients:

let
    // Get an Auth Toke
    BearerToken = (Json.Document(Web.Contents("https://id.sophos.com/api/v2/oauth2/token",
        [
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
            Content = Text.ToBinary("grant_type=client_credentials&client_id=" & #"SophosClientID" & "&client_secret=" & #"SophosClientSecret" & "&scope=token")
        ]
    )) [access_token]),
    
    // Get the start Key of Page 2
    Page2StartKeyQuery = Json.Document(Web.Contents(#"SophosApiUrl", [RelativePath="endpoint/v1/endpoints?pageFromKey=&pageSize=500&pageTotal=true", Headers = [#"X-Tenant-ID"=#"SophosTenantId",#"Authorization"="Bearer " & BearerToken]])),
    StartKeyPagesInfo = Page2StartKeyQuery[pages],
    Page2StartKey = StartKeyPagesInfo[nextKey],
    Keys = {"", Page2StartKey},

    GetPage = (nextKey) =>
    let 
        GetTotalItemsQuery = Json.Document(Web.Contents(#"SophosApiUrl", [RelativePath="endpoint/v1/endpoints?pageFromKey=" & nextKey & "&pageSize=500&pageTotal=true", Headers = [#"X-Tenant-ID"=#"SophosTenantId",#"Authorization"="Bearer " & BearerToken]])),
        EndpointItems = GetTotalItemsQuery[items],
        ToTable = Table.FromList(EndpointItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        ToTable,

    Pages = List.Transform(Keys, each GetPage(_)),
    ToTable2 = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandRecords1 = Table.ExpandTableColumn(ToTable2, "Column1", {"Column1"}, {"Column1.1"}),
    ExpandRecords1.1 = Table.ExpandRecordColumn(ExpandRecords1, "Column1.1", {"id", "type", "tenant", "hostname", "health", "os", "ipv4Addresses", "ipv6Addresses", "macAddresses", "group", "associatedPerson", "tamperProtectionEnabled", "assignedProducts", "lastSeenAt"}, {"id", "type", "tenant", "hostname", "health", "os", "ipv4Addresses", "ipv6Addresses", "macAddresses", "group", "associatedPerson", "tamperProtectionEnabled", "assignedProducts", "lastSeenAt"}),
    ExpandTenant = Table.ExpandRecordColumn(ExpandRecords1.1, "tenant", {"id"}, {"id.1"}),
    ExpandHealth = Table.ExpandRecordColumn(ExpandTenant, "health", {"overall", "threats", "services"}, {"health.overall", "health.threats", "health.services"}),
    ExpandHealthThreats = Table.ExpandRecordColumn(ExpandHealth, "health.threats", {"status"}, {"status"}),
    ExpandHealthServices = Table.ExpandRecordColumn(ExpandHealthThreats, "health.services", {"status"}, {"health.services.status"}),
    ExpandOS = Table.ExpandRecordColumn(ExpandHealthServices, "os", {"isServer", "platform", "name", "majorVersion", "minorVersion", "build"}, {"os.isServer", "os.platform", "os.name", "os.majorVersion", "os.minorVersion", "os.build"}),
    ExpandGroup = Table.ExpandRecordColumn(ExpandOS, "group", {"name"}, {"group.name"}),
    ExpandAssociatedPerson = Table.ExpandRecordColumn(ExpandGroup, "associatedPerson", {"viaLogin"}, {"associatedPerson.viaLogin"}),
    ExtractValues = Table.TransformColumns(ExpandAssociatedPerson, {"ipv4Addresses", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RemoveColumns = Table.RemoveColumns(ExtractValues,{"ipv6Addresses", "macAddresses", "assignedProducts"})
in
    RemoveColumns

There must be a way to get this dynamically?

The nextKey is missing from the result when you have reached the last page.

Any help would be appriciated.

Thanks Stephan

References:

Intro and API Urls: https://developer.sophos.com/intro

Endpoint API: https://developer.sophos.com/docs/endpoint-v1/1/overview

Hi,

Has anybody successfully received all the items using pagination for the Sophos API?

Hi, thanks for sharing, I´m trying to connect but it show me this messge:

What can I do?