Wiki source code of Erweiterte Abfragen erstellen

Last modified by Sabrina V. on 2024/12/10 13:59
Show last authors
1 {{aagon.priorisierung}}
2 20
3 {{/aagon.priorisierung}}
4
5 {{aagon.floatingbox/}}
6
7 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.
8
9 {{aagon.infobox}}
10 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.
11 {{/aagon.infobox}}
12
13 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.
14 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.
15
16 {{aagon.infobox}}
17 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.
18 {{/aagon.infobox}}
19
20 {{figure}}
21 (% style="text-align:center" %)
22 [[image:64_Erweiterte Abfrageverwaltung_Feld- und Tabellennamen ermitteln_762.png]]
23
24 {{figureCaption}}
25 Feld- und Tabellennamen ermitteln
26 {{/figureCaption}}
27 {{/figure}}
28
29 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:
30
31 * Erweiterte Abfragen - Standard: SELECT mit Assistent (Abfragedesigner zur Erstellung von erweiterten Abfragen)
32 * Erweiterte Abfragen - Experte** **SELECT oder SP für Ergebnismenge (SQL Editor zum Erstellen von SQL Abfragen)
33
34 {{figure}}
35 (% style="text-align:center" %)
36 [[image:64_Erweiterte Abfrageverwaltung_Erweiterter Abfragemodus_775.png]]
37
38 {{figureCaption}}
39 Erweiterter Abfragemodus
40 {{/figureCaption}}
41 {{/figure}}
42
43 = Erweiterte Abfragen - Standard: SELECT mit Assistent =
44
45 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.
46
47 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.
48 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).
49
50 {{figure}}
51 (% style="text-align:center" %)
52 [[image:64_Erweiterte Abfrageverwaltung_Erweiterte Abfrage mithilfe des Assistenten erstellen_1315.png]]
53
54 {{figureCaption}}
55 Erweiterte Abfrage mithilfe des Assistenten erstellen
56 {{/figureCaption}}
57 {{/figure}}
58
59 {{aagon.infobox}}
60 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.
61 {{/aagon.infobox}}
62
63
64 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).
65 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.
66
67 {{figure}}
68 (% style="text-align:center" %)
69 [[image:64_Erweiterte Abfrageverwaltung_Felder deaktivieren_309.png]]
70
71 {{figureCaption}}
72 Felder deaktivieren
73 {{/figureCaption}}
74 {{/figure}}
75
76 {{aagon.infobox}}
77 Diese Vorgehensweise müssen Sie für alle Tabellen vornehmen, die eine Dopplung der Einträge vorsieht (z.B. ClientID oder HistoryID).
78 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.
79 {{/aagon.infobox}}
80
81 {{aagon.warnungsbox}}
82 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.
83 {{/aagon.warnungsbox}}
84
85 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.
86
87 {{aagon.infobox}}
88 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.
89 {{/aagon.infobox}}
90
91 {{aagon.infobox}}
92 Auch können Sie beispielsweise ein bestehendes SQL-Statement aus dem SQL Server Management Studio herauskopieren und in die erweiterte Abfrage einbauen.
93 {{/aagon.infobox}}
94
95 == Beispiel für eine mit dem Standard-Modus eingefügten erweiterten Abfrage im SQL Tab ==
96
97 ##SELECT SYS_SW_SETUP.NAME, SYS_SW_SETUP.VERSION, COUNT(1) AS AnzahlClients
98 FROM CLT_CLIENTS INNER JOIN
99 CLT_SW_SETUP ON CLT_CLIENTS.CLIENTID = CLT_SW_SETUP.CLIENTID INNER JOIN
100 SYS_SW_SETUP ON CLT_SW_SETUP.SWSETUPID = SYS_SW_SETUP.SWSETUPID
101 WHERE NAME LIKE '%UPDATE%' AND PUBLISHER LIKE '%MICROSOFT%'
102 GROUP BY SYS_SW_SETUP.NAME, SYS_SW_SETUP.VERSION
103 ORDER BY COUNT(1) DESC;##
104
105 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.
106
107 = Erweiterte Abfrage - Experte: SELECT oder SP für Ergebnismenge =
108
109 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.
110 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.
111
112 {{aagon.infobox}}
113 Auch können Sie beispielsweise ein bestehendes SQL-Statement aus dem SQL Server Management Studio herauskopieren und in die erweiterte Abfrage einfügen.
114 {{/aagon.infobox}}
115
116 {{aagon.warnungsbox}}
117 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).
118 {{/aagon.warnungsbox}}
119
120 {{figure}}
121 (% style="text-align:center" %)
122 [[image:64_Erweiterte Abfrageverwaltung_Erweiterte Abfrage erstellen_906.png]]
123
124 {{figureCaption}}
125 Erweiterte Abfrage erstellen
126 {{/figureCaption}}
127 {{/figure}}
128
129 {{aagon.warnungsbox}}
130 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.
131 {{/aagon.warnungsbox}}
132
133 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.
134
135 == Beispiel für eine händisch eingegebene Abfrage (SELECT oder SP für Ergebnismenge) ==
136
137 ##SELECT
138 CLT_CLIENTS_TABLE.COMPUTERNAME,##
139 ## CLT_CLIENTS_TABLE.PRIMARYIP,##
140 ## CLT_SW_SETUP.UNINSTALLPATH##
141 ##WHERE##
142 ## CLT_SW_SETUP.UNINSTALLPATH LIKE 'C:%'##
143 ##FROM CLT_CLIENTS_TABLE,##
144 ## CLT_SW_SETUP##
145
146 Bei dieser Abfrage werden die IP-Adressen und die Computernamen von den Clients ausgeworfen, bei denen der Uninstallpath mit „C:“ beginnt.
147
148 {{aagon.warnungsbox}}
149 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:
150 \\CLT_SW_SETUP.INSTALLDATE -2 AS DATETIME
151 {{/aagon.warnungsbox}}
152
153 = Weitere Beispiele für erweiterte Abfragen =
154
155 == Abfrage für einen Report zur Benachrichtung über neue Managed Software Pakete ==
156
157 ##SELECT SoftwareName, DisplayVersion, cast(releasedate -2 as datetime) as Releasedate,
158 Case##
159 ## When TestRing = 0 Then 'Kein Ring'##
160 ## When TestRing = 1 Then 'Testring 1'##
161 ## When TestRing = 2 Then 'Testring 2'##
162 ## When TestRing = 255 Then 'Freigegeben'##
163 ## End as Testring
164 From VW_ManagedSoftware_Versions
165 Where Testring > 0
166 Order by SoftwareName, DisplayVersion##
167
168 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.
169
170 {{aagon.infobox}}
171 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.
172 {{/aagon.infobox}}
173
174 == Abfrage zur Auflistung einer bestimmten Software bei den Clients ==
175
176 {{aagon.infobox}}
177 Die nachfolgende Abfrage verhält sich bei Ausführung des SQL Statements fast wie eine Funktion einer normalen Abfrage.
178 {{/aagon.infobox}}
179
180 ##SELECT [Computername], [Name], [Version], CLT_CLIENTS.CLIENTID
181 FROM SYS_SW_SETUP##
182 ##LEFT JOIN CLT_SW_SETUP ON CLT_SW_SETUP.SWSETUPID = SYS_SW_SETUP.SWSETUPID##
183 ##INNER JOIN CLT_CLIENTS ON CLT_CLIENTS.CLIENTID = CLT_SW_SETUP.CLIENTID##
184 ##WHERE NAME LIKE ‘Microsoft Office%‘##
185
186 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.
187
188 {{aagon.infobox}}
189 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).
190 {{/aagon.infobox}}
191
192 == Abfrage zur Auflistung aller Client Commands ==
193
194 ##SELECT DISTINCT description
195 FROM SYS_SCRIPTS##
196 ##ORDER BY DESCRIPTION##
197
198 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.
199
200 Das SQL Statement kann als Grundlage für einen Report verwendet werden.
201
202 == Abfrage zur Auflistung von Client Commands inkl. der Verknüpfungsorte (Container, ACMP Kiosk, Job Collection) ==
203
204 ##Select DISTINCT
205 SYS_SCRIPTS.DESCRIPTION As "Client Command Name"##
206 ## ~-~- ,SYS_SCRIPTS.ScriptVersion As "Version",##
207 ## ~-~- Status = Case SYS_SCRIPTS.State ##
208 ## ~-~- When '1' Then 'Erstellen'##
209 ## ~-~- When '2' Then 'Testen' ##
210 ## ~-~- When '3' Then 'Bereit zur Synchronisation'##
211 ## ~-~- When '4' Then 'Synchronisieren' ##
212 ## ~-~- When '5' Then 'Bereit zur Freigabe'##
213 ## ~-~- When '7' Then 'Freigegeben'##
214 ## ~-~- When '8' Then 'Freigabe zurückgezogen' ##
215 ## ~-~- Else 'Unbekannt' End##
216 ## ~-~-,Interaktiv = Case SYS_SCRIPTS.ALLOWINTERACTIVEUSE When 1 Then 'Ja'
217 Else 'Nein' End##
218 ## ,CLT_CONTAINER_STRUCTURE.Name AS "Container Name"##
219 ## ,CLT_CONTAINER_STRUCTURE.Path AS "Container Pfad"##
220 ## ,VW_SYS_Kiosk_Items.ContentName AS "Kiosk Eintrag"##
221 ## ,SYS_Templates.NAME AS "Job Collection Name"##
222
223 ##FROM SYS_SCRIPTS##
224 ## Left JOIN VW_Script_Container_Jobs ON SYS_SCRIPTS.SCRIPTID = VW_Script_Container_Jobs.ScriptId##
225 ## Left JOIN CLT_CONTAINER_STRUCTURE ON CLT_CONTAINER_STRUCTURE.ContainerID = VW_Script_Container_Jobs.ContainerId##
226 ## Left JOIN VW_SYS_Kiosk_Items ON SYS_SCRIPTS.SCRIPTID = VW_SYS_Kiosk_Items.ContentId##
227 ## Left JOIN SYS_Jobs ON SYS_Jobs.ContentId = SYS_SCRIPTS.SCRIPTID##
228 ## Left JOIN SYS_Templates_Jobs ON SYS_Jobs.Id = SYS_Templates_Jobs.JobId##
229 ## Left JOIN SYS_Templates ON SYS_Templates_Jobs.TemplateId = SYS_Templates.Id##
230 ##ORDER BY "Client Command Name" DESC##
231
232 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.
233
234
235 == Abfrage zur Übersicht über Update Collection installierte Windows Updates ==
236
237 ##;WITH cte AS
238 (
239 Select Items.ComputerName,
240 UpdateName = SubItems.NativeMessage,
241 ErrorMsg = SubItems.NativeErrorMessage,
242 DateTime = Cast(SubItems.EndDate -2 as datetime),
243 LetzteBoot = Cast(os.LastBoot -2 as datetime),
244 SubItems.Result,
245 Items.ClientId,
246 ROW_NUMBER() OVER (PARTITION BY
247 Items.ComputerName,SubItems.NativeMessage,SubItems.NativeErrorMessage
248 ORDER BY SubItems.EndDate DESC) AS rn
249 From SYS_Jobs_Logs_SubItems As SubItems
250 Left Join SYS_Jobs_Logs_Items As Items On (SubItems.ParentExecId =
251 Items.ExecId
252 or (SELECT ParentExecId FROM SYS_Jobs_Logs_SubItems WHERE
253 ChildExecId=SubItems.ParentExecId) = Items.ExecId)
254 left join CLT_CLIENTS on CLT_CLIENTS.CLIENTID = Items.ClientId
255 left join CLT_OPERATINGSYSTEM AS OS ON os.CLIENTID = Items.ClientId
256 Where SubItems.JobKind = 58
257 And (SubItems.NativeMessage Like '_%' Or SubItems.NativeErrorMessage Like
258 '_%')
259 And Items.ComputerName Like '%' And SubItems.LastUpdate >= DateAdd(day,-12, GetDate())
260 and ChildExecId not in (select ParentExecId from SYS_Jobs_Logs_SubItems)
261 Group By Items.ComputerName,
262 SubItems.NativeMessage,
263 SubItems.NativeErrorMessage,
264 SubItems.EndDate,
265 SubItems.Result,
266 os.LastBoot,
267 Items.ClientId
268 )
269 SELECT *
270 FROM cte
271 WHERE rn = 1
272 Order By cte.ComputerName, cte.DateTime Desc##
273
274 Ü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.
© Aagon GmbH 2025
Besuchen Sie unsere neue Aagon-Community