For covering all aspects of any environment, it’s important to know your Opaque services as it represents a blind spots in your environment. So what if your environment is huge and you need to easily cover this point to take action like start support ticket for the opaque services relies on technologies that are supported by Dynatrace , upgrade technology version or doing manual instrumentation. So it’s important to not only select this also you should know many other things about each service like process group, process group tech version, in which host it relies and many important meta data about the technology.
We’ll cover this in the below steps:
- Auto tag all opaque services
- Define PowerQuery function to list services from API and this method should be recursive to run through all result pages
- Define PowerQuery function to get each service details
1. Auto tag all Opaque Services
We’ll define auto tag for capturing all services that is
- Not external, so we’ll add to the entity selector that our service must be running on process group that’s hosted on monitored host.
- Agent technology is missing and this is the Opaque service indicator, accordingly we use agentTechnologyType(“N/A”) in our entity selector query
This is the entity selector query below:
type(SERVICE),agentTechnologyType("N/A"),serviceType("WEB_REQUEST_SERVICE"),fromRelationship.runsOn(type(PROCESS_GROUP),fromRelationship.runsOn(type(HOST)))
2. Query that get entity details (service / process group)
When we getting list of services from the api we just get 3 things
- Entity type
- Entity ID
- Entity name
So we want to get entity details in addition for the ease of analysis in the future and defining the hotspots if we have big number of the opaque services based on process groups, technologies, hosts, etc..
We I made the below code for retrieving the entity details for each service after we fetch all services, open blank excel sheet then from the upper ribbon go to Data > Get Data > From Other Sources > Blank Query
let GetEntityDetails = (EntityID as text) =>
let
Response = Json.Document(Web.Contents("https://{your-domain}/e/{your-environment-id}/api/v2/entities/" & EntityID , [Headers=[Accept="application/*", #"Accept-Charset"="utf-8", #"Content-Type"="application/json", Authorization="Api-Token {your-token}"]]))
in
Response
in
GetEntityDetails
3. Query that retrieve pages and get services
In this section we’ll divide our code to 3 parts
- Part 1: how to make it recursive for fetch all services from all pages
- Part 2: get the service details
- Part 3: transform fetched data
To do all that we need API key with entities.read permission.
Part 1:
This is a recursive function for retrieving all services through all pages, as its repeat itself, when you use nextPageKey you don’t have to add entitySelector or other parameter to the url.
- In case it’s the first iteration it’ll add all needed parameters to the url
- In case not, it will just add nextPageKey only to the url
- In case no nextPageKey exists, it will stop recursion
See the below code:
let FetchServicesPages = (optional nextPageKey as text) =>
let
queryString = if nextPageKey = null then
"?entitySelector=type(SERVICE),tag(""opaque-service-tag"")"
else
"?nextPageKey=" & nextPageKey,
Response = Json.Document(Web.Contents("{environment-url}" & "api/v2/entities" & queryString, [Headers=[Accept="application/*", #"Accept-Charset"="utf-8", #"Content-Type"="application/json", Authorization="Api-Token {your-token}"]])),
services = Response[entities],
#"Services Table" = Table.FromList(services, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Services Table", "Column1", {"entityId", "displayName"}),
nextPage = if Record.HasFields(Response, "nextPageKey") then true else false,
FinalTable =
if nextPage = true then
Table.Combine({ #"Expanded Column1", FetchServicesPages(Response[nextPageKey])})
else
#"Expanded Column1"
in
FinalTable
in
FetchServicesPages
Part 2:
Is to get details for each services we have got, before checking the nextPage variable mentioned in part 1, we’ll add new column as below colored line and set it’s value using GetEntityDetails method we’ve defined separately before by passing the current Service ID to it.
#"Get Service Details" = Table.AddColumn(#"Expanded Column1", "ServiceDetails", each GetEntityDetails([entityId])),
see the below code snippet as our code should be:
let FetchServicesPages = (optional nextPageKey as text) =>
let
queryString = if nextPageKey = null then
"?entitySelector=type(SERVICE),tag(""opaque-service-tag"")"
else
"?nextPageKey=" & nextPageKey,
Response = Json.Document(Web.Contents("environment url" & "api/v2/entities" & queryString, [Headers=[Accept="application/*", #"Accept-Charset"="utf-8", #"Content-Type"="application/json", Authorization="Api-Token your token"]])),
services = Response[entities],
#"Services Table" = Table.FromList(services, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Services Table", "Column1", {"entityId", "displayName"}),
#"Get Service Details" = Table.AddColumn(#"Expanded Column1", "ServiceDetails", each GetEntityDetails([entityId])),
nextPage = if Record.HasFields(Response, "nextPageKey") then true else false,
FinalTable =
if nextPage = true then
Table.Combine({ #"Get Service Details", FetchServicesPages(Response[nextPageKey])})
else
#"Get Service Details"
in
FinalTable
in
FetchServicesPages
Part 3:
Transform ServiceDetails columns to get readable data.
when you expand technologies column you'll get duplication for each service by its techs it relies on, so it's important to limit service selection only based on Primary Technology!
what we did for defining the primary technology depending on Icon column based on it we remove other technologies from the list to keep only the primary technology name & version.
see the lines starting from #"Expanded ServiceDetails" in the below code snippet:
let FetchServicesPages = (optional nextPageKey as text) =>
let
queryString = if nextPageKey = null then
"?entitySelector=type(SERVICE),tag(""opaque-service-tag"")"
else
"?nextPageKey=" & nextPageKey,
Response = Json.Document(Web.Contents("environment url" & "api/v2/entities" & queryString, [Headers=[Accept="application/*", #"Accept-Charset"="utf-8", #"Content-Type"="application/json", Authorization="Api-Token your token"]])),
services = Response[entities],
#"Services Table" = Table.FromList(services, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Services Table", "Column1", {"entityId", "displayName"}),
#"Get Service Details" = Table.AddColumn(#"Expanded Column1", "ServiceDetails", each GetEntityDetails([entityId])),
#"Expanded ServiceDetails" = Table.ExpandRecordColumn(#"Get Service Details", "ServiceDetails", {"properties", "icon", "fromRelationships"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded ServiceDetails", "properties", {"port", "softwareTechnologies"}, {"port", "softwareTechnologies"}),
#"Expanded icon" = Table.ExpandRecordColumn(#"Expanded properties", "icon", {"primaryIconType"}, {"primaryTechnology"}),
#"Expanded fromRelationships" = Table.ExpandRecordColumn(#"Expanded icon", "fromRelationships", {"runsOn", "runsOnHost"}),
#"Expanded runsOn" = Table.ExpandListColumn(#"Expanded fromRelationships", "runsOn"),
#"Expanded runsOn1" = Table.ExpandRecordColumn(#"Expanded runsOn", "runsOn", {"id"}),
#"Expanded runsOnHost" = Table.AddColumn(#"Expanded runsOn1", "HostID", each Text.Combine(List.Transform([runsOnHost], each Text.From([id])), ",")),
//#"Expanded runsOnHost" = Table.ExpandListColumn(#"Expanded runsOn1",{"runsOnHost", each Text.Combine(List.Transform([runsOnHost], each Text.From([id])), ",")}),
// #"Expanded runsOnHost" = Table.ExpandListColumn(#"Expanded runsOn1", "runsOnHost"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded runsOnHost", {"runsOnHost"}),
#"Expanded softwareTechnologies" = Table.ExpandListColumn(#"Removed Columns", "softwareTechnologies"),
#"Expanded softwareTechnologies1" = Table.ExpandRecordColumn(#"Expanded softwareTechnologies", "softwareTechnologies", {"type", "version"}, {"technologyType", "technologyVersion"}),
#"Uppercased Text" = Table.TransformColumns(#"Expanded softwareTechnologies1",{{"primaryTechnology", Text.Upper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","-","_",Replacer.ReplaceText,{"primaryTechnology"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [technologyType] = [primaryTechnology]),
nextPage = if Record.HasFields(Response, "nextPageKey") then true else false,
FinalTable =
if nextPage = true then
Table.Combine({ #"Filtered Rows", FetchServicesPages(Response[nextPageKey])})
else
#"Filtered Rows"
in
FinalTable
in
FetchServicesPages
Finally you can focus on FetchServicesPages and press invoke without entering the nextPageKey as it’s optional parameter as shown below.
Featured Posts
