Home > PowerShell Scripting, Workspace Manager > Reporting RES Workspace Manager realtime licensing with PowerShell.

Reporting RES Workspace Manager realtime licensing with PowerShell.

This is an old script I use internally but I had somebody ask me how I was doing it at Citrix Synergy. I figured if they needed it why not share the script.

Finance departments love interval based licensing reports, to get snapshots of usage as the day goes by. From this they like to draw trends and watch out for closing in on capacity.

We could get this reporting quite easily with Citrix’s LMSTAT.exe utility but RES did not offer a similar product. A quick support call later, the following SQL statement was shared with me to report committed licenses:

"select count(strLicenseType) as LicenseCount from dbo.tblLicenses where strLicenseType ='HARD'"

Using the above command as an SQL query will return the currently in use licenses from your Workspace Manager Database.

Taking this a step further, we needed to retrieve this number every 15 minutes and report to excel. I used powershell to achieve this with the least administrative work. Below is the Powershell function I wrote to achieve this task. This script connects into the database you specify, runs the sql query and returns the license count:

Function get-RESlicensecount{
    param(
        [Parameter(Mandatory = $true)]
          [String]$server,
          [Parameter(Mandatory = $true)]
          [String]$database,
          [Parameter(Mandatory = $true)]
          [String]$username,
          [Parameter(Mandatory = $true)]
          [String]$password
    )#end param

    $SqlQuery = "select count(strLicenseType) as LicenseCount from dbo.tblLicenses where strLicenseType ='HARD'"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$Server; Database=$database; user id=$username ; Password=$Password ; Trusted_Connection=False"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) | out-null
    $SqlConnection.Close()
    $result=($dataset.tables[0] | select-object licensecount).licensecount
    Return $result
}

get-RESlicensecount -server "SQLServerName" -Database "DatabaseName" -username "Username" -password "Password"

The output of the command should simply list the number of committed licenses for you to report / ammend as you wish:

Drop me a comment if you need the additional excel component. It’s much longer and fairly unique to our implementation.

  1. JustMe
    June 12, 2012 at 8:59 pm

    Hi do you have more powershell reports for res workspace manager ?

    • June 12, 2012 at 9:01 pm

      Not currently, but love a challenge! What would you like to report on?

  2. Jochem Langerak
    October 6, 2012 at 10:54 pm

    Bit late to comment, but: I am searching for a report with application title, description, owner, license type, etc… Best Regards, Jochem

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: