PowerBI – Using FreshDesk Web Services API

November 7, 2018

We started exploring the use of the FreshDesk cloud-based customer support platform.

Which is one of the modules of the FreshWorks Suite.

You can start off with a free subscription, which is good enough to start building you BI tool.

image

Once you are happy you can switch over to a paid subscription giving you extra features.

image

One of them is extensive reporting,

After exploring the Power BI integration. I found out that all of the third party solution provided did not work Sad smile

And one if the solutions required that you have a Power BI Pro licence…

SOLUTION :

So I ended up making my own Power BI Dashboard using the FreshDesk REST Web Services API.

See here for the API documentation : https://developers.freshdesk.com/api/

The Web Service API looks very straight forward and works with any REST compatible application.

Implementing this in Power BI, was challenging.

But as you can see here, you can do all you need in your Free Power BI Desktop too.

So this will save you some licenses money too Winking smile

You can start off with a free subscription, which is good enough to start building your BI Dashboards.

image

image

Shows that Power BI is the right marriage for the 2 platforms, O365 and FreshDesk Winking smile

In case you need to run this as offline data source or want to schedule reports.

You can use PowerShell to extract the data, and use this as a data source.

This will save you quite some money uplifting your subscription.

image

Example :

If you have 25 Agents and you need to schedule reports. Your subscription is $19 / user / month more expansive.

This is a burden of an additional $5,000 subscription cost.

Other paid service provider is FreshInsights / Radiare

image

PowerShell

If you run the Invoke-WebRequest command against your URI in Powershell.

Like this :

$Data = Invoke-WebRequest -uri $URL -Headers $Headers -Method GET -ContentType application/json 

image

You can see in the response header information the API Version and the remaining calls you have

In this case 2996 out of 3000 per hour !

This is a limit set by Freshdesk to reduce the maximum API calls per hour.

Steps to get started using Power BI using the Web Service API :

1. create a free trial subscription

2. Copy your Web Service API Key

image

3. Open Power BI and do a Get DATA Web Data

image

image

Paste in the web service URL for Tickets for example.

4. Connect to FreshDesk using the API Key and password

image

PS : To use the Power BI Service and Scheduled REFRESH,

        you need to use the Anonymous Authentication !

5. Next built your Queries and transformations to extract the data

image

6. Build your Visuals and Measures

image

Tips and Tricks

An EXTRA Bonus is that you can incorporate this in your SharePoint Online site.

No license needed see here on how to.

image

Schedule a Refresh using Power BI Service

Recently Microsoft changed the Power BI service policy.

Where you can’t schedule a refresh when using a public web service ?

image

But no worries there is a solution for this too Winking smile

image

Need to get started building your own interactive Dashboard, drop a message here.


PowerShell – Monitor MS Azure Status

February 22, 2015

If you are using MS Azure or MS Office 365 you are depending on the uptime of all the MS Cloud services.

Well to monitor this there is the Azure Status site.

image

This site offers for each service a RSS subscription to monitor the status.

In this case there was an issue, so we can kick in Powershell to grab the RSS output.

Here we go

 

CLS

$hsg = Invoke-WebRequest  "http://azure.microsoft.com/en-us/status/feed/"

$hsg.Content

[xml]$ret = $hsg.Content.TrimStart("")

#$ret.rss.channel.item | Select-Object *

write-host ""



if($ret.rss.channel.item.category -eq $null){

        write-host $ret.rss.channel.title " On : "  $ret.rss.channel.pubDate
        write-host "All services are working properly"
        write-host ""
    }
Else
    {

    write-host $ret.rss.channel.title " On : "  $ret.rss.channel.pubDate
    Write-Host "Issue Category : " $ret.rss.channel.item.category
    write-host ""
    Write-Host "Details : " $ret.rss.channel.item.title

    $ret.rss.channel.item.description
    write-host ""

}

rv hsg, ret
image

As you can see the raw XML data is prefixed (deliberately or not?) by a few strange characters which we have to eliminate before PowerShell can dig it.

So the final output is like this.

image

So you can create a job to have this run at a frequency to check for uptime issues.

Enjoy!


PowerShell – Export HTML content to CSV or Excel

August 22, 2014

Let’s say you are interested in downloading some HTML content from a website based on some filters.

PowerShell it the tool to use. It just takes a few lines Smile

let’s take this blog as an example.

I would like to get hold of all Title Posts of the category ‘PowerShell’

The URL for this is this https://audministrator.wordpress.com/category/Sharepoint/

If you run it in a browser you get all the post content listed. But we need only the Title ?

Therefor we need to use the DOM (Document Object Model). In order to find what we need.

Here we go :

Open up the IE browser and press F12. This will open up the DOM explorer.

In the right corner fill in a search key word of a post Title

image

Next inspect the section needed.

Next we are going to need a DOM method to access the data, in this case.

getElementsByTagName = “h2”

Next I wanted to get the text of the Title.

So we can use the method

innerText

here is the coded plus some in between debugging information for you to get more information out of this example.

CLS

$URI ="https://audministrator.wordpress.com/category/powershell/"
$HTML= Invoke-WebRequest -uri $URI

# echo $HTML

# $HTML | Get-Member

# $HTML.ParsedHtml | Get-Member

<#
Check out these Members

getElementById
getElementsByName
getElementsByTagName
#>

$Ret = $HTML.ParsedHtml.getElementsByTagName("h2" #| Where { $_.className  }
echo $Ret.innerText

$Ret = $HTML.ParsedHtml.getElementsByTagName("h2" | Where { [int]$_.className.trim.length -eq 0  } | % { ([String]$_.sourceindex + " " + $_.innerText) }
$Ret | Select-Object @{Name='Name';Expression={$_}} | Export-Csv ($Env:USERPROFILE+"\Desktop\Test.csv") -NoTypeInformation

Invoke-item ($Env:USERPROFILE+"\Desktop\Test.csv")

echo $Ret
echo

Next I got a bit too much information. As you can see the last items are not post but are the headers of the right widgets.

image

So we have to filter them out. See code

className.trim.length -eq 0

image

Finally we got what we wanted. And we can now export it to a CSV file if we want.

image


PowerShell – Invoke-RestMethod Error !

July 14, 2014

When running the PS Invoke-RestMethod, I got the connection closed Error ?

Invoke-RestMethod : The underlying connection was closed: An unexpected error occurred on a send.

image

Looking on the net there are a lot’s simular posts coming up, providing solutions that had to do with avoiding certificates validation checks when running the script ?

like adding this to the script :

add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@

[System.Net.ServicePointManager]::CertificatePolicy = ´
                        New-Object TrustAllCertsPolicy

Or

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}

But, none of them worked out well?

So the solution is here :

When specifying an URI for the Invoke-RestMethode, don’t pre-fix it with HTTP:// or HTTPS://

Example :

Invoke-RestMethod -Uri "gdata.youtube.com/feeds/api/videos?v=2&q=PowerShell" `
| Select-Object Title, Author, Link

Result :

image

As simple as that Winking smile


Programming skills are not the same as Technical skills ?

July 1, 2014

Being a Developer does not require that you have good Technical and Protocol skills alone these days.

First of all you need to master many Technologies, that are best fit to support the Business scenario’s and requirements.

Technologies in a Windows Environment are piling up each year.

You should know what is :
WPF Windows Presentation Foundation
MCF Windows Communication Foundation
WWF Windows Workflow Foundation
– WIF Windows Identity Framework
– WMF Windows Management Framework
WSE Web Service Enhancements
– WEF Windows Entity Framework

Depending if the requirement is a Client Side application or a Web Based Server Side Business Solution.
You would need to pick either one of them or even a mix of both.

You would at least need to learn what CLR (Common Runtime Language) is. And what is IL- CLR- CTS-CLS-and-GIT.

Or DLR is not the same as CLR

Maybe a bit too much all together for scripters. Confused smile

But let’s have a quick overview of which kind of Technologies we can pick from.

.NET (versions 1 to 4.x) versus Java(Script)

Application Architecture
Object Oriented Programming
Service Oriented programming

Web Oriented Architecture
XML                      – JSON
* XML/A                  – AJAX
* CAML                    – DOM
* XAML (mobile)
– CSS (3)               – SOAP
– HTML (5)             – REST
Java(Script)         – Web API / ASMX

Enterprise Data Architecture
SQL (AZURE)
OLAP
Odata
OLEDB / ADO.NET
LINQ
SQL / MDX

Server Side Technology
AD
ASP.Net
IIS
SharePoint

Client Side Technology
Powershell
Webmatrix / Razor
SQLite.Net / LDF databases

Programming Technology
.Net Framework
* Assemblies / Classes / Objects
* .Net Reflection (late binding)
* .Net Reflection.EMIT ?
* Model View Controller (MV)

– JavaScript Libraries

* SP.Core.js
* SP.js
* JQuery.js
* Node.js
* KnockOut.js
* Angular.js
* …

Most of these Technologies you can access using smart Scripting Techniques.

I am trying to give some examples on in this Blog whenever possible.

Bottom line is that in this ever changing IT world keeping up and learning new Technologies is a necessity.

For some amongst us this is fun for others this is a real challenge 🙂