Last weekend I was thinking about how to save the tweets for PowerShell Conference Europe. This annual event occurs in Hanover and this year it is on April 17-20, 2018. The agenda has just been released and you can find it on the website http://www.psconf.eu/

I ended up creating an interactive PowerBi report to which my good friend and Data Platform MVP Paul Andrew b | t added a bit of magic and I published it. The magnificent Tobias Weltner b | t who organises PSConfEU pointed the domain name http://powershell.cool at the link. It looks like this.

During the monthly #PSTweetChat

Reminder that we do this chat the first Friday of every month from 1-2PM Eastern which I think is 6:00PM UTC #pstweetchat

— Jeffery Hicks (@JeffHicks) February 2, 2018

I mentioned that I need to blog about how I created it and Jeff replied

Yes, please. I'd love to setup something similiar for the PowerShell+DevOps Summit. #pstweetchat

— Jeffery Hicks (@JeffHicks) February 2, 2018

so here it is! Looking forward to seeing the comparison between the PowerShell and Devops Summit and the PowerShell Conference Europe

This is an overview of how it works

• A Microsoft Flow looks for tweets with the #PSConfEU hashtag and then gets the information about the tweet
• Microsoft Cognitive Services Text Analysis API analyses the sentiment of the tweet and provides a score between 0 (negative) and 1 (positive)
• Details about the tweet and the sentiment are saved in Azure SQL database
• A PowerBi report uses that data and provides the report

You will find all of the resources and the scripts to do all of the below in the GitHub repo. So clone it and navigate to the filepath

Create Database

First lets create a database. Connect to your Azure subscription

## Log in to your Azure subscription using the Add-AzureRmAccount command and follow the on-screen directions.
 Add-AzureRmAccount
## Select the subscription
Set-AzureRmContext -SubscriptionId YourSubscriptionIDHere

Then set some variables

# The data center and resource name for your resources
$resourcegroupname = "twitterresource"
$location = "WestEurope"
# The logical server name: Use a random value or replace with your own value (do not capitalize)
$servername = "server-$(Get-Random)"
# Set an admin login and password for your database
# The login information for the server You need to set these and uncomment them - Dont use these values
# $adminlogin = "ServerAdmin"                
# $password = "ChangeYourAdminPassword1"
# The ip address range that you want to allow to access your server - change as appropriate
# $startip = "0.0.0.0"
# $endip = "0.0.0.0"
# To just add your own IP Address
$startip = $endip = (Invoke-WebRequest 'http://myexternalip.com/raw').Content -replace "`n"
# The database name
$databasename = "tweets"
$AzureSQLServer = "$servername.database.windows.net,1433"
$Table = "table.sql"
$Proc = "InsertTweets.sql"

They should all make sense, take note that you need to set and uncomment the login and password and choose which IPs to allow through the firewall

Create a Resource Group

## Create a resource group
New-AzureRmResourceGroup -Name $resourcegroupname -Location $location

Create a SQL Server

## Create a Server
$newAzureRmSqlServerSplat = @{
    SqlAdministratorCredentials = $SqlAdministratorCredentials
    ResourceGroupName = $resourcegroupname
    ServerName = $servername
    Location = $location
}
New-AzureRmSqlServer @newAzureRmSqlServerSplat

Create a firewall rule, I just use my own IP and add the allow azure IPs

$newAzureRmSqlServerFirewallRuleSplat = @{
    EndIpAddress = $endip
    StartIpAddress = $startip
    ServerName = $servername
    ResourceGroupName = $resourcegroupname
    FirewallRuleName = "AllowSome"
}
New-AzureRmSqlServerFirewallRule @newAzureRmSqlServerFirewallRuleSplat
# Allow Azure IPS
$newAzureRmSqlServerFirewallRuleSplat = @{
    AllowAllAzureIPs = $true
    ServerName = $servername
    ResourceGroupName = $resourcegroupname
}
New-AzureRmSqlServerFirewallRule @newAzureRmSqlServerFirewallRuleSplat

Create a database

# Create a database
$newAzureRmSqlDatabaseSplat = @{
    ServerName = $servername
    ResourceGroupName = $resourcegroupname
    Edition = 'Basic'
    DatabaseName = $databasename
}
New-AzureRmSqlDatabase  @newAzureRmSqlDatabaseSplat

I have used the dbatools module to run the scripts to create the database. You can get it using

Install-Module dbatools # -Scope CurrentUser # if not admin process

Run the scripts

# Create a credential
$newObjectSplat = @{
    ArgumentList = $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)
    TypeName = 'System.Management.Automation.PSCredential'
}
$SqlAdministratorCredentials = New-Object @newObjectSplat
## Using dbatools module
$invokeDbaSqlCmdSplat = @{
    SqlCredential = $SqlAdministratorCredentials
    Database = $databasename
    File = $Table,$Proc
    SqlInstance = $AzureSQLServer
}
Invoke-DbaSqlCmd @invokeDbaSqlCmdSplat

This will have created the following in Azure, you can see it in the portal

You can connect to the database in SSMS and you will see

Create Cognitive Services

Now you can create the Text Analysis Cognitive Services API

First login (if you need to) and set some variables

## This creates cognitive services for analysing the tweets
## Log in to your Azure subscription using the Add-AzureRmAccount command and follow the on-screen directions.
Add-AzureRmAccount
## Select the subscription
Set-AzureRmContext -SubscriptionId YOUR SUBSCRIPTION ID HERE
#region variables
# The data center and resource name for your resources
$resourcegroupname = "twitterresource"
$location = "WestEurope"
$APIName = 'TweetAnalysis'
#endregion

Then create the API and get the key

#Create the cognitive services
$newAzureRmCognitiveServicesAccountSplat = @{
    ResourceGroupName = $resourcegroupname
    Location = $location
    SkuName = 'F0'
    Name = $APIName
    Type = 'TextAnalytics'
}
New-AzureRmCognitiveServicesAccount @newAzureRmCognitiveServicesAccountSplat
# Get the Key
$getAzureRmCognitiveServicesAccountKeySplat = @{
    Name = $APIName
    ResourceGroupName = $resourcegroupname
}
Get-AzureRmCognitiveServicesAccountKey @getAzureRmCognitiveServicesAccountKeySplat

You will need to accept the prompt

Copy the Endpoint URL as you will need it.Then save one of  the keys for the next step!

Create the Flow

I have exported the Flow to a zip file and also the json for a PowerApp (no details about that in this post). Both are available in the Github repo. I have submitted a template but it is not available yet.

Navigate to https://flow.microsoft.com/ and sign in

Creating Connections

You will need to set up your connections. Click New Connection and search for Text

Click Add and fill in the Account Key and the Site URL from the steps above

click new connection and search for SQL Server

Enter the SQL Server Name (value of $AzureSQLServer) , Database Name , User Name and Password from the steps above

Click new Connection and search for Twitter and create a connection (the authorisation pop-up may be hidden behind other windows!)

Import the Flow

If you have a premium account you can import the flow, click Import

and choose the import.zip from the Github Repo

Click on Create as new and choose a name

Click select during import next to Sentiment and choose the Sentiment connection

Select during import for the SQL Server Connection and choose the SQL Server Connection and do the same for the Twitter Connection

Then click import

Create the flow without import

If you do not have a premium account you can still create the flow using these steps. I have created a template but it is not available at the moment. Create the connections as above and then click Create from blank.

Choose the trigger When a New Tweet is posted and add a search term. You may need to choose the connection to twitter by clicking the three dots

Click Add an action

search for detect and choose the Text Analytics Detect Sentiment

Enter the name for the connection, the account key and the URL from the creation of the API above. If you forgot to copy them

#region Forgot the details
# Copy the URL if you forget to save it
$getAzureRmCognitiveServicesAccountSplat = @{
    Name = $APIName
    ResourceGroupName = $resourcegroupname
}
(Get-AzureRmCognitiveServicesAccount @getAzureRmCognitiveServicesAccountSplat).Endpoint | Clip
# Copy the Key if you forgot
$getAzureRmCognitiveServicesAccountKeySplat = @{
    Name = $APIName
    ResourceGroupName = $resourcegroupname
}
(Get-AzureRmCognitiveServicesAccountKey @getAzureRmCognitiveServicesAccountKeySplat).Key1 | Clip
#endregion

Click in the text box and choose Tweet Text

Click New Step and add an action. Search for SQL Server and choose SQL Server – Execute Stored Procedure

Choose the stored procedure [dbo].[InsertTweet]

Fill in as follows

• __PowerAppsID__         0
• Date                                 Created At
• Sentiment                      Score
• Tweet                              Tweet Text
• UserLocation                 Location
• UserName                      Tweeted By

as shown below

Give the flow a name at the top and click save flow

Connect PowerBi

Open the PSConfEU Twitter Analysis Direct.pbix from the GitHub repo in PowerBi Desktop. Click the arrow next to Edit Queries and then change data source settings

Click Change source and enter the server (value of $AzureSQLServer) and the database name. It will alert you to apply changes

It will then pop-up with a prompt for the credentials. Choose Database and enter your credentials and click connect

and your PowerBi will be populated from the Azure SQL Database This will fail if there are no records in the table because your flow hasn’t run yet. If it does just wait until you see some tweets and then click apply changes again.

You will probably want to alter the pictures and links etc and then yo can publish the report

Happy Twitter Analysis

Dont forget to keep an eye on your flow runs to make sure they have succeeded.