How-To - Create Custom SQL Reports and email them to an address

How-To - Create Custom SQL Reports and email them to an address

Overview

Atria's Database has a lot of Customer, User and Service Data stored, but it's not feasible  to provide all staff SQL Server access to perform SQL Queries to get relevant data out of Atria, or, if a Customised report is required for a customer, you cannot provide them this access and it becomes a manual task.

This guide will overview how to get SQL data via PowerShell, Transform this data then send it via email. This can then be integrated with our Jobs functionality added in V15 or can be created as a Scheduled Task, or carried out via another Script Automation tool your company may use.

Applies to

Atria v12.13.x  and above
For previous versions, please contact Atria Support to assist with the script to work on your version.

Requirements

A SMTP Server to relay the email message through (Any which is supported by the Send-MailMessage cmdlet (Send-MailMessage (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs)

Procedure

Craft your SQL Query, We've got a few examples below, but you will need to create a SQL Query that can get the data you require from OLM, whether it be by a stored procedure, or just a generic Query, as long as you can run this against Atria and get data back, we can use this down the line.

First, download the "Shell Script" we've got attached to this document. This does the following, but is easy to modify as needed.

1. Gets Todays Date (Used later in Script for File Names), additionally, you will need to add the 'CustomerID" of the relevant customer who needs the report.
2. FilePath of where the export will be stored
3. Gets the OLM (Atria DB) Connection String to export data
4. Retrieves the Billing Period from Atria - This means the last time the Billing scheduled task was ran.
5. A SQL Command to retrieve data from Atria - In this example, it's a stored Procedure to get the last Bill Items
6. Invoking the SQL Command in Step 5 to retrieve the data from SQL
7. Format the results to something our client would want
8. Email it to a user

Examples

Export Azure Sync Log Reports

We've written an additional guide on this one specifically - 

Export a customers userlist who consume Citrix      

See the attached PowerShell Script to this KB (Export_CustomerCitrixUsers)


Other Example or you'd like assistance? Please contact our friendly support team to assist you in speeding up your manual workflows.