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.

    • Related Articles

    • Quick Reference Guide - Creating Custom Rules to allow Atria to populate email address attribute on provisioning in AD

      In this Quick Reference Guide, we will discuss on how to add 2 custom Provisioning Rules to allow the Atria portal to populate the email attribute to AD if the account don’t have Office365 1.1 Step by Step Guidance: Step Action 1. Set email address ...

    • 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 ...

    • How to configure Azure AD Custom Tenant Configuration Scripts

      Overview As part of the Atria Azure AD Service Sync Policies, you can assign custom configuration scripts that will be run each time the service is provisioned.  This is useful to ensure your customer's tenants are always configured with your ...

    • How to exclude Test Users from billing in Atria

      Overview In this article, we will show you how to create non-billable users for a customer. The idea to show non-billable users in Atria is to create billing rules in the service billing Configuration page, we will use a query to retrieve Test users ...

    • Atria v15+ System Requirements

      Overview The following article provides the prerequisites necessary for the deployment of Atria v15+ into your environment. System Requirements Environment Core servers for the platform should be domain joined. Before you can deploy Atria the Active ...