Having worked with customers for many years deploying Citrix App Layering, I know additional reporting features are in high demand. I have worked on several ways to produce custom reports that I will walk through in this blog post. I will also provide a couple of the most asked for reports, as examples, that can be used immediately in all Citrix App Layering environments.

First, the Console

Let’s start with understanding what the Citrix App Layering console provides in terms of information. When you open the new console, you are presented with a menu on the left that allows you to manage Images, Layers, and Tasks.

If you open the Images node and select an image, you can view details about the image.

This includes information about the image formats and connectors in the “Template Information” tab.

But more importantly, information about the included Layers and the priority of those Layers within the image is shown on the “Layer Assignment” tab, where the OS Layer is always the lowest priority, and the Platform Layer is always the highest priority.

Similarly, if you open the Layers node, you can view detailed information about a Layer, including the size, ID, Operating System (for OS layers), OS Layer, and Image Templates that have the Layer included.

If you choose the version information, you can see the current versions and get more detailed information about each version, including the Date Created, Last Modified By, Layer Size, Image Template Assignments, and Elastic Assignments.

You can also find the Elastic Fit details for each version here, which helps you understand if a Layer can be used elastically.

As you can see, there is a wealth of information available in the console. However, that does not mean it is always in the desired format. The most common reporting requests I have seen include:

  • A Layer report to show all current layers and their versions with associated information
  • Elastic Layer Assignments
  • Layer Size
  • Layer Dependencies
  • Layer Priority
  • Admin Auditing Log Report

For the rest of this blog post, I am going to show how you can make your own reports with a bit of scripting and ingenuity. I will also post examples of a Layer Report and Audit Log Report.

Creating a “Layer Report”

As an example, I have created a Layer Report that lists all of the Layers and the information associated with the layers. I used html output to make the report easy to read. Of course, if you would prefer a set of CSV files, you could modify the report to output several CSV files — one for each layer type.

The information included is broken up into

  • OS Layers
  • Platform Layers
  • Application Layers (Not assigned Elastically)
  • Elastically Assigned Application Layers

The script uses direct queries against the App Layering Appliance database using “plink.exe,” which is an open-source utility that allows SSH scripting against remote machines. Get plink here. Look for “Download it here” near the top of the page.

When I create a new report, I start by looking through the SQL schema to find the information I am looking for. I use a utility called SQLyog that allows me to use a GUI against the remote MySQL database. You can also SSH into the appliance and run MySQL commands in the console.

Looking through the available tables I notice right away that there is a table for layers. However, I notice the layers table does not have any Layer Version information.

I see that there is also a LayerRevision table, which has the fields for versions.

One other piece of information I wanted to include was Elastic Layer Assignment. Elastic Layers are assigned to Layer Versions. I see that there is an Elastic Assignment table that can be joined to the LayerRevision table using the LayerId. Also, the ElasticAssignment table has an index to the ADItentity table to match user and groups that have been assigned to Elastic Layers, so we will need a join to the ADItentity table, as well.

Another nice thing about SQLyog is that you can create queries using the GUI, which is much easier than figuring out the syntax manually. This is the schema for the tables we are joining together.

The SQL for this becomes:

SELECT
`Layer`.`layType`,
`Layer`.`layName`,
`Layer`.`layDescription`,
`Layer`.`layDateCreated`,
`Layer`.`layPriority`,
`Layer`.`layScriptPath`,
`Layer`.`layGuid`,
`LayerRevision`.`lrName`,
`LayerRevision`.`lrWhoLastEdited`,
`LayerRevision`.`lrDiskFileSize`,
`LayerRevision`.`lrRevision`,
`LayerRevision`.`lrDateCreated`,
`LayerRevision`.`lrDateLastModified`,
`LayerRevision`.`lrExported`,
`LayerRevision`.`lrGuid`,
`AdEntity`.`adentName`
FROM
`MAData`.`LayerRevision`
LEFT JOIN `MAData`.`Layer`
ON (
`LayerRevision`.`lrLayerId` = `Layer`.`layId`
)
LEFT JOIN `MAData`.`ElasticAssignment`
ON (
`ElasticAssignment`.`eaCurrentRevId` = `LayerRevision`.`lrId`
)
LEFT JOIN `MAData`.`AdEntity`
ON (
`ElasticAssignment`.`eaAdEntityId` = `AdEntity`.`adentId`
)
ORDER BY `Layer`.`layType` DESC,
`Layer`.`layName` ASC,
`LayerRevision`.`lrName` ASC,
`AdEntity`.`adentName` ASC;

Now that we know the SQL, we can use it in a PowerShell script.

Scripting MySQL in PowerShell

There are several important parts of the PowerShell script. The first is performing the query against MYSQL. First, we need to slightly convert the format of the SQL query we got from SQLyog so it will work in PowerShell. Note that for every tick (“`”) mark, we have to use two.  That is because the tick is a special character in PowerShell that is used to escape other characters. So, the double tick is actually escaping the tick character itself.

Here we are adding our entire SQL query into a variable called $strScript because we will pass that to the appliance using SSH.  To accomplish that, we save this text to a file, then create a plink.exe command line to run plink sending the MySQL cmdline to the appliance.

What we will get back is the output of the query put into the variable $AllLayerOutput. The script then reads this output into a table and uses the table to format the output as html.

For the look of the HTML, you will see around line 300, a section for CSS settings. This can of course be adjusted to your preferences for color and size, etc.

Audit Log Report

I am asked a lot how to integrate the appliance with SIEM solutions like Splunk. It is theoretically possible to change some of the App Layering logs to write to a syslog server but it is difficult, does not work for most of the important logs, and is often reset during an upgrade. The best way to integrate the appliance logs is to export the log bundle and then script downloading those logs via sftp. However, if you just get the logs, you will miss a very important log, the App Laying Audit log, because that is not in a file, it is in the App Layering database.

As in the Layer Report example, you can use the same scripting techniques to capture the Audit log. In this script you must similarly define the desired settings at the top of the script at approximately line 42.

#----------------------------------------------------------------
#  MAIN SCRIPT
#----------------------------------------------------------------
$title = "Citrix App Layering Audit Report"#Enter Name of CSV file but omit the .csv
$CSVFileName = "AppLayeringAuditLog"
$AlwaysGetFullLog = $False
$ApplianceFQDN = "ApplianceFQDN"
$ApplianceRootPassword = 'password'

This includes the csv file name without “.csv” at the end and a flag $AlwaysGetFullLog, which tells the script whether to get the full log every time or just the new entries since the last time it was run. This is handled using a simple WHERE clause in the SQL as shown below:

#Where clause depends on whether we will always get the full log or remember the last entry
if ($AlwaysGetFullLog)
{
$strScript += "WHERE (``LogEntry``.``leId`` > 1);"
}
else
{
$strScript += "WHERE (``LogEntry``.``leId`` > $LastRecordId);"
}

In this script, I made the appliance root password a variable because it is likely that this script would be automated. I have not gone to great pains to store this password encrypted.

If you want to store the password safely, you can add the credential into the Windows credential manager (on Windows 10) using the PowerShell Module available here. This will store the credential in the Windows Credential Manager so that only the user that stored it has access to it.

To use it you would first save a credential using:

Install-Module CredentialManager
Import-Module CredentialManager
$creds = Get-Credential -UserName root
New-StoredCredential -Credentials $creds -Target AppLayeringAudit

Then in the script add

$ApplianceRootPassword = Get-StoredCredential -Target AppLayeringAudit
$credPassword = ApplianceRootPassword.GetNetworkCredential().Password

Remember that if you then run the script in a scheduled task, you need to run it as the user that saved the credential on the machine where the credential was saved.

This script creates a CSV file rather than an HTML report.  If HTML would be desired for your use, you can merge the example scripts together to get the format you desire.

Image Template Report (Added 7-15-2022)

With the introduction of the new HTML 5 UI layer priority is no longer shown properly in the image templates. I was asked by several customers for  a report listing each image template with its associated layers, layer priority and  layer versions.  So I added a report for this to the blog download called ImageVersions.ps1.

Using the Example Scripts

After downloading the script zip remember to “unblock” it in the file properties or the scripts will be blocked by Windows. You will also need to make sure your PowerShell Execution Policy is set to “Remote-Signed” because these scripts are not signed.

You will need to download the latest version of plink.exe, unblock it and put it in the same folder as the script.  Also, to use plink against the appliance, you must connect once using putty.exe to save the host fingerprint.

Download the scripts here.

This software application is provided to you “as is” with no representations, warranties or conditions of any kind. You may use and distribute it at your own risk. CITRIX DISCLAIMS ALL WARRANTIES WHATSOEVER, EXPRESS, IMPLIED, WRITTEN, ORAL OR STATUTORY, INCLUDING WITHOUT LIMITATION WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NONINFRINGEMENT. Without limiting the generality of the foregoing, you acknowledge and agree that (a) the software application may exhibit errors, design flaws or other problems, possibly resulting in loss of data or damage to property; (b) it may not be possible to make the software application fully functional; and (c) Citrix may, without notice or liability to you, cease to make available the current version and/or any future versions of the software application. In no event should the code be used to support of ultra-hazardous activities, including but not limited to life support or blasting activities. NEITHER CITRIX NOR ITS AFFILIATES OR AGENTS WILL BE LIABLE, UNDER BREACH OF CONTRACT OR ANY OTHER THEORY OF LIABILITY, FOR ANY DAMAGES WHATSOEVER ARISING FROM USE OF THE SOFTWARE APPLICATION, INCLUDING WITHOUT LIMITATION DIRECT, SPECIAL, INCIDENTAL, PUNITIVE, CONSEQUENTIAL OR OTHER DAMAGES, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You agree to indemnify and defend Citrix against any and all claims arising from your use, modification or distribution of the code.