Forum Discussion
msgeorgie
5 years agoRegular Visitor
Need some help using the KnowBe4 rest API and PowerBI
I am attempting to pull KnowBe4 data into PowerBI and unfortunatley the data is paginated and the only variables available are page and per_page. There is no way to tell how many total records are i...
Samson217
4 years agoNew Member
This isn't a perfect solution, but I got this working.
let
//This will return up to 5000 users. Change the 2nd arguement in List.Numbers to query more or less users.
Source = List.Numbers(1,10,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://us.api.knowbe4.com/v1",
[
RelativePath= "users?page="& [Column1] & "per_page=500",
Headers=[Authorization="Bearer INSERT_YOUR_TOKEN_HERE"]
]
)
)
),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"id", "employee_number", "first_name", "last_name", "job_title", "email", "phish_prone_percentage", "phone_number", "extension", "mobile_phone_number", "location", "division", "manager_name", "manager_email", "provisioning_managed", "provisioning_guid", "groups", "current_risk_score", "aliases", "joined_on", "last_sign_in", "status", "organization", "department", "language", "comment", "employee_start_date", "archived_at", "custom_field_1", "custom_field_2", "custom_field_3", "custom_field_4", "custom_date_1", "custom_date_2"}, {"id", "employee_number", "first_name", "last_name", "job_title", "email", "phish_prone_percentage", "phone_number", "extension", "mobile_phone_number", "location", "division", "manager_name", "manager_email", "provisioning_managed", "provisioning_guid", "groups", "current_risk_score", "aliases", "joined_on", "last_sign_in", "status", "organization", "department", "language", "comment", "employee_start_date", "archived_at", "custom_field_1", "custom_field_2", "custom_field_3", "custom_field_4", "custom_date_1", "custom_date_2"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom1",{{"Column1", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [id] <> null and [id] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"})
in
#"Removed Columns"
bigdatakev
4 years agoFrequent Visitor
I used this code for my query and I was able to pull 1,000 rows where I had been only getting 500. I did an export from within the KnowBe4 app of the users and I have over 5,000 in the dataset. Does anyone know how to query the full dataset?