With ACMP you can create queries quickly and easily without having to know the structure of the data in the database. With normal queries, you can build a query intuitively and practically by finding the field of a name and dragging and dropping it into the query window. You do not need to know any further information about where the records are stored in the database or how they relate to each other, because ACMP does this work for you. This approach is very convenient, but it reaches its limits when more complex queries are required. This is where advanced queries come in.

Some database fields cannot be combined with each other in normal mode. Advanced mode helps you to map these, because anything you can do with SQL, you can do with advanced queries.

The query pre-filters the data and writes a native SQL that is executed in the database. This mode allows you to create more complex queries that combine tables. For beginners, this can quickly become confusing and complex. There is also a risk of building advanced queries that consume a lot of resources on the server and console side, meaning that the console takes longer to process the data, for example.

Create an advanced Query by clicking on Add in the Query Management section of the ribbon bar. In the wizard that opens, you have to specify an output directory and a name for the Query and select the Create advanced Query checkbox. The Advanced Query mode interface changes. You can optionally enter an additional description. Then select from two modes to start the advanced Query: Standard or Expert:

• Advanced Queries - Basic: SELECT with assistant (Query Designer for creating advanced Queries)
• Advanced Queries - Expert SELECT or SP for result set (SQL Editor for creating SQL Queries)

Advanced Queries – Basic: SELECT with Assistant

In this mode, the assistant helps you to create a query. It even helps you to link tables and correlate queries.

On the right-hand side, you will find the database tables that you can link to each other by dragging and dropping the appropriate tables into the middle field (the main flow of the wizard). The selected fields are displayed in the lower window, along with the following table fields, from which you can obtain additional information: printout, alias, sort type, aggregate, grouping or criterion.

If you want to remove an existing field from the main flow, either click the delete icon in the lower window or remove the tick from the field entry (see figure below).

Select the fields from the table and create a relation using the primary or foreign key. You can do this by dragging a field from one table to the corresponding field in the other table (see the prior figure).

You can also select the Disable All function from the context menu to deselect all fields in a single step. You can then manually enable the fields you need and add them to the table.

The fields are displayed in the order in which they appear in the SQL statement. If you want to change the order, you have to customise it manually.

Example of an advanced query inserted in SQL Tab using Standard mode

SELECT SYS_SW_SETUP.NAME, SYS_SW_SETUP.VERSION, COUNT(1) AS AnzahlClients
FROM CLT_CLIENTS INNER JOIN
CLT_SW_SETUP ON CLT_CLIENTS.CLIENTID = CLT_SW_SETUP.CLIENTID INNER JOIN
SYS_SW_SETUP ON CLT_SW_SETUP.SWSETUPID = SYS_SW_SETUP.SWSETUPID
WHERE NAME LIKE '%UPDATE%' AND PUBLISHER LIKE '%MICROSOFT%'
GROUP BY SYS_SW_SETUP.NAME, SYS_SW_SETUP.VERSION
ORDER BY COUNT(1) DESC;

This Query displays the number of Clients grouped by software and versions. However, it only displays software that contains „Update“ in its name and comes from Microsoft. The entries are sorted in descending order of frequency, i.e. the most frequent ones first.

Advanced Query – Expert: SELECT or SP for Result Set

The advanced default mode is quite flexible and offers a minimum of comfort and control while creating a query. Executions that a SQL database offers within the query techniques are limited. And that is exactly where the expert mode of the advanced Query mode comes into play.

In contrast to the standard view of the advanced Query, there is no additional help in this mode. You have to enter the SQL statements by hand or use the stored procedures (SP) that you may have built in MS Management Studio. Stored procedures are stored and named SQL Queries on the SQL Server that can be accessed by a database client.

Never use the stored procedures supplied with ACMP. These can change or even be removed after an Update. If you want to use them as a model, make copies of the procedures and use these in your Query.

Example of a manually entered Query (SELECT or SP for Result Set)

SELECT
               CLT_CLIENTS_TABLE.COMPUTERNAME,
               CLT_CLIENTS_TABLE.PRIMARYIP,
               CLT_SW_SETUP.UNINSTALLPATH
WHERE
               CLT_SW_SETUP.UNINSTALLPATH LIKE 'C:%'
FROM CLT_CLIENTS_TABLE,
               CLT_SW_SETUP

Queries of this kind return the IP addresses and computer names of Clients whose uninstall path begins with "C:".

Continuing with examples of advanced Queries

Query for a report on notifications of new Managed Software packages

SELECT SoftwareName, DisplayVersion, cast(releasedate -2 as datetime) as Releasedate,
                               Case
                               When TestRing = 0 Then 'Kein Ring'
                               When TestRing = 1 Then 'Testring 1'
                               When TestRing = 2 Then 'Testring 2'
                               When TestRing = 255 Then 'Freigegeben'
                               End as Testring
From VW_ManagedSoftware_Versions
Where Testring > 0
Order by SoftwareName, DisplayVersion

With this query, you can generate a report and be informed about possible new managed software packages. The various test rings are defined by name and the software names and versions are listed. If it is relevant to your work, it may also be helpful that the publish date of the software is also specified via this query. The example also shows how to use the SQL view.

Query for listing specific software on clients

SELECT [Computername], [Name], [Version], CLT_CLIENTS.CLIENTID
FROM SYS_SW_SETUP
LEFT JOIN CLT_SW_SETUP ON CLT_SW_SETUP.SWSETUPID = SYS_SW_SETUP.SWSETUPID
INNER JOIN CLT_CLIENTS ON CLT_CLIENTS.CLIENTID = CLT_SW_SETUP.CLIENTID
WHERE NAME LIKE ‘Microsoft Office%‘

The clients that have a certain software installed on them are listed within the query. In the example given here, the software is Microsoft Office, filled with a wildcard (%), so that not only one specific version is listed. In addition, the versions and the computer names of the Clients are also returned.

Query for listing all Client Commands

SELECT DISTINCT description
FROM SYS_SCRIPTS
ORDER BY DESCRIPTION

This query allows you to list all client commands available to you. The query results are sorted alphabetically. This type of query can be useful, for example, if you want to clean up the ACMP and filter which commands you want to keep using a list.

The SQL statement can be used as a basis for a report.

Query to list Client Commands including the link locations (container, ACMP Kiosk, Job Collection)

Select DISTINCT
    SYS_SCRIPTS.DESCRIPTION As "Client Command Name"
   -- ,SYS_SCRIPTS.ScriptVersion As "Version",
   --     Status = Case SYS_SCRIPTS.State 
   --     When '1' Then 'Erstellen'
   --     When '2' Then 'Testen' 
   --     When '3' Then 'Bereit zur Synchronisation'
   --     When '4' Then 'Synchronisieren' 
   --     When '5' Then 'Bereit zur Freigabe'
   --     When '7' Then 'Freigegeben'
   --     When '8' Then 'Freigabe zurückgezogen' 
   --     Else 'Unbekannt' End
    --,Interaktiv = Case SYS_SCRIPTS.ALLOWINTERACTIVEUSE When 1 Then 'Ja'
Else 'Nein' End
    ,CLT_CONTAINER_STRUCTURE.Name AS "Container Name"
    ,CLT_CONTAINER_STRUCTURE.Path AS "Container Pfad"
    ,VW_SYS_Kiosk_Items.ContentName AS "Kiosk Eintrag"
    ,SYS_Templates.NAME AS "Job Collection Name"

FROM SYS_SCRIPTS
  Left JOIN VW_Script_Container_Jobs ON SYS_SCRIPTS.SCRIPTID = VW_Script_Container_Jobs.ScriptId
  Left JOIN CLT_CONTAINER_STRUCTURE ON CLT_CONTAINER_STRUCTURE.ContainerID = VW_Script_Container_Jobs.ContainerId
  Left JOIN VW_SYS_Kiosk_Items ON SYS_SCRIPTS.SCRIPTID = VW_SYS_Kiosk_Items.ContentId
  Left JOIN SYS_Jobs ON SYS_Jobs.ContentId = SYS_SCRIPTS.SCRIPTID
  Left JOIN SYS_Templates_Jobs  ON SYS_Jobs.Id = SYS_Templates_Jobs.JobId
  Left JOIN SYS_Templates ON SYS_Templates_Jobs.TemplateId = SYS_Templates.Id
ORDER BY  "Client Command Name" DESC

This Query lists all Client Commands in your system and possible locations. All Client Commands of the different phases (status) are taken into account (Create, Release, Withdrawn). Containers, the ACMP Kiosk and the Job Collections are used as possible locations - wherever you can usually link a Client Command. As soon as a Client Command is used or stored in one of the locations mentioned, the row is named with the name or path so that you can easily find or assign the command. The locations of the deposits are determined by the different IDs. The query results are sorted by the name of the Client Command.

Query for an overview of Windows Updates installed via the Update Collection

;WITH cte AS
 (
  Select Items.ComputerName,
  UpdateName = SubItems.NativeMessage,
  ErrorMsg = SubItems.NativeErrorMessage,
  DateTime = Cast(SubItems.EndDate -2 as datetime),
  LetzteBoot = Cast(os.LastBoot -2 as datetime),
  SubItems.Result,
  Items.ClientId,
  ROW_NUMBER() OVER (PARTITION BY
 Items.ComputerName,SubItems.NativeMessage,SubItems.NativeErrorMessage
 ORDER BY SubItems.EndDate DESC) AS rn
 From SYS_Jobs_Logs_SubItems As SubItems
  Left Join SYS_Jobs_Logs_Items As Items On (SubItems.ParentExecId =
 Items.ExecId
 or (SELECT ParentExecId FROM SYS_Jobs_Logs_SubItems WHERE
 ChildExecId=SubItems.ParentExecId) = Items.ExecId)
 left join CLT_CLIENTS on CLT_CLIENTS.CLIENTID = Items.ClientId
 left join CLT_OPERATINGSYSTEM AS OS ON os.CLIENTID = Items.ClientId
 Where SubItems.JobKind = 58
      And (SubItems.NativeMessage Like '_%' Or SubItems.NativeErrorMessage Like
 '_%')
      And Items.ComputerName Like '%' And SubItems.LastUpdate >= DateAdd(day,-12, GetDate())
      and ChildExecId not in (select ParentExecId from SYS_Jobs_Logs_SubItems)
 Group By Items.ComputerName,
  SubItems.NativeMessage,
  SubItems.NativeErrorMessage,
  SubItems.EndDate,
  SubItems.Result,
  os.LastBoot,
  Items.ClientId
 )
 SELECT *
 FROM cte
 WHERE rn = 1
 Order By cte.ComputerName, cte.DateTime Desc

This query allows you to display results listing the updates installed via a Windows Update Collection. To do this, the script searches through all executions of the Update Collections that have been run on the Clients. The updates installed via these executions are displayed.