Monthly Usage Reporting to Automate101 - Powershell Script

Monthly Usage Reporting to Automate101 - Powershell Script


We have heard from several customers who have been looking for an easier way to get their Atria license reporting out of Atria.

Although there is a distributor report within the system, it seems that

  1. In many deployments this is not functional, and
  2. It does not determine whether or not the bundled “Cloud Complete” SKU is a better cost option.

We have now written a Powershell script which will extract your user-based license usage. This also determines whether or not the cloud complete bundle SKU should be used.

The process retrieves the services assigned to each user and aggregates a weighting (cost) against each SKU, if the cost for that user exceeds the cost of the cloud complete SKU, all of the user's individual licenses are swapped for the CloudComplete SKU. This is aggregated and then listed.

The process excludes the following objects:

  1. any users flagged as Test
  2. any users flagged as Template Users
  3. any users in a de-provisioned state
  4. any services in a de-provisioned state.
Please email or log a ticket if you have issues with this process and we can provide assistance.

Applies To:

  1. CloudPortal Services Manager V11.x
  2. CloudPortal Services Manager V11.5.x
  3. Atria V12.x


There is a .zip file attached to the bottom of this article.
Download this file onto any server in your environment that has access to the SQL server containing the Atria databases
Make sure to check the file to see if it is blocked before extracting the contents
Extract the .zip contents, then execute it via PowerShell using the Execution guidelines below


      [-ServerName <ServerName>]
      [-Instance <SQL Instance>]
      [-Port <SQL Port>]
      [-Threshold <weighting threshold>]

      [-ServerName <ServerName>]
      [-Instance <SQL Instance>]
      [-Port <SQL Port>]
      -SQLUsername <Username>
      -SQLUserPassword <Password>
      [-Threshold <weighting threshold>]


Retrieve Usage Stats with all default parameters.  This will connect to the default instance of SQL on CortexSQL using Windows Authentication
  1. .\GetUsageStats.ps1

Connect to the database server on Server02 using a non-standard instance of Atria over port 52681, and connecting with SQL Authentication
  1. .\GetUsageStats.ps1 -ServerName Server02 -Instance Atria -Port 52681 -SQLUsername AtriaUser -SQLUserPassword 'D!ji94bG.St'

NOTE:  A log file is generated which will give a more detailed view of which users were and were not included in the Cloud Complete SKU count
This log file is stamped with the date that the script was executed and is named "{dateofexecution}_UsageDetails.log"

    • Related Articles

    • License Reporting Process

      Overview This guide describes how Service Providers should report their Atria License Usage as per their customer services agreement with Automate101 Ltd. Click here for the License Reporting Portal URL. Getting started When your company signs up ...
    • PowerShell based batch processing with the Atria Job Runner

      Scheduling PowerShell Jobs Applies to Atria 15.2 or later Purpose PowerShell is a popular and powerful scripting language that is well known by  Microsoft administrators.   Atria has the ability to schedule PowerShell jobs that can be centrally ...
    • Security Roles - Get List of Security Roles by User

      Overview This article contains a SQL scripts to generate a report to get a list of admin users by security role declared. We additionally have a script that notes down if they have the Atria MFA service enabled. This solves the problem for ...
    • Connecting Atria to Microsoft Partner Center

      Objective This article shows you how to set up a secure connection to Microsoft Partner Center which will be used for Atria to automate processes in Partner Center.  The process itself should take less than 5 minutes to complete. Note: Before Atria ...
    • How to Extend Automation of Microsoft Online Services

      Overview Since version 12.0, Atria has provided the capability to allow easy management of Azure AD and User-based subscriptions (such as Microsoft 365 and Office 365).  As a CSP provider operating at scale, you may have additional configuration ...