Open Forum

Oisin Glynn posted 03-29-2023 06:42 Best Answer

You should make sure you understand and are comfortable with code snipits before using them. 

This would be a multi step process involved in authenticating and then requesting data.

This example assumes you've setup some parameters names

• apikey - contains the api key for Work Analytics
• WorkAUsername - contains the username for a user who has the correct permission and has used the browser Analytics at least one time
• WorkAPassword - contains the password for the user.
• pbxname - contains the name of the PBX you are looking for data from
• StartTimeDate - contains the start time/date for the query
• EndTimeDate - contains the start time/date for the query

Step 1 function called GetBearer

(username as text, password as text) =>

let
credentials = "username=" & username & "&password=" & password,
body = credentials,

webdata = Web.Contents("https://api.8x8.com/analytics/work/v1/oauth/token", [Headers=[#"8x8-apikey"=apikey, #"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(body)]),
response = Json.Document(webdata)

in
    response[access_token]

Create a Query (This shows call-legs but you could hit any of the endpoints)

This calls GetBearer function above to authenticate

The formatting of the data was for this purpose you can change this to suit your needs.

let

	BaseURL = "https://api.8x8.com/analytics/work/v2/call-legs?",
	EntitiesPerPage = 1000,
	pageSize = "1000",

	bearertoken = GetBearer(WorkAUsername, WorkAPassword),

	pbxid = pbxname,
	startTime = DateTime.ToText(StartTimeDate, "yyyy-MM-dd HH:mm:ss"),
	endTime = DateTime.ToText(EndTimeDate, "yyyy-MM-dd HH:mm:ss"),
	timeZone = "Europe/London",

	params = "pbxId=" & pbxid & "&startTime=" & startTime & "&endTime=" & endTime & "&timeZone=" & timeZone & "&pageSize=" & pageSize,
	Options = [Headers=[ #"Authorization" = "Bearer " & bearertoken, #"8x8-apikey"=apikey, #"Content-Type"="application/json" ]],
	url = BaseURL&params,
	scrollUrl = BaseURL&params & "&scrollId=",


		GetJson = (Url) =>
			let Options = [Headers=[ #"Authorization" = "Bearer " & bearertoken, #"8x8-apikey"=apikey, #"Content-Type"="application/json" ]],
				RawData = Web.Contents(Url, Options),
				Json    = Json.Document(RawData)
			in  Json,

		GetTotalRecords = () =>
			let Url = BaseURL & params & "&pageSize=1",
				Json  = GetJson(Url),
				Count = Json[meta]
			in  Count,
 
	EntityCount = GetTotalRecords()[totalRecordCount],
	PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
	Pages       = Fetch(url, scrollUrl, Options, null, null, PageCount),
	Entities    = Pages,
    #"Expanded Column1" = Table.ExpandRecordColumn(Entities, "Column1", {"callId", "legId", "startTimeUTC", "startTime", "connectTimeUTC", "connectTime", "disconnectedTimeUTC", "disconnectedTime", "talkTimeMS", "talkTime", "caller", "callerName", "callee", "calleeName", "lra", "direction", "parentCallId", "transferToCallId", "dnis", "status", "callerDeviceId", "calleeDeviceId", "callerDeviceModel", "calleeDeviceModel", "callerId", "missed", "abandoned", "answered", "cause", "callerSvcName", "callerSvcType", "calleeSvcName", "calleeSvcType", "lraType", "calleeHoldDurationMS", "calleeHoldDuration", "calleeDisconnectOnHold", "callerDisconnectOnHold", "pbxId", "sipCallId", "departments", "branches", "recordServiceOn", "bargeServiceOn", "masterSlaveExts", "propsLastPartyDisp", "accountCode", "aaPath", "callTime"}, {"callId", "legId", "startTimeUTC", "startTime", "connectTimeUTC", "connectTime", "disconnectedTimeUTC", "disconnectedTime", "talkTimeMS", "talkTime", "caller", "callerName", "callee", "calleeName", "lra", "direction", "parentCallId", "transferToCallId", "dnis", "status", "callerDeviceId", "calleeDeviceId", "callerDeviceModel", "calleeDeviceModel", "callerId", "missed", "abandoned", "answered", "cause", "callerSvcName", "callerSvcType", "calleeSvcName", "calleeSvcType", "lraType", "calleeHoldDurationMS", "calleeHoldDuration", "calleeDisconnectOnHold", "callerDisconnectOnHold", "pbxId", "sipCallId", "departments", "branches", "recordServiceOn", "bargeServiceOn", "masterSlaveExts", "propsLastPartyDisp", "accountCode", "aaPath", "callTime"}),
	
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"startTime", type datetimezone}, {"disconnectedTime", type datetimezone}, {"connectTime", type text}, {"legId", Int64.Type}, {"callTime", Int64.Type}, {"calleeHoldDurationMS", Int64.Type}, {"direction", type text}})
in
    #"Changed Type"

EDITED

Add Fetch Function

let
    RecursiveFetch = (Url, scrollUrl, options, scroll, counter, pageCount) =>
			let
				TotalPageCount = pageCount,
				
				Counter = if (counter = null) then 0 else counter,

				Results = if (scroll = null) then            
					Json.Document(Web.Contents(Url, options))
				else
					Json.Document(Web.Contents(scrollUrl&scroll, options)),

				ParsedResults = Table.FromList(Results[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

				Return = if (Counter < TotalPageCount) then
					ParsedResults & Fetch(Url, scrollUrl, options, Results[meta][scrollId], Counter+1, TotalPageCount)
				else
					ParsedResults 
			in Return
in
    RecursiveFetch