A Couple of Useful Environment Manager SQL Queries

In Support there are a number of SQL queries that we use as a base for querying Environment Manager User Virtualization data when troubleshooting.  Typically these are cases of slow logon/logoff and application start/stop or large databases.  The queries allow a quick overview of files and folders that have been captured, which means they're also useful for a Systems Administrator to get a better understanding of what files are being captured.

Over a few blogs we’ll cover a couple of these queries and variants of them.  If you’re not overly familiar with SQL, Microsoft SQL Server comes with a good Query Editor GUI.  As the queries are already built, you can use the GUI to change the criteria and sort order to return the results you're interested in.  I'll cover some of the basics so it's possible to get up and running and start getting a better overview of what is captured.  The results can be exported in CSV format for further refining in Excel if required.

If you're already familiar with SQL, feel free to just copy the SQL and start playing.  Be aware that these queries report on the application data captured.  Without a sufficient WHERE clause the query could run for a while and have a detrimental effect on the SQL server performance.

Support is occasionally asked if we provide a Data Dictionary.  Although there isn't a formal Data Dictionary document, the "AppSense Environment Manager Personalization Server API" guide which comes with the product documentation, provides a description of each table column under the "API Classes" section.

Application Profile

Let's start with a basic query to review Application Profile information, without the individual application data rows.  This is a useful query to determine the top 10, 100, 1000 Application Profiles by profile size.  Once these have been determined, the query can be expanded to review the Application Data to see what is consuming the space.  The Personalization Analysis tool offers the same functionality at the Personalization Group and User level.  Using a SQL query allows focusing on a particular application.

The query below finds the top 100 Application Profiles for the application “AnApp” that are larger than 5MB in size, sorted by Stored Size.

Start by opening the Microsoft SQL Server Management Console on the SQL Server.  If you don't want to be continually working on the SQL Server, the console can be installed from the original SQL Server install media or downloaded from Microsoft as a separate download.

Within the toolbar click on "New Query" and then select the Personalization Database from the available database drop down menu.  Paste in the query below into the new query window:

[code lang="sql"]
SELECT TOP(100) UG.Name AS PersonalizationGroup, U.Name AS UserName, Apps.Name AS AppName, AP.StoredSize As StoredSizeBytes
FROM ApplicationProfile AS AP
INNER JOIN [User] AS U ON AP.UserFK = U.UserPK
INNER JOIN UserGroup AS UG ON AP.UserGroupFK = UG.UserGroupPK
INNER JOIN (SELECT Application.ApplicationPK,Application.Name FROM Application
UNION ALL
SELECT ApplicationGroup.ApplicationGroupPK,ApplicationGroup.Name FROM ApplicationGroup
UNION ALL
SELECT ApplicationReserved.ApplicationReservedPK,ApplicationReserved.Name FROM ApplicationReserved
) AS Apps ON AP.ApplicationFK = Apps.ApplicationPK
WHERE Apps.Name = N'AnApp'
AND Ap.StoredSize >= 5242880
ORDER BY StoredSize DESC, PersonalizationGroup, UserName, AppName
[/code]

Before using the Query Editor GUI, there are a couple of things worth noting about the query if you're not familiar with SQL.

• The TOP (100) returns only the top 100 rows.  To update this value either manually update the query or right click in the Query Editor window and select Properties.  The TOP clause can be added and modified under the Top Specification.  Within the Top Specification you can see that TOP is quite versatile, you’re not just restricted to the TOP (X) rows but you can return the TOP (X)PERCENT.  So how do you get the BOTTOM(X) rows?  Change the sort order in the ORDER BY clause.  TOP is useful when testing queries to ensure thousands of results are not returned or for just focusing on the applications that require immediate attention
• The tables are aliased to reduce the size of the queries.  The alias can be seen immediately after the table within the FROM clause e.g. User AS U, UserGroup AS UG etc.  Within the Query Editor the tables appear as the alias name

Select all the text within the query window, right click and select "Design in Query Editor...".  It's important to select the query you wish to edit, it's an easily overlooked step but the Query Editor will only edit the text that is selected.  This is a useful feature as it will allow you to edit nested queries and individual queries within a larger script.

The Query Editor makes it easy to add and remove columns by simply ticking and unticking.  Define how the results are sorted and what filters are applied.  A few simple changes and its possible to change the nature of the report.

The original query appears as follows:

Using the same query but making changes to the WHERE clause and the sort order allows you to change the nature of the report.  Let’s change the query to report on Application Profiles that have not been modified since 20th October 2014.  This may help you determine how much obsolete data you have captured.  Within the Query Editor:

• Change the “Stored Size” “Sort Order” column to “Unsorted”
• Tick the "Application Profile" (AP) table "ModifiedDate"
• Add a filter on the ModifiedTime to be: <='20/10/2014'
• On "ModifiedDate" set the "Sort Order" = 1 and "Sort Type" as Descending
• Remove the filter on AppName

Click on OK to exit the Query Editor and click on Execute to run. For this report, consider increasing the TOP value if required.

The QueryEeditor also fixes the format of the WHERE clause. The most obvious fix is the ModifiedTime column which is changed to:

[code lang="sql"]WHERE (AP.ModifiedTime <= CONVERT(DATETIME, '2014-10-20 00:00:00', 102))[/code]

The same is the case for string values.   Entering "AnApp" without the " " into the AppName filter column and the Query Editor will correctly fix as by appending N and wrapping the text in ‘ ‘.  Note the "N" represents that the field is a Unicode string.  As the column is defined as nvarchar(50).  A quick search will return plenty of websites describe the difference between nvarchar and varchar.  But the Query Editor automatically fixes the query for you.

[code lang="sql"]WHERE Apps.Name = N'AnApp'[/code]

Application Data

The results from the first query show that there are a number of Application Profiles for the Application Group “AnApp” that are over 20MB in size.  As these need to be synchronized from the Personalization Server on Process Start and synchronized back on Process Stop, they might be worth further investigation to see if the data captured can be optimized further.

To get more information about what files are captured by adding the ApplicationData table to the Application Profile query.  It's worth noting:

• ApplicationData contains all files captured (but not the archives), therefore it's advised to use WHERE clause to limit the results and impact on the SQL server
• Don't add the "Data" column for reporting purposes as this contains the file captured

[code lang="sql"]
SELECT TOP(1000) UG.Name AS PersonalizationGroup, U.Name AS UserName, Apps.Name AS AppName, AP.StoredSize, AD.RelativePath, AD.Filename, AD.Size AS AppDataUnCompressedSize
FROM ApplicationProfile AS AP
INNER JOIN [User] AS U ON AP.UserFK = U.UserPK
INNER JOIN UserGroup AS UG ON AP.UserGroupFK = UG.UserGroupPK
INNER JOIN (SELECT Application.ApplicationPK,Application.Name FROM Application
UNION ALL
SELECT ApplicationGroup.ApplicationGroupPK,ApplicationGroup.Name FROM ApplicationGroup
UNION ALL
SELECT ApplicationReserved.ApplicationReservedPK,ApplicationReserved.Name FROM ApplicationReserved
) AS Apps ON AP.ApplicationFK = Apps.ApplicationPK
INNER JOIN ApplicationData AS AD ON AD.ApplicationProfileFK = AP.ApplicationProfilePK
WHERE Apps.Name = N'AnApp'
AND Ap.StoredSize >= 5242880
ORDER BY StoredSize DESC, PersonalizationGroup, UserName, AppName, AppDataUnCompressedSize DESC
[/code]

Without reading further through the results we can see a {CSIDL_LOCAL_APPDATA} location captured, which possibly contained a locally cached copy of reports generated by the application.  As {CSIDL_LOCAL_APPDATA} is not a roaming part of the profile, it’s likely that this folder is candidate for review.

This is where tweaking the ORDER BY clause helps to analyse the results.  Rather than focus on size, ordering by RelativePath will give an indication of paths captured and their respective files.

All but the smallest files are compressed before being uploaded to the Personalization Server.  AD.Size represents the size of the file uncompressed in bytes when it’s restored on the endpoint.  To determine the size as stored in the database, use the DATALENGTH function on AD.Data.

An example updated SELECT line:

[code lang="sql"]
SELECT TOP(1000) UG.Name AS PersonalizationGroup, U.Name AS UserName, Apps.Name AS AppName, AP.StoredSize, AD.RelativePath, AD.Filename, AD.Size AS AppDataUnCompressedSize, DATALENGTH(AD.Data) AS AppDataStoredSize
[/code]

DATALENGTH may increase the overhead of the SELECT statement. So again be careful to initially test with WHERE clause that limits the results e.g. a particular Personalization Group, Application and Profile Size over X MB. Continue to test extending the WHERE clause, for example just a Personalization Group and Application.

A few extra features to be aware of

To get statistics on run time between queries, for example the difference between running with and without the DATALENGTH, use the tool "Query Menu -> Include Client Statistics". With each run of the script a client statistics window will be populated at the bottom of the screen. Also keep an eye on the SQL Server Activity Monitor by right clicking on the SQL Server and selecting "Activity Monitor".

So what if your SQL query appears to be running away with itself, the messages tab is returning red text that doesn’t look positive and you want to stop the script. Just hit the Stop Button, then try and figure out what went wrong.

Finally, the Microsoft SQL Studio Management Console has a SQL Syntax verifier, which you can run before running your SQL query to quickly verify the syntax.

In this blog we cover the basics on how to get up and running querying your Personalization Data. It's worth noting that there is an API that will allow you to perform similar tasks using PowerShell but SQL can provide the flexibility to use reporting applications. In the next blog, we'll add a few extra SQL queries based upon the above to dig a little more into the Personalization Data.