Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hi, thanks for sharing, I´m trying to connect but it show me this messge:
What can I do?
Hi,
Has anybody successfully received all the items using pagination for the Sophos API?
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.