Forum Discussion
Help with filtering the KnowBe4 "training/enrollments API endpoint
I am pretty new the "API" side of PowerBI, although have extensive experience in transforming data and building reports once they have been setup.
I am attempting to build a data model which includes data from this endpoint: "https://eu.api.knowbe4.com/v1/training/enrollments"
Without any API filters applied it takes a long time to pull back this data into PowerBI - but we only really need data where the [enrollment_date] parameter is within the last 90 days.
The KnowBe4 API documentation does not state that you can apply this particular filter to the request, so I am asking here in case anyone has achieved this or similar. Link to the documentation for this endpoint: https://developer.knowbe4.com/rest/reporting#tag/Training/paths/~1v1~1training~1enrollments/get
This is my query code currently, which I trial and errored to get working from a previous post from the users endpoint, and how to paginate successfully
let
Source = List.Numbers(1,10000,1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
Json.Document(
Web.Contents(
"https://eu.api.knowbe4.com/v1",
[
RelativePath= "training/enrollments?page="& [Column1] & "&per_page=500",
Timeout=#duration(0, 16, 40, 0),
Headers=[Authorization="Bearer <redacted>"]
]
)
)
),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"enrollment_id", "content_type", "module_name", "user", "campaign_name", "enrollment_date", "start_date", "completion_date", "status", "time_spent", "policy_acknowledged", "score"}, {"enrollment_id", "content_type", "module_name", "user", "campaign_name", "enrollment_date", "start_date", "completion_date", "status", "time_spent", "policy_acknowledged", "score"}),
#"Expanded user" = Table.ExpandRecordColumn(#"Expanded Custom1", "user", {"id", "first_name", "last_name", "email"}, {"id", "first_name", "last_name", "email"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded user",{{"enrollment_date", type datetime}, {"start_date", type datetime}, {"completion_date", type datetime}, {"time_spent", type number}})
in
#"Changed Type1"
Hi foxyox ,
Thank you for reaching out to the Microsoft Community Forum.
You are trying to filter the KnowBe4 training/enrollments API endpoint to only retrieve data from the last 90 days based on the enrollment_date field, but the official documentation does not mention support for such filtering directly in the API request URL. Since the API doesn’t support filtering by enrollment_date on the server side.
Instead of trying to filter in the API call, continue fetching all pages, but filter the data in Power Query after import to only keep rows from the past 90 days.
Please refer below M code to filter the data from the past 90 days.
let
Source = List.Numbers(1, 10000, 1),
PageTable = Table.FromList(Source, Splitter.SplitByNothing(), {"PageNumber"}),
AddApiCall = Table.AddColumn(PageTable, "ApiResponse", each
Json.Document(
Web.Contents(
"https://eu.api.knowbe4.com/v1",
[
RelativePath = "training/enrollments?page=" & Text.From([PageNumber]) & "&per_page=500",
Timeout = #duration(0, 16, 40, 0),
Headers = [Authorization = "Bearer <redacted>"]
]
)
)
),ExpandEnrollments = Table.ExpandListColumn(AddApiCall, "ApiResponse"),
ExpandRecords = Table.ExpandRecordColumn(ExpandEnrollments, "ApiResponse", {
"enrollment_id", "content_type", "module_name", "user", "campaign_name",
"enrollment_date", "start_date", "completion_date", "status", "time_spent",
"policy_acknowledged", "score"
}),ExpandUser = Table.ExpandRecordColumn(ExpandRecords, "user", {"id", "first_name", "last_name", "email"}, {"user_id", "first_name", "last_name", "email"}),
ChangeTypes = Table.TransformColumnTypes(ExpandUser, {
{"enrollment_date", type datetime},
{"start_date", type datetime},
{"completion_date", type datetime},
{"time_spent", type number}
}),
FilterRecent = Table.SelectRows(ChangeTypes, each [enrollment_date] >= DateTime.LocalNow() - #duration(90, 0, 0, 0))
in
FilterRecentI hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Thank you, I think this is the best outome considering the lack of filterability on the API endpoint - whilst it may not help with the data refresh time, it certainly keeps the report smaller and more usable.
4 Replies
Hi foxyox ,
Thank you for reaching out to the Microsoft Community Forum.
You are trying to filter the KnowBe4 training/enrollments API endpoint to only retrieve data from the last 90 days based on the enrollment_date field, but the official documentation does not mention support for such filtering directly in the API request URL. Since the API doesn’t support filtering by enrollment_date on the server side.
Instead of trying to filter in the API call, continue fetching all pages, but filter the data in Power Query after import to only keep rows from the past 90 days.
Please refer below M code to filter the data from the past 90 days.
let
Source = List.Numbers(1, 10000, 1),
PageTable = Table.FromList(Source, Splitter.SplitByNothing(), {"PageNumber"}),
AddApiCall = Table.AddColumn(PageTable, "ApiResponse", each
Json.Document(
Web.Contents(
"https://eu.api.knowbe4.com/v1",
[
RelativePath = "training/enrollments?page=" & Text.From([PageNumber]) & "&per_page=500",
Timeout = #duration(0, 16, 40, 0),
Headers = [Authorization = "Bearer <redacted>"]
]
)
)
),ExpandEnrollments = Table.ExpandListColumn(AddApiCall, "ApiResponse"),
ExpandRecords = Table.ExpandRecordColumn(ExpandEnrollments, "ApiResponse", {
"enrollment_id", "content_type", "module_name", "user", "campaign_name",
"enrollment_date", "start_date", "completion_date", "status", "time_spent",
"policy_acknowledged", "score"
}),ExpandUser = Table.ExpandRecordColumn(ExpandRecords, "user", {"id", "first_name", "last_name", "email"}, {"user_id", "first_name", "last_name", "email"}),
ChangeTypes = Table.TransformColumnTypes(ExpandUser, {
{"enrollment_date", type datetime},
{"start_date", type datetime},
{"completion_date", type datetime},
{"time_spent", type number}
}),
FilterRecent = Table.SelectRows(ChangeTypes, each [enrollment_date] >= DateTime.LocalNow() - #duration(90, 0, 0, 0))
in
FilterRecentI hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Thank you, I think this is the best outome considering the lack of filterability on the API endpoint - whilst it may not help with the data refresh time, it certainly keeps the report smaller and more usable.
It might be worth asking the KnowBe4 support if they can add that filter to their API.
Thanks, I raised with them at the same time as posting this, they said the reporting API was not filterable in this way but their graph API endpoint was, https://developer.knowbe4.com/graphql/ksat/query/enrollments
So I need to look at this next. Thank you
