Authenticating to the Sophos Central API with Power Query

[Anonymous]

Authenticating to the Sophos Central API with Power Query

It took me quite some time start authenticating to the Sophos API, so for those searching, here's the Power Query that I use to obtain bearer tokens.

First, you'll need to create two parameters called SophosClientID and SophosClientSecret with values of your client ID and client secret from Sophos Central.

Next, you'll need to add a new data source of type 'Blank Query'.

Then, in the Power Query Editior, right click on the new data source and then click 'Advanced Editor'. In the Advanced editor, replace the pre-insterted text with the text below:

let
    SophosBearerToken = "Bearer " & (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])
in
    SophosBearerToken

Click Done, and you should now see a bearer token presented in the Power Query Editor.

You can rename this data source something like "SophosBearerToken", and call it in other data source queries using:

#"SophosBearerToken"

For example, you can query the Sophos WhoAmI API to get your Partner ID using the Power Query below:

let
    PartnerIDQuery = Json.Document(Web.Contents("https://api.central.sophos.com/whoami/v1", [Headers = [#"Authorization"=#"SophosBearerToken"]])),
    PartnerID = PartnerIDQuery[id]
in
    PartnerID

You can reference Sophos' documentation on their API for more useful queries to obtain information from Sophos Central.

Hopefully this helps some others! I use this method to authenticate to several APIs that require bearer tokens and it hasn't let me down!

[maherreraa]

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

What can I do? 

[ShaunBuchan]

Hi,

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

[stephan14x]

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

[JLynes]

This was really useful, thank you. 

I did manage to get this to read the nextKey and pull the data in reccursivley.

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]),
    Headers = [Headers = [#"X-Tenant-ID"=#"SophosTenantId",#"Authorization"="Bearer " & BearerToken]],
    GetAllPages = (Url as text, optional NextKey as nullable text) as list =>
        let
            // Construct the URL with the nextkey if it exists
            FullUrl = if NextKey <> null then Url & "&pageFromKey=" & NextKey else Url,
            Source = Json.Document(Web.Contents(FullUrl,Headers)),
            Data = Source[items],
            NewPageInfo = Source[pages],
            NewNextKey = Record.FieldOrDefault(NewPageInfo, "nextKey", null),
            // If a nextkey is present, recursively call the function to get the next page
            NextPageData = if NewNextKey <> null then @GetAllPages(Url, NewNextKey) else {}
        in
            // Combine the current page data with the next page data
            Data & NextPageData,
    BaseURL = #"SophosApiUrl" & "/endpoint/v1/endpoints?view=full&pageSize=500&pageTotal=true",
    EndpointItems = GetAllPages(BaseURL),
    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

[v-juanli-msft · Accepted solution]

Thanks for sharing!

I Would mark my answer as a solution so your post may be easily found when people search for the similar topic.

Best Regards

Maggie