Wiki source code of Erweiterte Abfragen erstellen

Last modified by Sabrina V. on 2025/03/31 05:39
Show last authors
1 {{aagon.priorisierung}}
2 20
3 {{/aagon.priorisierung}}
4
5 {{aagon.floatingbox/}}
6
7 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.
8
9 {{aagon.infobox}}
10 With advanced queries you can basically query all data in the ACMP database. This requires knowledge of the database schema and the meaning of the data. It also requires more knowledge of SQL structures.
11 {{/aagon.infobox}}
12
13 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.
14
15 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.
16
17 {{aagon.infobox}}
18 If you want to determine certain fields or tables for the advanced query, you can do this using the normal query and the associated filters. To do this, drag the field you want to determine into the OR area, right-click on the field and open the Copy item in the context menu. This will provide you with the SQL Query for the stored filter. You will then find the field and table names in the lower area of the Where Clause.
19 {{/aagon.infobox}}
20
21 {{figure}}
22 [[image:64_Erweiterte Abfrageverwaltung_Feld- und Tabellennamen ermitteln_762.png||data-xwiki-image-style-alignment="center"]]
23
24 {{figureCaption}}
25 Determining field and table names
26 {{/figureCaption}}
27 {{/figure}}
28
29 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:
30
31 * Advanced Queries - Basic: SELECT with assistant (Query Designer for creating advanced Queries)
32 * Advanced Queries - Expert** **SELECT or SP for result set (SQL Editor for creating SQL Queries)
33
34 {{figure}}
35 [[image:64_Erweiterte Abfrageverwaltung_Erweiterter Abfragemodus_775.png||data-xwiki-image-style-alignment="center"]]
36
37 {{figureCaption}}
38 Advanced Query mode
39 {{/figureCaption}}
40 {{/figure}}
41
42 = Ad**vanced Queries – Basic: SELECT with Assistant** =
43
44 In this mode, the assistant helps you to create a query. It even helps you to link tables and correlate queries.
45
46 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.
47
48 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).
49
50 {{figure}}
51 [[image:64_Erweiterte Abfrageverwaltung_Erweiterte Abfrage mithilfe des Assistenten erstellen_1315.png||data-xwiki-image-style-alignment="center"]]
52
53 {{figureCaption}}
54 Create advanced Query using the wizard
55 {{/figureCaption}}
56 {{/figure}}
57
58 {{aagon.infobox}}
59 Please note that for a JOIN, you must create a relation between the tables by linking the primary keys of the tables. In this example, it is the CLIENTID.
60 {{/aagon.infobox}}
61
62 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).
63
64 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.
65
66 {{figure}}
67 [[image:64_Erweiterte Abfrageverwaltung_Felder deaktivieren_309.png||data-xwiki-image-style-alignment="center"]]
68
69 {{figureCaption}}
70 Disabling fields
71 {{/figureCaption}}
72 {{/figure}}
73
74 {{aagon.infobox}}
75 You will need to repeat this procedure for all tables that allow duplicate entries (e.g. ClientID or HistoryID).
76 The key fields may have different names. This means that the connection entries in the columns may have different names but still need to be combined with each other. For example, a field may be called ClientID or just ID.
77 {{/aagon.infobox}}
78
79 {{aagon.warnungsbox}}
80 The meaning behind a field is important: ID can be a client in one table, an Asset in another, and a file repository or a Job in yet another. Often, but not always, the field names in the schema are self-explanatory, in which case the IDs are named appropriately (e.g. ClientID or AssetID). Please note this when connecting the table fields. You will not receive error messages, only results where you cannot always immediately see that the connection is incorrect.
81 {{/aagon.warnungsbox}}
82
83 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.
84
85 {{aagon.infobox}}
86 The entries in the table on the right are sorted alphabetically. All entries that were created as custom fields via the settings were stored in the database with a randomly generated GUID. So, for example, if you discover a field beginning with "CF_VAL" (CustomField_Value) or "CF_HIS" (CustomField_History), it is one of the fields you created.
87 {{/aagon.infobox}}
88
89 {{aagon.infobox}}
90 You can also copy an existing SQL statement from SQL Server Management Studio and incorporate it into the advanced Query.
91 {{/aagon.infobox}}
92
93 == Example **of an advanced query inserted in SQL Tab using Standard mode** ==
94
95 ##SELECT SYS_SW_SETUP.NAME, SYS_SW_SETUP.VERSION, COUNT(1) AS AnzahlClients
96 FROM CLT_CLIENTS INNER JOIN
97 CLT_SW_SETUP ON CLT_CLIENTS.CLIENTID = CLT_SW_SETUP.CLIENTID INNER JOIN
98 SYS_SW_SETUP ON CLT_SW_SETUP.SWSETUPID = SYS_SW_SETUP.SWSETUPID
99 WHERE NAME LIKE '%UPDATE%' AND PUBLISHER LIKE '%MICROSOFT%'
100 GROUP BY SYS_SW_SETUP.NAME, SYS_SW_SETUP.VERSION
101 ORDER BY COUNT(1) DESC;##
102
103 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.
104
105 == **Advanced Query – Expert: SELECT or SP for Result Set** ==
106
107 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.
108
109 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.
110
111 {{aagon.infobox}}
112 You can also copy an existing SQL statement from SQL Server Management Studio and insert it into the advanced query.
113 {{/aagon.infobox}}
114
115 {{aagon.warnungsbox}}
116 Depending on the tool you are copying an SQL statement from, the inserted text may contain invisible control characters that can cause unexpected error messages when executing the advanced query (for example, in Winword or PDF).
117 {{/aagon.warnungsbox}}
118
119 {{figure}}
120 [[image:64_Erweiterte Abfrageverwaltung_Erweiterte Abfrage erstellen_906.png||data-xwiki-image-style-alignment="center"]]
121
122 {{figureCaption}}
123 Create advanced Query
124 {{/figureCaption}}
125 {{/figure}}
126
127 {{aagon.warnungsbox}}
128 Only use the expert mode to create an advanced Query if you have sufficient knowledge of SQL databases. Otherwise you could unintentionally delete data that would irrevocably disappear from the database. You could also run the risk of creating Queries that are very resource-intensive and would slow down both the SQL Server and the ACMP Server.
129 {{/aagon.warnungsbox}}
130
131 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.
132
133 == Example of a manually entered Query (SELECT or SP for Result Set) ==
134
135 ##SELECT
136 CLT_CLIENTS_TABLE.COMPUTERNAME,##
137 ## CLT_CLIENTS_TABLE.PRIMARYIP,##
138 ## CLT_SW_SETUP.UNINSTALLPATH##
139 ##WHERE##
140 ## CLT_SW_SETUP.UNINSTALLPATH LIKE 'C:%'##
141 ##FROM CLT_CLIENTS_TABLE,##
142 ## CLT_SW_SETUP##
143
144 Queries of this kind return the IP addresses and computer names of Clients whose uninstall path begins with "C:".
145
146 {{aagon.warnungsbox}}
147 Please note that you have to correct the date for time values such as InstallDateTime. This is because the programming languages used (DELPHI, C# and TSQL) use two different time calculations and work with a two-day offset. If you want to use a date field of the type DATETIME or FLOAT in your query, for example, it may be necessary to insert -2 after the date field:
148 \\CLT_SW_SETUP.INSTALLDATE -2 AS DATETIME
149 {{/aagon.warnungsbox}}
150
151 = **Continuing with examples of advanced Queries** =
152
153 == Q**uery for a report on notifications of new Managed Software packages** ==
154
155 ##SELECT SoftwareName, DisplayVersion, cast(releasedate -2 as datetime) as Releasedate,
156 Case##
157 ## When TestRing = 0 Then 'Kein Ring'##
158 ## When TestRing = 1 Then 'Testring 1'##
159 ## When TestRing = 2 Then 'Testring 2'##
160 ## When TestRing = 255 Then 'Freigegeben'##
161 ## End as Testring
162 From VW_ManagedSoftware_Versions
163 Where Testring > 0
164 Order by SoftwareName, DisplayVersion##
165
166 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.
167
168 {{aagon.infobox}}
169 Depending on whether the ACMP or SQL Server determines the date, there may be an offset of two days, which must be corrected when reading.
170 {{/aagon.infobox}}
171
172 == **Query for listing specific software on clients** ==
173
174 {{aagon.infobox}}
175 When executing the SQL statement, the following query behaves almost like a function of a normal query.
176 {{/aagon.infobox}}
177
178 ##SELECT [Computername], [Name], [Version], CLT_CLIENTS.CLIENTID
179 FROM SYS_SW_SETUP##
180 ##LEFT JOIN CLT_SW_SETUP ON CLT_SW_SETUP.SWSETUPID = SYS_SW_SETUP.SWSETUPID##
181 ##INNER JOIN CLT_CLIENTS ON CLT_CLIENTS.CLIENTID = CLT_SW_SETUP.CLIENTID##
182 ##WHERE NAME LIKE ‘Microsoft Office%‘##
183
184 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.
185
186 {{aagon.infobox}}
187 This Query contains, among other things, a ClientID field. This allows you to select one or more Clients in the Query Result in the console and then perform actions on these selected Clients (e.g. execute a Client Command on them).
188 {{/aagon.infobox}}
189
190 == **Query for listing all Client Commands** ==
191
192 ##SELECT DISTINCT description
193 FROM SYS_SCRIPTS##
194 ##ORDER BY DESCRIPTION##
195
196 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.
197
198 The SQL statement can be used as a basis for a report.
199
200 == **Query to list Client Commands including the link locations (container, ACMP Kiosk, Job Collection)** ==
201
202 ##Select DISTINCT
203 SYS_SCRIPTS.DESCRIPTION As "Client Command Name"##
204 ## ~-~- ,SYS_SCRIPTS.ScriptVersion As "Version",##
205 ## ~-~- Status = Case SYS_SCRIPTS.State ##
206 ## ~-~- When '1' Then 'Erstellen'##
207 ## ~-~- When '2' Then 'Testen' ##
208 ## ~-~- When '3' Then 'Bereit zur Synchronisation'##
209 ## ~-~- When '4' Then 'Synchronisieren' ##
210 ## ~-~- When '5' Then 'Bereit zur Freigabe'##
211 ## ~-~- When '7' Then 'Freigegeben'##
212 ## ~-~- When '8' Then 'Freigabe zurückgezogen' ##
213 ## ~-~- Else 'Unbekannt' End##
214 ## ~-~-,Interaktiv = Case SYS_SCRIPTS.ALLOWINTERACTIVEUSE When 1 Then 'Ja'
215 Else 'Nein' End##
216 ## ,CLT_CONTAINER_STRUCTURE.Name AS "Container Name"##
217 ## ,CLT_CONTAINER_STRUCTURE.Path AS "Container Pfad"##
218 ## ,VW_SYS_Kiosk_Items.ContentName AS "Kiosk Eintrag"##
219 ## ,SYS_Templates.NAME AS "Job Collection Name"##
220
221 ##FROM SYS_SCRIPTS##
222 ## Left JOIN VW_Script_Container_Jobs ON SYS_SCRIPTS.SCRIPTID = VW_Script_Container_Jobs.ScriptId##
223 ## Left JOIN CLT_CONTAINER_STRUCTURE ON CLT_CONTAINER_STRUCTURE.ContainerID = VW_Script_Container_Jobs.ContainerId##
224 ## Left JOIN VW_SYS_Kiosk_Items ON SYS_SCRIPTS.SCRIPTID = VW_SYS_Kiosk_Items.ContentId##
225 ## Left JOIN SYS_Jobs ON SYS_Jobs.ContentId = SYS_SCRIPTS.SCRIPTID##
226 ## Left JOIN SYS_Templates_Jobs ON SYS_Jobs.Id = SYS_Templates_Jobs.JobId##
227 ## Left JOIN SYS_Templates ON SYS_Templates_Jobs.TemplateId = SYS_Templates.Id##
228 ##ORDER BY "Client Command Name" DESC##
229
230 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.
231
232 == **Query for an overview of Windows Updates installed via the Update Collection** ==
233
234 ##;WITH cte AS
235 (
236 Select Items.ComputerName,
237 UpdateName = SubItems.NativeMessage,
238 ErrorMsg = SubItems.NativeErrorMessage,
239 DateTime = Cast(SubItems.EndDate -2 as datetime),
240 LetzteBoot = Cast(os.LastBoot -2 as datetime),
241 SubItems.Result,
242 Items.ClientId,
243 ROW_NUMBER() OVER (PARTITION BY
244 Items.ComputerName,SubItems.NativeMessage,SubItems.NativeErrorMessage
245 ORDER BY SubItems.EndDate DESC) AS rn
246 From SYS_Jobs_Logs_SubItems As SubItems
247 Left Join SYS_Jobs_Logs_Items As Items On (SubItems.ParentExecId =
248 Items.ExecId
249 or (SELECT ParentExecId FROM SYS_Jobs_Logs_SubItems WHERE
250 ChildExecId=SubItems.ParentExecId) = Items.ExecId)
251 left join CLT_CLIENTS on CLT_CLIENTS.CLIENTID = Items.ClientId
252 left join CLT_OPERATINGSYSTEM AS OS ON os.CLIENTID = Items.ClientId
253 Where SubItems.JobKind = 58
254 And (SubItems.NativeMessage Like '_%' Or SubItems.NativeErrorMessage Like
255 '_%')
256 And Items.ComputerName Like '%' And SubItems.LastUpdate >= DateAdd(day,-12, GetDate())
257 and ChildExecId not in (select ParentExecId from SYS_Jobs_Logs_SubItems)
258 Group By Items.ComputerName,
259 SubItems.NativeMessage,
260 SubItems.NativeErrorMessage,
261 SubItems.EndDate,
262 SubItems.Result,
263 os.LastBoot,
264 Items.ClientId
265 )
266 SELECT *
267 FROM cte
268 WHERE rn = 1
269 Order By cte.ComputerName, cte.DateTime Desc##
270
271 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.
© Aagon GmbH 2025
Besuchen Sie unsere neue Aagon-Community