Erweiterte Abfragen erstellen
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.
Hinweis:
Mit den erweiterten Abfragen können Sie grundsätzlich alle Daten abfragen, die sich in der ACMP-Datenbank befinden. Dafür benötigen Sie sowohl Wissen über das Datenbankschema als auch die Bedeutung der Daten. Zusätzlich ist mehr Wissen über SQL-Strukturen erforderlich.
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.
Hinweis:
Sollten Sie gewisse Felder oder Tabellen für die erweiterte Abfrage ermitteln wollen, können Sie dies über die normale Abfrage und den zugehörigen Filtern machen. Ziehen Sie sich hierzu das zu ermittelnde Feld in den ODER Bereich und klicken Sie mit der rechten Maustaste auf das Feld und öffnen Sie den Punkt Kopieren im Kontextmenü. Hierdurch erhalten Sie die SQL Abfrage für den hinterlegten Filter. Im unteren Bereich der Where-Klausel finden Sie dann den Feld- und Tabellennamen.
Feld- und Tabellennamen ermitteln
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)
Erweiterter Abfragemodus
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).
Erweiterte Abfrage mithilfe des Assistenten erstellen
Hinweis:
Beachten Sie, dass Sie bei einem JOIN eine Beziehung zwischen den Tabellen herstellen müssen, indem Sie die Primärschlüssel der Tabellen miteinander verknüpfen. In diesem Beispiel ist es die CLIENTID.
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.
Felder deaktivieren
Hinweis:
Diese Vorgehensweise müssen Sie für alle Tabellen vornehmen, die eine Dopplung der Einträge vorsieht (z.B. ClientID oder HistoryID).
Die Schlüsselfelder können unterschiedliche Namensbezeichnungen haben. Das bedeutet, dass die Verbindungseinträge in den Spalten unterschiedlich heißen können und trotzdem miteinander kombiniert werden müssen. So kann ein Feld zum Beispiel ClientID oder lediglich mal ID heißen.
Achtung:
Wichtig ist, welche Bedeutung hinter einem Feld steht: ID kann in einer Tabelle ein Client sein, in einer anderen ein Asset und wiederum in einer anderen ein File Repository oder ein Job. Oft, aber nicht immer, sind die Feldbezeichnungen im Schema sprechend, dann werden die IDs passend genannt (z.B. ClientID oder AssetID). Beachten Sie das, wenn Sie die Tabellenfelder miteinander verbinden. Sie bekommen keine Fehlermeldungen, sondern nur Ergebnisse, bei denen Sie nicht immer auf Anhieb erkennen können, dass die Verbindung falsch verbunden ist.
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.
Hinweis:
Die Einträge in der rechten Tabelle sind alphabetisch sortiert. Alle Einträge, die als benutzerdefinierte Felder über die Einstellungen angelegt wurden, wurden mit einer zufällig generierten GUID in der Datenbank gespeichert. Sollten Sie also beispielsweise ein Feld beginnend mit „CF_VAL“ (CustomField_Value) oder „CF_HIS“ (CustomField_History) entdecken, handelt es sich um eines von Ihren angelegten Feldern.
Hinweis:
Auch können Sie beispielsweise ein bestehendes SQL-Statement aus dem SQL Server Management Studio herauskopieren und in die erweiterte Abfrage einbauen.
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.
Hinweis:
Auch können Sie beispielsweise ein bestehendes SQL-Statement aus dem SQL Server Management Studio herauskopieren und in die erweiterte Abfrage einfügen.
Achtung:
Je nachdem, aus welchem Tool Sie ein SQL-Statement herauskopieren, kann der eingefügte Text unsichtbare Steuerzeichen enthalten, die dazu führen, dass beim Ausführen der erweiterten Abfrage unerwartete Fehlermeldungen aufkommen können (zum Beispiel bei Winword oder PDF).
Erweiterte Abfrage erstellen
Achtung:
Benutzen Sie den Experten-Modus zur Erstellung einer erweiterten Abfrage nur, wenn Sie über ausreichendes Wissen bezüglich SQL-Datenbanken verfügen. Andernfalls könnten Sie unbeabsichtigt Daten löschen, die unwiderruflich aus der Datenbank verschwinden würden. Sie könnten auch Gefahr laufen, Abfragen zu erstellen, die sehr ressourcenintensiv sind und sowohl den SQL Server als auch den ACMP Server in Ihrer Leistung einschränken würden.
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.
Achtung:
Beachten Sie, dass Sie für die Zeitwerte wie InstallDateTime eine Datumskorrektur vornehmen müssen. Dies liegt daran, dass die eingesetzten Programmiersprachen (DELPHI, C# und TSQL) zweierlei Zeitrechnungen verwenden und mit zwei Tagen Versatz arbeiten. Sollten Sie also in Ihrer Abfrage zum Beispiel ein Datumsfeld vom Typ DATETIME oder z.B. FLOAT verwenden wollen, kann es unter Umständen nötig sein, hinter dem Datumsfeld -2 einzufügen:
CLT_SW_SETUP.INSTALLDATE -2 AS DATETIME
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.
Hinweis:
Je nachdem ob der ACMP oder SQL Server das Datum bestimmt, kann es einen Versatz von zwei Tagen geben, was beim Auslesen korrigiert werden muss.
Abfrage zur Auflistung einer bestimmten Software bei den Clients
Hinweis:
Die nachfolgende Abfrage verhält sich bei Ausführung des SQL Statements fast wie eine Funktion einer normalen Abfrage.
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.
Hinweis:
Diese Abfrage enthält unter anderem als Feld eine ClientID. Damit haben Sie in der Console die Möglichkeit, einen oder mehrere Clients im Result der Abfrage zu markieren, um dann mit diesen ausgewählten Clients Aktionen durchzuführen (z.B. ein Client Command darauf auszuführen).
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.