Useful Environment Manager Queries Part 2

*This post originally appeared on the AppSense blog prior to the rebrand in January 2017, when AppSense, LANDESK, Shavlik, Wavelink, and HEAT Software merged under the new name Ivanti.

In the first blog “A Couple of Useful Environment Manager Queries” we covered a couple of basic queries that are commonly used by AppSense Support when troubleshooting issues. As many of these issues relate to performance issues or profile size, utilizing these queries to gain an understanding of captured data allows an Environment Manager (EM) Administrator to refine their settings and optimize their environment further.

In this blog we’ll cover a few variations of the queries and continues in much the same way, aimed at the EM Admin. This series of blogs isn’t intended as a SQL tutorial but hopefully provides enough information on how to use the Microsoft SQL Query Editor GUI to modify the existing SQL queries to build your own reports. There are plenty of online resources which will better describe the components of the SQL query.

If you’re not familiar with the Microsoft SQL Query Editor GUI or SQL, please review the first blog in the series. This blog doesn’t cover the same level of detail in the steps as the introductory blog.

Please be aware, the queries that report on ApplicationData tables query the bulk of the data within in database (excluding archives). Therefore it’s recommended to limit queries using WHERE clauses until you’re confident on the size of the dataset that can be reported on without having an adverse impact.

Aggregate Reports

In the first blog we initially covered a basic query to provide Application Profile information (excluding Application Data). The query returned Application Profiles for the Application Group “AnApp”, where the Application Profile size is over 5MB. Each Application Profile is returned as a single row, so you will be able to see that user “A” has a profile of 4MB, user “B” has a profile of 3MB etc.

It’s possible to modify the query in order to aggregate the results using the Aggregate functions. These functions can be useful for returning the sum or average size of an Application Profile data size and a count of the number of Application Profile records captured by the clauses.

The Application Profile query we were using in the first blog is provided below:

[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]

Let’s update this query to return the SUM of the StoredSize for Application Profiles and a count of the number of Application Profiles. We’ll also limit the results to an individual Personalization Group PG02. There are a number of steps involved to convert the query into an aggregate form. There is an updated query provided in case the steps are not working out.

Within the query window select the query text, right click and select “Design in Query Editor…”:

• This query will not return thousands of rows so we can remove the TOP clause. Right click in the Query Editor and select “Properties”. Under Top Specification change (Top) to “No” to remove the TOP clause (see first blog for more information on TOP). Click on Close to return to the Query Editor window.
• Right click in the Query Editor and select “Add Group By”. You should note a Group By column has been added to the Query Editor.
• Within the StoredSize column, under “Group By” change from “Group By” to “Sum”.
• Remove the User table by right clicking on the table aliased as U and selecting remove.
• Tick the ApplicationProfilePK column in the ApplicationProfile table (aliased as AP). This will be used to count the number of application profiles used by each row returned.
  • Change the ApplicationProfilePK Group By column to Count.
  • As ApplicationProfilePK isn’t a very friendly name, update the ApplicationProfilePK Alias to APCount.
• Remove the AppName and StoredSize filters so they’re blank.
• Finally add a filter in the PersonalizationGroup row for a Personalization Group, in this example for the group “PG02”.

Before running the query, it does need a manual fix. Before I discuss that let’s cover a couple of points.

• There are quite a few modifications to the query but you’ve now changed the nature of the output to aggregate results. Aggregate reports can be very useful to isolate areas for further detailed investigation.
• Once a query is an aggregate (Group By), all columns need to be aggregated. Click in the Group By columns to see what options are available.
• The WHERE clause has changed to HAVING. HAVING is used to filter on the grouped result rather than individual rows prior to aggregating the results. As we noted in the first blog post, the Query Editor automatically fixes some syntax.
• As we wanted a count on the number Application Profiles, there is little point in retaining the User table as any columns selected would require aggregating.

Below is the new query as shown within the Query Designer (note the User table aliased as U has been removed as per the steps above):

So where’s the issue? The StoredSize column is defined as an Integer (INT), which is limited to 2,147,483,647 as the highest value. As this field stores the Application Profile Stored Size in bytes, a SUM would overrun this value at approximately 2GB. So we need to convert the StoredSize from an Integer to a Big Integer (BIGINT). This can be performed using the CAST function.

Replace:

[code lang="sql"]
SUM(AP.StoredSize) AS StoredSizeBytes
[/code]

With:

[code lang="sql"]
SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes
[/code]

We’ve covered how to modify the Application Profile to an aggregate function using the Query Editor. What does the final query look like?

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, Apps.Name AS AppName, SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes, COUNT(AP.ApplicationProfilePK) AS APCount
FROM ApplicationProfile AS AP
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
GROUP BY UG.Name, Apps.Name
HAVING (UG.Name = N'PG02')
ORDER BY StoredSizeBytes DESC, PersonalizationGroup, AppName
[/code]

How does the output look?

It could be argued that the results look a little odd, we can see 170 users with a combined 0 bytes of stored “Microsoft Office 2003 Group” data. This is because the Personalization Server has created an ApplicationProfile record for these applications but has no associated ApplicationData. The actual ApplicationProfile StoredSize is set to as NULL. Unfortunately this demonstrates that an understanding of the database schema or how the application works is beneficial. On occasions when the results to a query look a little odd, you will need to either debug your query or do a little digging into the results to see if it’s expected behaviour.

Ideally we need to filter out the rows where StoredSize IS NULL but this has to be performed at the row level before the results are aggregated. As discussed above, HAVING filters on the results returned by the aggregate function, WHERE filters before the aggregation takes place. The Query Editor has automatically changed the WHERE clause to HAVING by adding the Group By column, this is the reason that “HAVING (UG.Name = N’PG02’)” is included in the HAVING clause. It may very well be more efficient to have had that within the WHERE clause, so that only the aggregated data of the PG02 Personalization Group is calculated, rather than all Personalization Groups and then filtered to only show PG02.

Adding the WHERE clause for a column is possible using the Query Editor.

• Within the query window select the query text, right click and select “Design in Query Editor…”:
• Because the ApplicationProfile (aliased as AP) table StoredSize has an aggregate function (SUM) run against it, rather than just Group By it appears in the table view as unticked and with the Summation (Sigma) symbol next to it. Tick the StoreSize column in the AP table.
• Untick the StoreSize Output tick box, as this entry will not be used for the Output. If the Output remains ticked, then an aggregate function will need to be performed on the new StoredSize and a new row will be created for the WHERE clause.
• Change the StoredSize Group By clause to Where.
• Update the StoreSize Filter to IS NOT NULL.
  • You may get a number of warning messages as you type IS, then NOT stating that the entry can only be used with IS NULL and IS NOT NULL. You can either keep clicking on OK but it’s possible to use the incorrectly formatted <> NULL in the Filter and the Query Editor will fix it to IS NOT NULL without any popup messages. Please note that <> NULL is not a valid SQL format for “not equal to NULL”, so please don’t use it when manually editing queries.

The updated query now includes both the WHERE and HAVING clause:

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, Apps.Name AS AppName, SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes, COUNT(AP.ApplicationProfilePK) AS APCount
FROM ApplicationProfile AS AP
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 AP.StoredSize IS NOT NULL
GROUP BY UG.Name, Apps.Name
HAVING (UG.Name = N'PG02')
ORDER BY StoredSizeBytes DESC, PersonalizationGroup, AppName
[/code]

The modified output is now

As mentioned above we removed the User table as it didn’t seem relevant to the result. There was no point in counting the Users as it was Application Profiles we wanted a count of. There was no point in grouping by users either as this would have split the results out further. The table joins can have significant consequences on the results, therefore when removing tables run the query before and after to see the impact.

To demonstrate the impact of joins on results, below is an updated query that returns the total profile size for users across all Personalization Groups in descending order. In this case the Application Name (a combination of 3 other tables) Apps is not used within any clause or predicate but has been left in and has an impact on the results.   If you’re not familiar with the JOIN statement, it may be worth an Internet search.

First run of the updated query:

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, U.Name AS UserName, SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes, COUNT(AP.ApplicationProfilePK) AS APCount
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 AP.StoredSize IS NOT NULL
GROUP BY UG.Name, U.Name
ORDER BY StoredSizeBytes DESC, PersonalizationGroup, UserName
[/code]

Example output:

The results appear to make sense, the user “tb\tb-fin-199” within Personalization Group “PG02”, has 6 Application Profiles with a total stored size of “26765534” bytes. As Apps wasn’t used within any clauses it would make sense to consider removing the subquery from the JOIN clause. Removing Apps and focusing just on the user “tb\tb-fin-199” we can see the Application Profile count has increased:

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, U.Name AS UserName, SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes, COUNT(AP.ApplicationProfilePK) AS APCount
FROM ApplicationProfile AS AP
INNER JOIN [User] AS U ON AP.UserFK = U.UserPK
INNER JOIN UserGroup AS UG ON AP.UserGroupFK = UG.UserGroupPK
WHERE AP.StoredSize IS NOT NULL AND U.Name = N'TB\tb-fin-199'
GROUP BY UG.Name, U.Name
ORDER BY StoredSizeBytes DESC, PersonalizationGroup, UserName
[/code]

Returns:

“tb\tb-fin-199” now has 16 Application Profiles associated with the account (where the AP.StoredSize IS NOT NULL). If we remove the AP.StoredSize predicate we actually see that there are 22 profiles associated with the account:

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, U.Name AS UserName, SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes, COUNT(AP.ApplicationProfilePK) AS APCount
FROM ApplicationProfile AS AP
INNER JOIN [User] AS U ON AP.UserFK = U.UserPK
INNER JOIN UserGroup AS UG ON AP.UserGroupFK = UG.UserGroupPK
WHERE U.Name = N'TB\tb-fin-199'
GROUP BY UG.Name, U.Name
ORDER BY StoredSizeBytes DESC, PersonalizationGroup, UserName
[/code]

We discuss the “WHERE AP.StoredSize IS NOT NULL” predicate above, which accounts for 6 of the additional profiles. So where are the additional 10 profiles coming from? There is an additional table called ApplicationDiscovered that is used to populate the ApplicationProfile.ApplicationFK column. The relationship between the tables similar to:

Adding the ApplicationDiscovered to the Apps subquery returns information about the additional ApplicationProfiles:

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, U.Name AS UserName, SUM(CAST(AP.StoredSize AS BIGINT)) AS StoredSizeBytes, COUNT(AP.ApplicationProfilePK) AS APCount, Apps.Name
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
UNION ALL
SELECT ApplicationDiscovered.ApplicationDiscoveredPK,ApplicationDiscovered.Name FROM ApplicationDiscovered
) AS Apps ON AP.ApplicationFK = Apps.ApplicationPK
WHERE U.Name = N'tb\tb-fin-199'
GROUP BY UG.Name, U.Name, Apps.Name
ORDER BY StoredSizeBytes DESC, PersonalizationGroup, UserName
[/code]

We get the result:

We can see from the results above that the remaining rows are from Discovered Applications. As Discovered Applications do not store data within ApplicationData, I’ve omitted them from the Apps subquery for general reporting purposes. It wouldn’t necessarily be useful to know there are 22 profiles, when only 6 that are directly user related. It also highlights the impact of a table join on the results and as your reporting becomes more complicated, a greater understanding of the schema through investigation maybe required.

Aggregate ApplicationData

Aggregating ApplicationData can be useful to identify large numbers of files or large file types. For example let’s modify the ApplicationData query in the first blog to return a COUNT of files captured in a RelativePath for an Application Group and a SUM of their size (note, this is the size when extracted on the endpoint and not compressed size in the database, please see the first blog).

The original query:

[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]

The updated query is below:

[code lang="sql"]
SELECT UG.Name AS PersonalizationGroup, Apps.Name AS AppName, AD.RelativePath, COUNT(AD.FileName) AS FileCount, SUM(CAST(AD.Size AS BIGINT)) 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
GROUP BY UG.Name, Apps.Name, AD.RelativePath
HAVING Apps.Name = N'AnApp'
ORDER BY PersonalizationGroup, RelativePath
[/code]

Review the query using the Query Editor tool to determine how it can be modified to meet your requirements. Aggregate functions on the ApplicationData table can take time to process. As mentioned previously, initially start with WHERE/HAVING clauses that will limit the data returned to assess the volume of data that can be queried within your environment without having a negative impact.

The results of the query:

Using the

ApplicationProfile COUNT query discussed in the previous section, we know there are 78 Application Profiles for AnApp in the Personalization Group PG02. From this query we can see that there are two folders worth further investigation:

• {CSIDL_LOCAL_APPDATA}\AnApp\LocalCache - Large number of small files for 78 users.
• {CSIDL_LOCAL_APPDATA}\AnApp\Reports - Large size of data in bytes with relation to number of files captured

To leave a base query that can be modified in the Query Editor, I’ve left in the User table even though it’s not referenced elsewhere in the query.

Summary

In the first two blogs we have covered how to use the Microsoft SQL Server Query Editor GUI to edit a couple of existing SQL queries that are used by AppSense Support during troubleshooting, in order to provide both detailed information on Personalized data and also an aggregated overview.

In the next blog in the series we’ll cover a couple of extra modified queries to look for specific file extensions.