Gravatar for daniel.campbell@solarwinds.com

Question by Daniel Campbell, Aug 11, 2016 10:16 AM

Using the Usage Analytics API to retrieve data into an SQL Server database

I'm looking for a way to retrieve the Usage Analytics data directly into an SQL Server database. Coveo Customer Support recommended the Usage Analytics API.

Does anyone have experience using the Usage Analytics API to retrieve data into an SQL Server database? I haven't used an API before, though have been reading about them online (including tutorials). Relative to SQL Server, it looks like this web page is relevant to my interest -- but I'm feeling like I need pointers from someone with more experience, able to give advice on how to get started.

Gravatar for jflheureux@coveo.com

Comment by Jean-François L'Heureux, Aug 11, 2016 10:48 AM

Hi Daniel,

What do you want to achieve with the analytics data after you extract it from the Cove Usage Analytics platform and add it in your SQL database?

This is the first time I come across this requirement to move the analytics data somewhere else. By better understanding your use case, I think we would be able to suggest an easier alternative.

Thanks,

Jeff

Gravatar for daniel.campbell@solarwinds.com

Comment by Daniel Campbell, Aug 11, 2016 11:10 AM

Thank you, Jeff. What I want to achieve is:

  1. interrogating the data via SQL queries, which I find more flexible/useful than what's possible with the Coveo reports (https://cloud.coveo.com/#usage/reports/); and
  2. interrogating the data alongside / in relation to data from other sources, such as Salesforce, which provides data not available through Coveo but relevant to our interests.

I have access to the Salesforce data directly within our SQL Server environment. While I can use the Coveo Exporting to download the Coveo data to CSV, then import that into SQL Server, I was hoping there might be a more direct route - or one that I could script so it's less manual. (See my comment in reply to Guillaume's answer below.)

2 Replies
Gravatar for gsimard@coveo.com

Answer by guillaume simard, Aug 11, 2016 10:51 AM

Is this something that you need to do regularly or only once?

If you only need to do this once, you could simply manually do an Export https://onlinehelp.coveo.com/en/cloud/exportinganalyticsdata.htm

You could then import the CSV into a SQL Server Database. There are various ways to do this such as http://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server

If you need to do this periodically, you'll need to get familiar with the API. I'd use the API to launch an Export, and then download the ZIP, extract it and push it into SQL Server. This is not trivial and will require some programming experience.

Gravatar for daniel.campbell@solarwinds.com

Comment by Daniel Campbell, Aug 11, 2016 11:10 AM

Thank you, Guillaume. I would be wanting to do this on a regular basis. I've already been using the Exports to get all the usage analytics data and then import the CSVs into the SQL Server db. I could continue that, but my instinct is to get to a more automated approach.

I have some programming experience - mostly with Unix shell scripts, somewhat with Python and R. I'm happy to learn something new - and tend to learn by trying to solve problems as I go - so if you can point me towards how best to get familiar with the API, and/or using your approach to getting the data into SQL Server, I'd appreciate it.

Gravatar for jrochette@coveo.com

Answer by jrochette, Aug 12, 2016 8:41 AM

Hi,

As Guillaume said, you need to use the API to do this. The call you.ll want to use is https://usageanalytics.coveo.com/docs/#!/v15%2Fexports%2F/export. The documentation should explain the parameters. Note that for optimal performances, incremental exports are the way to go (i.e. you don't need to re-export data that you have already exported).

Once you created the export, you can use the following API call to download it : https://usageanalytics.coveo.com/docs/#!/v15%2Fexports%2F/getExport. With the parameter "redirect" to false, you will get information on the export. Once the status is AVAILABLE, you can use the same call with "redirect" to true to download a zip file containing the different CSV files of the export.

After that, you need to import the data you want into your database. Since I am not familiar with SQL Server, I can't help you with that, but there are plenty of resources available online that should point you in the right direction.

Hope this helps, Jonathan

Gravatar for daniel.campbell@solarwinds.com

Comment by Daniel Campbell, Aug 12, 2016 9:59 AM

Thank you, Jonathan! That's helped me start figuring this out. I've worked out what to use to create an export, how to check the status, and what ought to then let me download the export. So far, I've just used the web interface (https://usageanalytics.coveo.com/docs/) to build and test each of those - as well as trying the built curl lines via Cygwin, since I'm familiar with curl.

But on the last step, after using redirect=false to confirm the export is AVAILABLE and then switching to redirect=true, I get what seems like an error:

Response Body no content Response Code 0 Response Headers { "error": "no response from server" }

Yet, when I look at https://cloud.coveo.com/#usage/export/, it's clear the export is there - and downloading results in the correct file. So why is the download request via the API web interface failing? (I also tested it via curl, and got nothing.) FYI: I authorized for the writeUA, readUA, and executeQuery scopes.

Gravatar for jrochette@coveo.com

Comment by jrochette, Aug 12, 2016 11:07 AM

It can't know for sure what is happenning on your side since I don't know what your setup is. The best advice I can give you is to use the --verbose option on curl to try and debug the request you are sending.

Also, since the download is actually a redicrect, you may need to tell curl to follow this redirect.

Ask a question