Im ACMP können Sie schnell und einfach Abfragen erstellen, ohne über den Aufbau und die Struktur der Daten in der Datenbank Bescheid wissen zu müssen. Mithilfe der normalen Abfragen können Sie intuitiv und praktisch eine Abfrage zusammenstellen, indem Sie das Feld eines Namens suchen und es per Drag-and-Drop in das Abfragefenster ziehen. Sie benötigen keine weiteren Angaben darüber, wo beispielsweise die Einträge in der Datenbank gespeichert sind oder wie man diese miteinander in Beziehung setzt, da ACMP diese Arbeiten für Sie erledigt. Diese Herangehensweise ist sehr komfortabel, kommt aber bei komplexeren Abfragewünschen an seine Grenzen. Hier kommen die erweiterten Abfragen ins Spiel.

Manche Datenbankfelder sind entweder im normalen Modus gar nicht oder nicht einfach miteinander kombinierbar. Der erweiterte Modus unterstützt Sie dabei, diese abzubilden, denn hierbei gilt: alles, was Sie mit SQL machen können, ist auch mit den erweiterten Abfragen möglich.
Durch die Abfrage wird eine Vorfilterung der Daten vorgenommen und ein natives SQL geschrieben, welches in der Datenbank ausgeführt wird. Dank diesem Modus können Sie komplexere Abfragen erstellen, bei denen Tabellen miteinander kombiniert werden. Dabei kann es schnell für Einsteiger unübersichtlich und komplex werden. Außerdem besteht die Gefahr, dass man erweiterte Abfragen baut, die viele Ressourcen auf Server- und Consolenseite verbrauchen, wodurch z.B. die Console zur Aufbereitung der Daten längere Zeit in Anspruch nimmt.

Erstellen Sie eine erweiterte Abfrage, indem Sie in der Abfrageverwaltung in der Ribbonleiste auf Hinzufügen klicken. In dem sich öffnenden Wizard müssen Sie einen Zielordner und einen Namen für die Abfrage angeben und die Checkbox Erweiterte Abfrage erzeugen auswählen. Die Oberfläche des erweiterten Abfragemodus wechselt. Optional können Sie zusätzlich eine Beschreibung eingeben. Wählen Sie anschließend aus zweierlei Modi, um mit der erweiterten Abfrage zu beginnen: Standard oder Experte:

• Erweiterte Abfragen - Standard: SELECT mit Assistent (Abfragedesigner zur Erstellung von erweiterten Abfragen)
• Erweiterte Abfragen - Experte SELECT oder SP für Ergebnismenge (SQL Editor zum Erstellen von SQL Abfragen)

Erweiterte Abfragen - Standard: SELECT mit Assistent

Bei diesem Modus hilft der Assistent Ihnen dabei, eine Abfrage zu erstellen. Er unterstützt Sie sogar auch bei der Verknüpfung von Tabellen und bei korrelierenden Abfragen.

Auf der rechten Seite finden Sie die Datenbanktabellen, die Sie miteinander verknüpfen können, indem Sie die passenden Tabellen in das mittlere Feld (der Hauptablauf des Assistenten) per Drag-and-Drop hineinziehen. Die ausgewählten Felder werden Ihnen im unteren Fenster angezeigt, sowie die folgenden Tabellenfelder, aus denen Sie zusätzliche Informationen ziehen können: Ausdruck, Alias, Sortierrichtung, Aggregat, Gruppierung oder Kriterium.
Sollten Sie ein bestehendes Feld aus dem Hauptablauf entfernen wollen, klicken Sie entweder im unteren Fenster auf das Lösch-Icon oder nehmen Sie den Haken aus dem Feldeintrag heraus (siehe nachfolgende Abbildung).

Wählen Sie die Felder aus der Tabelle und stellen Sie eine Beziehung über den Primär- bzw. Fremdschlüssel her. Dies erreichen Sie, indem Sie ein Feld einer Tabelle auf das zugehörige Feld der anderen Tabelle ziehen (siehe vorherige Abbildung).
Auch ist es möglich über das Kontextmenü die Funktion Alles deaktivieren auszuwählen, um alle Felder in nur einem Schritt abzuwählen. Die benötigten Felder können Sie anschließend manuell aktivieren und der Tabelle hinzufügen.

 Die Felder werden in der Reihenfolge, wie sie im SQL-Statement angegeben sind, angezeigt. Sollten Sie die Reihenfolge ändern wollen, müssen Sie diese manuell anpassen.

Beispiel für eine mit dem Standard-Modus eingefügten erweiterten Abfrage im SQL Tab

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;

Bei dieser Abfrage wird die Anzahl der Clients angezeigt, die nach Software und Versionen gruppiert werden. Hierbei wird jedoch nur die Software angezeigt, die im Namen „Update“ enthält und von Microsoft stammt. Die Einträge sind absteigend nach der Häufigkeit sortiert, also die Häufigsten zuerst.

Erweiterte Abfrage - Experte: SELECT oder SP für Ergebnismenge

Der erweiterte Standardmodus ist recht flexibel und bietet dabei ein Minimum an Komfort und Kontrolle während der Erstellung einer Abfrage. Hierbei sind die Ausführungen, die eine SQL-Datenbank innerhalb der Abfragetechniken anbieten, begrenzt. Und genau dort kommt der Expertenmodus des erweiterten Abfragemodus ins Spiel.
Im Gegensatz zu der Standardansicht der erweiterten Abfrage gibt es in diesem Modus keine zusätzlichen Hilfen. Sie müssen hier die SQL-Statements per Hand eingeben oder auf die Stored Procedures (SP) zurückgreifen, die Sie möglicherweise im MS Management Studio gebaut haben. Stored Procedures sind auf dem SQL Server gespeicherte und benannte SQL-Abfragen, die von einem Datenbank-Client aufgerufen werden können.

Verwenden Sie nie die von ACMP mitgelieferten Stored Procedures. Diese können sich nach einem Update ändern oder sogar entfernt werden. Wenn Sie diese als Vorbild verwenden wollen, fertigen Sie eine Kopie der Procedure an und verwenden diese in Ihrer Abfrage.

Beispiel für eine händisch eingegebene Abfrage (SELECT oder SP für Ergebnismenge)

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

Bei dieser Abfrage werden die IP-Adressen und die Computernamen von den Clients ausgeworfen, bei denen der Uninstallpath mit „C:“ beginnt.

Weitere Beispiele für erweiterte Abfragen

Abfrage für einen Report zur Benachrichtung über neue Managed Software Pakete

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

Mit dieser Abfrage können Sie sich einen Report erzeugen und sich über mögliche neue Managed Software Pakete informieren lassen. Dabei werden die verschiedenen Testringe namentlich definiert und es werden die Softwarenamen und Versionen gelistet. Sollte es für Ihre Arbeit relevant sein, kann es auch hilfreich sein, dass über diese Abfrage das Veröffentlichungsdatum der Software mit angegeben wird. Im Beispiel können Sie ebenfalls sehen, wie man die SQL-View verwenden kann.

Abfrage zur Auflistung einer bestimmten Software bei den 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%‘

Innerhalb der Abfrage werden die Clients gelistet, die eine bestimmte Software bei sich installiert haben. In dem hier genannten Beispiel ist die Software Microsoft Office, gefüllt mit einer Wildcard (%), damit nicht nur eine spezifische Version aufgezählt wird. Zusätzlich dazu werden auch die Versionen und die Computernamen der Clients zurückgegeben.

Abfrage zur Auflistung aller Client Commands

SELECT DISTINCT description
FROM SYS_SCRIPTS
ORDER BY DESCRIPTION

Anhand dieser Abfrage ist es möglich, sich alle Client Commands auflisten zu lassen, die bei Ihnen zur Verfügung stehen. Die Abfrageergebnisse werden alphabetisch sortiert ausgeworfen. Diese Art von Abfrage kann Ihnen beispielsweise dann nützlich sein, wenn Sie das ACMP bereinigen wollen und über eine Liste filtern möchten, welche Commands beibehalten werden sollen.

Das SQL Statement kann als Grundlage für einen Report verwendet werden.

Abfrage zur Auflistung von Client Commands inkl. der Verknüpfungsorte (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

Mit dieser Abfrage lassen sich alle Client Commands Ihres Systems sowie mögliche Verknüpfungsorte auflisten. Hierbei werden sämtliche Client Commands der verschiedenen Phasen (Status) berücksichtigt (Erstellen bis Freigabe zurückgezogen). Als mögliche Verknüpfungsorte werden Container, der ACMP Kiosk und die Job Collections verwendet - überall da, wo Sie in der Regel ein Client Command verknüpfen können. Sobald ein Client Command an einem der genannten Orte verwendet oder hinterlegt wurde, wird die Zeile mit dem Namen oder dem Pfad genannt, sodass Sie das Command gezielt wiederfinden oder zuordnen können. Die Orte der Hinterlegungen werden anhand der unterschiedlichen IDs bestimmt. Die Abfrageergebnisse sind nach dem Namen des Client Commands sortiert.

Abfrage zur Übersicht über Update Collection installierte Windows Updates

;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

Über diese Abfrage können Sie sich Ergebnisse anzeigen lassen, die Updates auflisten, die über eine Windows Update Collection installiert wurden. Dabei durchsucht das Skript alle Ausführungen der Update Collections, die auf den Clients ausgeführt wurden. Hierbei werden die Updates angezeigt, die genau dortüber installiert wurden.