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

AppSense join the twitter conversation graphic

In the first two blogs of this series of “A couple of useful Environment Manager Queries”, we cover a couple of basic SQL queries that are commonly used by AppSense Support when troubleshooting issues.  These queries are particularly useful as a base to produce a number of reports.

The first blog covers the queries and how to use the Microsoft SQL Query Editor GUI to update the clauses.  The blog series is not intended to be a SQL tutorial but demonstrates how an Environment Manager Administrator can utilise the Microsoft SQL Query Editor GUI, to make changes to the clauses to produce reports applicable to their environments.

The second blog demonstrates how the Query Editor GUI can be used to modify the two base queries in order to produce aggregated results.  These can be particularly useful to get an overview of data before using the queries to focus on individual data.

As the first two blogs go into detail on how to use the Query Editor, this blog will just cover specific types of changes using the Query Editor.  Each section includes a finished example query for review using the Query Editor to see how they can be modified.

We conclude the series with a couple of extra queries focusing on returning specific Application Data information.  If you’re not overly familiar with SQL, please refer to the first blog to get an overview of the Query Editor.  If you’re familiar with SQL, just grab the queries and start getting a better overview of your captured data.  However, please be aware that Application Data contains the bulk of the data and therefore rows within your database (excluding the Archives).  I would suggest starting with restricted WHERE clauses to reduce the results, in order to ensure the queries do not have a negative impact on performance.

Files Extension Detailed

It’s a common request to find file type by extension that have been unintentionally captured.  This is a simple modification to the ApplicationData query but to use wildcards the LIKE predicate is required in the WHERE clause.

The ApplicationData base query is below, filter on an application called AnApp.

[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'
ORDER BY StoredSize DESC, PersonalizationGroup, UserName, AppName, AppDataUnCompressedSize DESC
[/code]

We’ll use this query to find files with extension .txt.  The LIKE predicate uses the % to represent a wildcard.  The updated WHERE statement would be formatted as:

[code lang="sql"]
WHERE Apps.Name = N'AnApp' AND
AD.Filename LIKE '%.txt'
[/code]

Viewed within the Query Editor:

query designer screenshot

This is fine if you want just a single extension but what about .txt or .xml for the Application AnApp?  This maybe a good opportunity to quickly review the predicate filter and operator grid.  If you’re not familiar with operators such as AND, OR etc from scripting, it would be worth an Internet Search.

From the screen shot above, we can see that = N’AnApp’ and LIKE ‘%.txt’ are both within the same Filter column.  Because of this they are handled as an AND operator.

[code lang="sql"]
WHERE Apps.Name = N'AnApp' AND
AD.Filename LIKE '%.txt'
[/code]

Let’s add LIKE ‘%.xml’ to the OR operator column:

application management settings with code at bottom screenshot

Using the second Or… column has this affect, although the output isn’t quite the same (as can be seen in the screenshot above):

[code lang="sql"]
WHERE (Apps.Name = N'AnApp' AND
AD.Filename LIKE '%.txt') OR
AD.Filename LIKE '%.xml'
[/code]

The reason for this is each clause in a filter column is treated as an AND, the OR is between columns.  To get the correct where the AD.Filenames are grouped with OR operators, this has to be manually entered into the Query Editor, so that AnApp and (%.txt or %.xml) are grouped within the same column.

[code lang="sql"]
WHERE Apps.Name = N'AnApp' AND
(AD.Filename LIKE '%.txt' OR
AD.Filename LIKE '%.xml')
[/code]

query designer - AP, UG, U, Apps, AD. screenshot

The WHERE clause has a useful shortcut for OR operators. For example, to find the files Report1.txt, Report2.txt, Report3.txt.

[code lang="sql"]
WHERE AD.Filename = N'Report1.txt' OR
AD.Filename = N'Report2.txt' OR
AD.Filename = N'Report3.txt'
[/code]

The IN predicate can be used to create a multi-value subquery. So the above can be formatted as:

[code lang="sql"]
WHERE AD.Filename IN (N'Report1.txt',N'Report2.txt',N'Report3.txt')
[/code]

This can save a lot of typing and is also supported within the Query Editor.

query designer - AP, UG, U, Apps, AD. screenshot

The LIKE predicate is not supported within the IN predicate. The query below searches for a list of some file extensions that are commonly considered for exclusion, unfortunately each LIKE has to be completed. When so many OR operators are grouped together like this, it’s probably easier to edit manually rather than edit in the Query Editor GUI.

[code lang="sql"]
SELECT 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
(AD.Filename LIKE '%.accdb' OR
AD.Filename LIKE '%.accde' OR
AD.Filename LIKE '%.accdr' OR
AD.Filename LIKE '%.accdt' OR
AD.Filename LIKE '%.ade' OR
AD.Filename LIKE '%.adp' OR
AD.Filename LIKE '%.asd' OR
AD.Filename LIKE '%.avi' OR
AD.Filename LIKE '%.dll' OR
AD.Filename LIKE '%.doc' OR
AD.Filename LIKE '%.docb' OR
AD.Filename LIKE '%.docm' OR
AD.Filename LIKE '%.dotm' OR
AD.Filename LIKE '%.docx' OR
AD.Filename LIKE '%.dot' OR
AD.Filename LIKE '%.exe' OR
AD.Filename LIKE '%.log' OR
AD.Filename LIKE '%.m4a' OR
AD.Filename LIKE '%.m4p' OR
AD.Filename LIKE '%.m4v' OR
AD.Filename LIKE '%.mda' OR
AD.Filename LIKE '%.mdb' OR
AD.Filename LIKE '%.mde' OR
AD.Filename LIKE '%.mdw' OR
AD.Filename LIKE '%.mov' OR
AD.Filename LIKE '%.mp3' OR
AD.Filename LIKE '%.mpd' OR
AD.Filename LIKE '%.mpeg' OR
AD.Filename LIKE '%.mpg' OR
AD.Filename LIKE '%.mpp' OR
AD.Filename LIKE '%.mpt' OR
AD.Filename LIKE '%.mpw' OR
AD.Filename LIKE '%.mpx' OR
AD.Filename LIKE '%.odp' OR
AD.Filename LIKE '%.ods' OR
AD.Filename LIKE '%.ogg' OR
AD.Filename LIKE '%.one' OR
AD.Filename LIKE '%.ost' OR
AD.Filename LIKE '%.pdf' OR
AD.Filename LIKE '%.pot' OR
AD.Filename LIKE '%.potm' OR
AD.Filename LIKE '%.potx' OR
AD.Filename LIKE '%.ppa' OR
AD.Filename LIKE '%.ppam' OR
AD.Filename LIKE '%.pps' OR
AD.Filename LIKE '%.ppsm' OR
AD.Filename LIKE '%.ppsx' OR
AD.Filename LIKE '%.ppam' OR
AD.Filename LIKE '%.ppt' OR
AD.Filename LIKE '%.pptm' OR
AD.Filename LIKE '%.pptx' OR
AD.Filename LIKE '%.pst' OR
AD.Filename LIKE '%.pub' OR
AD.Filename LIKE '%.sldm' OR
AD.Filename LIKE '%.sldx' OR
AD.Filename LIKE '%.temp' OR
AD.Filename LIKE '%.tmp' OR
AD.Filename LIKE '%.vdw' OR
AD.Filename LIKE '%.vsd' OR
AD.Filename LIKE '%.wbk' OR
AD.Filename LIKE '%.xar' OR
AD.Filename LIKE '%.xla' OR
AD.Filename LIKE '%.xlam' OR
AD.Filename LIKE '%.xll' OR
AD.Filename LIKE '%.xlm' OR
AD.Filename LIKE '%.xls' OR
AD.Filename LIKE '%.xlt' OR
AD.Filename LIKE '%.xlsb' OR
AD.Filename LIKE '%.xlsm' OR
AD.Filename LIKE '%.xlsx' OR
AD.Filename LIKE '%.xltm' OR
AD.Filename LIKE '%.xltx' OR
AD.Filename LIKE '%.xlw' OR
AD.Filename LIKE '%.xsf' OR
AD.Filename LIKE '%.xsn' OR
AD.Filename LIKE '%.xtp' OR
AD.Filename LIKE '%.xtp2' OR
AD.Filename LIKE '%.xltm')
ORDER BY StoredSize DESC, PersonalizationGroup, UserName, AppName, AppDataUnCompressedSize DESC
[/code]

Total size of file extension (aggregated results)

Aggregate queries as useful to gain an overview of data captured before delving into the detail, this is the case with file extension queries. Allowing you to see both the number of files captured by extension and the total size captured.

The second blog in the series goes into detail on how to convert the standard query above into an aggregated query using the Query Editor. We’ll just cover the unique areas for the file extension query.

The query below produces an aggregated report showing the total number of files captured by extension and the size of the data captured as well as the uncompressed size on disk for the application AnApp, limited to the Personalization Group PG02. As mentioned previously, these can be time consuming reports and may have an impact on the SQL Server. Therefore start off with a limited WHERE clause until you have a good feel of your SQL environment. This is fine in my test database but if you’re running the report against Microsoft Office and it contains 100GB of data, it’s going to be a time consuming report to run.

[code lang="sql"]
SELECT RIGHT(AD.Filename,CHARINDEX('.',REVERSE(AD.Filename))) AS Extension, COUNT(AD.ApplicationDataPK) AS FileCount, SUM(CAST(AD.Size AS BigInt)) AS AppDataUnCompressedSize, SUM(CAST(DATALENGTH(AD.Data) AS BigInt)) AS AppDataStoredSize
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
UG.Name = N'PG02'
GROUP BY RIGHT(AD.Filename,CHARINDEX('.',REVERSE(AD.Filename)))
ORDER BY AppDataUnCompressedSize DESC
[/code]

We want to aggregate the report on file extension, to accomplish this we need to get just the file extension from the filename within the ApplicationData table (aliased as AD) Filename column. The following should return the characters after the final full stop ‘.’, as well as the full stop.

[code lang="sql"]
RIGHT(AD.Filename,CHARINDEX('.',REVERSE(AD.Filename))) AS Extension
[/code]

I don’t believe this can be generated in the Query Editor GUI and will need to be manually entered. Three string functions are used to process AD.Filename.

  • RIGHT(String,Integer) – Right returns the right most characters based upon the Integer provided. For example SELECT RIGHT('testdoc.txt',4) will return the right most 4 characters, which is '.TXT' in this example.
  • CHARINDEX(PatternToSearchFor,StringToSearch) – Unfortunately we can’t just pass 4 as an argument to RIGHT to say we want the last 4 characters of the string, as extensions can contain more or fewer characters. CHARINDEX is useful as it will return the position of the '.' within the string.
  • REVERSE(String) - Taking the example above if we run SELECT CHARINDEX('.','testdoc.txt') it would return a value of 8. Which doesn’t help with the RIGHT statement. The REVERSE function does pretty much what its name suggests and reverses the string from 'testdoc.txt' to 'txt.codtset'. So SELECT CHARINDEX('.',REVERSE('testdoc.txt')) returns a value of 4.

If we add the above together we get:

[code lang="sql"]
SELECT RIGHT('Testdoc.txt',CHARINDEX('.',REVERSE('testdoc.txt')))
[/code]

Which in turn returns the value '.txt'.
How about if a filename contains multiple '.'? For example:

[code lang="sql"]
SELECT RIGHT('Testdoc.abc123.txt',CHARINDEX('.',REVERSE('testdoc.abc123.txt')))
[/code]

CHARINDEX returns the position value of the first match of the pattern. So the value returned is still 4 and the extension returned is still '.txt'.
Running the original query we get the result as follows:

coding screenshot - extension, file count, AppDataUnCompressedSize, AppDataStoredSize

The GROUP BY clause doesn’t recognise the ‘Extension’ alias within the SELECT statement. I’m assuming this is down to the processing order of the query. Although the SELECT statement is at the top of the query, it’s processed between GROUP BY -> SELECT -> ORDER BY. Therefore the Extension alias is available to ORDER BY but is not available to GROUP BY. This results in a duplication of the Extension function within the GROUP BY clause:

[code lang="sql"]
GROUP BY RIGHT(AD.Filename,CHARINDEX('.',REVERSE(AD.Filename)))
[/code]

But we can have:

[code lang="sql"]
ORDER BY Extension, AppDataUnCompressedSize DESC
[/code]

What if the file doesn’t have an extension and therefore doesn’t contain a ‘.’? This should still get handled but you may see an empty field and/or a ‘.’ depending on how the file was captured.

screenshot of results and messages

End of the Series

The two base queries used in for the examples in this series, have been in use by AppSense Support for many years. Hopefully this series has helped the average Environment Manager Administrator who isn’t overly familiar with SQL, get a better understanding of the data they have captured by tweaking these queries. Adding a column here or changing a sort order there can provide a multitude of different useful reports. If after playing with the Microsoft SQL Query Editor you decide it’s not for you, you can always export the results of the base queries to CSV and review in Excel.