PowerBI - connection to the Auth0 user list and log

Help
1

Has anyone had success connecting there Auth0 user database and log file into powerBI?

1 Like
2

Hey there @kfaloon!

Researched our community forum a little bit and found this. Think this might be helpful!

3

I read this question a bit differently than konrad did. I wanted to extract users from Auth0 to Power BI for reporting purposes. Not sure if this is the route we’ll go but it’s one way.


let
  FetchBearerTokenResult = Json.Document(Web.Contents("https://YOURCOMPANY.auth0.com/oauth/token", [
    IsRetry=true,
    Content=Json.FromValue([client_id=YOUR_CLIENT_ID,
      client_secret=YOUR_CLIENT_SECRET
      audience="https://YOURCOMPANY.auth0.com/api/v2/", 
      grant_type="client_credentials"]), 
    Headers=[#"Content-Type"="application/json"]
  ])),
  BearerToken=FetchBearerTokenResult[access_token],
  StartJobResult = Json.Document(Web.Contents(
    "https://deltaplastics.auth0.com/api/v2/jobs/users-exports", 
    [
      IsRetry=true,
      Content=Json.FromValue([format="csv"]), 
      Headers=[
        #"Accept"="application/json", 
        #"Content-Type"="application/json",
        #"Authorization"="Bearer " & FetchBearerTokenResult[access_token]
      ]
    ]
  )),
  JobResultId = StartJobResult[id],
  FinishJobResults = List.Generate(
    () => [delayInSeconds=1, attempts=0, done=false, location=null, status="First Run", percentage_done=0, created_at=null, connection_id=null],
    each ([done] = false) and ([attempts] < 24),
    (_) => 
      if (_[location] <> null) then 
        [delayInSeconds=-1, attempts=-1, done=true, location=_[location], status="Done", percentage_done=100, created_at=null, connection_id=null]
      else Function.InvokeAfter(() =>
        let 
          testResult = Json.Document(Web.Contents(
          "https://YOURCOMPANY.auth0.com/api/v2/jobs/" & StartJobResult[id], 
          [
            IsRetry=true,
            Headers=[
                #"Accept"="application/json",
                #"Authorization"="Bearer " & FetchBearerTokenResult[access_token]
            ] 
          ]
          )),
          DT4=Diagnostics.Trace(TraceLevel.Information, "Job Status: " & testResult[status] 
            & ", Time Left: " & testResult[time_left_seconds], "Erp"),
          resultDone = Record.HasFields(testResult, "location"),
          resultLocation = if resultDone then testResult[location] else null,
          delay = if Record.HasFields(testResult, "time_left_seconds") 
            then testResult[time_left_seconds]
            else _[delayInSeconds] + 3
      in
          [
              delayInSeconds=delay, attempts=_[attempts] + 1, 
              done=false,
              location=resultLocation, status=testResult[status],
              percentage_done=testResult[percentage_done],
              created_at=testResult[created_at], connection_id=testResult[connection_id]
          ]
      , #duration(0, 0, 0, _[delayInSeconds]))
  ),
  FinishJobResultsTable = Table.FromList(FinishJobResults, Record.FieldValues),
  ZipUri = List.Last(FinishJobResults)[location],
  Zip = Web.Contents(ZipUri),
  UsersText = Binary.Decompress(Zip, Compression.GZip) 
in
  Table.PromoteHeaders(Csv.Document(UsersText))