Legacy Dev Forum Posts

Kasra_Moshrefi | 2019-05-06 17:28:23 UTC | #1

I am reading/experimenting PureCloud integration documents for days but i could not find an answer yet

I would like to get all call history data in specific date/time duration from PureCloud and save them in our local SQL database. Information such as who called with what phone number to all company’s phone numbers, which agent answered, duration of the call, start and end date/time,...

This process would run automatically every night. I'm wondering if there is a way or several ways such as:

• Writing a windows service to run every night on the server to connect to PureCloud and send specific data time from/to get all history data and then save it to local SQL server
• Designing a process in PureCloud and give the SQL server credentials and schedule for running the process
• Adding a service to SQL server to connect to PureCloud and get historical data for specific date/time duration

Questing is what is the right way and how to make connection to PureCloud Call History Database and Save them to local SQL Server database.

There is a similar answer to this question but I’m looking for a C#.NET way

tim.smith | 2019-05-06 17:34:51 UTC | #2

The general answer is that you'll need to write an application that uses the Analytics APIs to retrieve conversation data and then transform it to your liking for insertion into your relational database. The Developer Tools Analytics Query Builder is a good resource to help you design and test your analytics queries before/while writing the application. You may also be interested in the .NET SDK for your project.

I'm not aware of any .NET projects available that handle this, but there is a node.js project that illustrates retrieving data using Analytics APIs and transforming it for output. The project is Open Data Exporter.

Designing a process in PureCloud and give the SQL server credentials and schedule for running the process

This architecture is not possible, nor does PureCloud have the ability to connect directly to a relational database; we do not execute 3rd party apps from within PureCloud. Whatever you implement must be hosted and run via infrastructure of your choosing.

Kasra_Moshrefi | 2019-05-06 17:41:50 UTC | #3

Thanks tim, I was reviewing the project from your answer to another question but looks like i need to learn node.js

I think calling API: /api/v2/analytics/conversations/details/query would be another way.

Thanks again

Kasra_Moshrefi | 2019-05-07 14:40:54 UTC | #4

I used API and i got a list of all calls in JSON format: { "interval": "2019-04-16T04:00:00.000Z/2019-04-17T04:00:00.000Z", "order": "asc", "orderBy": "conversationStart", "paging": { "pageSize": "100", "pageNumber": "1" }

Problem is result Json string is not recognizable by SQL Server OPENJSON() function. There is additional ']' and '[' in the file. Is there a function to parse it to correct format?

tim.smith | 2019-05-07 19:43:59 UTC | #5

Can you elaborate on what you mean there are additional square brackets in the file? The analytics response produces valid JSON, which does contain arrays in several locations.

anon28066628 | 2019-05-07 20:14:24 UTC | #6

Just a shot in the dark, but I recall reading this about nested arrays and objects with OPENJSON:

If you reference JSON object or array you need to specify AS JSON clause.

Also here: https://docs.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-2017#openjson-and-json-input

You may need this to parse the result. The analytics response is wrapped in a top-level object, btw.

{
  "conversations": [ ... ]
}

https://stackoverflow.com/questions/37218254/sql-server-openjson-read-nested-json

Kasra_Moshrefi | 2019-05-08 12:53:43 UTC | #7

You are right Tim. I was using a parser that i wrote for simple arrays. I'm working on it.

Kasra_Moshrefi | 2019-05-08 12:55:49 UTC | #8

Thank you Smith. I saw this article before but i did not pay attention. Now i'm working on it and will share it.

Kasra_Moshrefi | 2019-05-21 20:07:35 UTC | #9

Finally :slight_smile:

DECLARE @json nVARCHAR(MAX)

SET @json = N'{ "conversations": [ ... ] }'

select conversations.conversationId, conversations.conversationStart , conversations.conversationEnd, conversations.originatingDirection, participants.participantName, participants.purpose, sessions.ani

from openjson (@json)

with ( conversations nvarchar(max) as json )as Allconversations

cross apply openjson (Allconversations.conversations) with ( conversationId varchar(100), conversationStart datetime, conversationEnd datetime, originatingDirection varchar(100), participants nvarchar(max) as json )as conversations

cross apply openjson (conversations.participants) with ( participantName varchar(100), purpose varchar(100), sessions nvarchar(max) as json ) as Participants

cross apply openjson (Participants.sessions) with ( ani varchar(100)

) as sessions

anon28066628 | 2019-05-09 18:53:27 UTC | #10

Very cool - thank you for sharing the result!

Kasra_Moshrefi | 2019-05-10 15:49:03 UTC | #11

But the main question still stands: "API call to purecloud in C#" so far we know that we need to use : "/api/v2/analytics/conversations/details/query" I added RestSharp reference to my C# project to easily use it. I guess API URL will be: https://api.mypurecloud.com/api/v2/analytics/conversations/details/query but i'm not sure how to send authorization parameters such as purecloud username and password also sending query filter parameters such as {"interval": "2019-05-10T04:00:00.000Z/2019-05-11T04:00:00.000Z","order": "asc","orderBy": "conversationStart","paging": {"pageSize": "1","pageNumber": 1}} to get results in JSON format.

var client = new RestClient("https://api.mypurecloud.com"); var request = new RestRequest("/api/v2/analytics/conversations/details/query", Method.POST); request.AddUrlSegment("interval", "2019-05-10T04:00:00.000Z/2019-05-11T04:00:00.000Z"); request.AddUrlSegment("order", "asc"); request.AddUrlSegment("orderBy", "conversationStart"); request.AddUrlSegment("paging", "{pageSize": "1", "pageNumber": 1}");

request.AddUrlSegment("username", "purecloudUserName"); request.AddUrlSegment("Password", "purecloudUserPassword");

• Error in paging segment because of wrong format
• Unknown authorization method

tim.smith | 2019-05-10 17:38:49 UTC | #12

Any reason you're not using the .NET SDK? The SDK has a method to handle this request. If this is an end-user desktop app, I'd recommend using the PureCloudOAuthControl. If it's a web app, there's a tutorial showing the implicit grant in ASP.NET. If this is a server app where no user is involved, you could use the client credentials helper in the SDK.

Kasra_Moshrefi | 2019-05-21 19:11:35 UTC | #13

Thanks Tim, I think i read all documents but looks like most of them are old and not working anymore. Project is: Installing a service on the server to run at the end of the day once, connect to purecloud, get all calls history saved in previous day and save it in a local database automatically. For this 1 - I Created an OAuth client in purecloud that expires in 24 hr (just for testing) with activating developer and integration roles 2 - took generated Client ID and Client Secret and used below code from SDK:

var accessTokenInfo = Configuration.Default.ApiClient.PostToken("My Client ID", "My Client Secret"); Configuration.Default.AccessToken = accessTokenInfo.AccessToken; var apiInstance = new AnalyticsApi(); var query = new ConversationQuery(); query.Interval = "2019-05-17T04:00:00.000Z/2019-05-22T04:00:00.000Z"; query.Order = ConversationQuery.OrderEnum.Asc; query.OrderBy = ConversationQuery.OrderByEnum.Conversationstart; query.Paging = new PagingSpec(25, 1); try { AnalyticsConversationQueryResponse result = apiInstance.PostAnalyticsConversationsDetailsQuery(query); textBox1.Text = result.ToJson(); } catch (Exception e2) { textBox1.Text = (e2.Message); }

3 - Result: Error calling PostAnalyticsConversationsDetailsQuery: {"status":403,"code":"forbidden","message":"Unable to perform the requested action. You are missing the following permission 'analytics:conversationDetail:view' in the provided division(s).","details":[],"errors":[]}

regarding to : https://developer.mypurecloud.com/api/rest/v2/conversations/index.html

Error 403 means: 403 - You are not authorized to perform the requested action.

tim.smith | 2019-05-21 21:31:23 UTC | #14

Kasra_Moshrefi, post:13, topic:5082
Unable to perform the requested action. You are missing the following permission 'analytics:conversationDetail:view

You need to grant the stated permissions to the role assigned to your client credentials OAuth client.

Kasra_Moshrefi | 2019-06-06 16:06:02 UTC | #16

I was running above SQL query on JSON string came from conversation API I noticed that a few conversations in JSON string, don't have ConversationEnd field. How it is possible that a conversation starts without end?

"originatingDirection": "inbound", "conversationId": "Something...", "conversationStart": "2019-06-03T22:18:06.741Z", "mediaStatsMinConversationMos": 4.1771845817565918, "mediaStatsMinConversationRFactor": 73.565750122070312, "participants": [

tim.smith | 2019-06-06 20:13:39 UTC | #17

A conversation won't have a value for conversationEnd if it hasn't ended yet, i.e. an active conversation.

Kasra_Moshrefi | 2019-06-10 19:35:19 UTC | #18

but conversation was started in 2019-06-03 and related agent have answered many calls after that time Update: Tested today again after 7 days and result is the same. No ConversationEnd field. Another question: I'm located in Toronto (Eastern time zone) and conversation Times i get is +4.00. Looks like servers are located in Iceland. Is there a way to get conversation call history in local time?

Thanks

tim.smith | 2019-06-10 22:53:23 UTC | #19

Please open a case with PureCloud Care to investigate the missing data for that conversation. Customer-specific data cannot be investigated on the forum.

The API generally sends timestamps in UTC, though some endpoints may deviate from that. It is generally considered the client app's responsibility to localize timestamps to the user's desired timezone.

Kasra_Moshrefi | 2019-06-13 13:09:18 UTC | #20

They fixed it :slight_smile: And time zone conversion fixed with below command:

declare @FromTimeZone varchar(50); declare @ToTimeZone varchar(50); SET @FromTimeZone = 'UTC' SET @ToTimeZone = 'Eastern Standard Time' CONVERT(datetime,conversations.conversationStart) AT TIME ZONE @FromTimeZone AT TIME ZONE @ToTimeZone

system | 2019-07-14 13:09:19 UTC | #21

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.

This post was migrated from the old Developer Forum.

ref: 5082