Application Compatibility Toolkit (ACT) queries in SQL

image_pdfimage_print

Microsoft Application Compatibility Toolkit is a tool that is used to see if an application is Vista or Windows 7 / Windows 8 compatible. It uses a database on a SQL Server.

The ACT Tool can be used to read the database but the results cannot be exported to – for example – Excel. To achieve that, it is needed to make custom queries in SQL Server.

In this article some examples are given.

All Discovered applications, ordered by priority and application name.
1-AllApps-prio-and-appname
1-AllApps-prio-and-appname-sql
1
2
3
SELECT      TYPE, appName, componentType, vendorName, version, priority
FROM        Applications
ORDER BY    priority, appName
All applications per computer.
2-AllAppsPerComputer
2-AllAppsPerComputer-sql
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT     Applications.priority, Application_Report_vw.OSID, 
           Application_Report_vw.vendorRating, Applications.vendorName,
           Applications.appName, Applications.version, 
           Applications.language, Machine_Installed_App_vw.machinename,
           Machine_Installed_App_vw.domainname
FROM       Application_Report_vw 
INNER JOIN Applications 
ON         Application_Report_vw.AppID = Applications.identity_hash
INNER JOIN Machine_Installed_App_vw
ON         Application_Report_vw.AppID = Machine_Installed_App_vw.AppID
WHERE      (Application_Report_vw.OSID = N'6.1.0')
ORDER BY   Machine_Installed_App_vw.machinename, Applications.priority,
           Applications.vendorName, Applications.appName
All Business Critical applications that do not have 64 bits support.
3-AllBCA-no-64bits-support
3-AllBCA-no-64bits-support-sql
1
2
3
4
5
6
7
8
9
10
SELECT     Applications.priority, Application_Report_vw.OSID,
           Application_Report_vw.vendorRating, Applications.vendorName,
           Applications.appName, Applications.version, Applications.language
FROM       Application_Report_vw
INNER JOIN Applications 
ON         Application_Report_vw.AppID = Applications.identity_hash
WHERE      (Application_Report_vw.OSID = N'6.1.0')
AND        (Application_Report_vw.vendorRating = 60)
AND        (Applications.priority = 1)
ORDER BY   Applications.vendorName, Applications.appName
All Business Critical Applications per computer that do not have 64 bits support.
4-AllBCA-per-computer-no-64bits-support
4-AllBCA-per-computer-no-64bits-support-sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT     Applications.priority, Application_Report_vw.OSID,
           Application_Report_vw.vendorRating,
           Applications.vendorName, Applications.appName, Applications.version, 
           Applications.language, Machine_Installed_App_vw.machinename,
           Machine_Installed_App_vw.domainname, OS.osName
FROM       Application_Report_vw
INNER JOIN Applications 
ON         Application_Report_vw.AppID = Applications.identity_hash
INNER JOIN Machine_Installed_App_vw 
ON         Application_Report_vw.AppID = Machine_Installed_App_vw.AppID
INNER JOIN Logical_Machine
ON         Machine_Installed_App_vw.machinename = Logical_Machine.machineName
INNER JOIN OS ON Logical_Machine.osID = OS.osID
WHERE     (Application_Report_vw.OSID = N'6.1.0')
AND       (Application_Report_vw.vendorRating = 60)
AND       (Applications.priority = 1)
ORDER BY Applications.vendorName, Applications.appName
All Discovered applications with a compatibility rating of ‘vendor signed’ or ‘certified’.
5-AllDiscoveredApps-vendor-signed-or-certified
5-AllDiscoveredApps-vendor-signed-or-certified-sql
1
2
3
4
5
6
SELECT     AppName, OSID, CompatibilityRating32, CompatibilityRating64
FROM       Application_Report_Complete_vw
WHERE      (OSID = N'6.1.0') AND (CompatibilityRating64 = 90)
OR         (CompatibilityRating32 = 90) 
OR         (CompatibilityRating64 = 100) 
OR         (CompatibilityRating32 = 100)

A description of the vendor rating, OS ID and compatibility rating.

Vendor rating
10 Logo certified
20 Works as is
30 Works
40 Works with solution
50 Works with minor issues or has solutions
60 Does not work
100 No data
OS ID
5.1.3 Windows XP with SP3
6.0.0 Windows vista
6.0.1 Windows Vista with SP1
6.1.0 Windows 7
6.1.1 Windows 7 with SP1
6.2.0 Windows 8
Compatibility Rating
0 Unknows
1 Pledge – intent to support
20 Does not Work
80 Works as is
90 Vendor signed support
100 Certified

Permanent link to this article: https://www.vroom.cc/wordpress/application-compatibility-toolkit-act-queries-in-sql/