Wiki source code of Erweiterte Abfragen erstellen

Last modified by Sabrina V. on 2025/03/31 05:39

version	line-number	content
1.1	1	{{aagon.priorisierung}}
	2	20
	3	{{/aagon.priorisierung}}
	4
	5	{{aagon.floatingbox/}}
	6
21.1	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.
1.1	8
	9	{{aagon.infobox}}
21.1	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.
1.1	11	{{/aagon.infobox}}
	12
21.1	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.
1.1	14
21.1	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
1.1	17	{{aagon.infobox}}
21.1	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.
1.1	19	{{/aagon.infobox}}
	20
	21	{{figure}}
21.1	22	[[image:64_Erweiterte Abfrageverwaltung_Feld- und Tabellennamen ermitteln_762.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	23
	24	{{figureCaption}}
21.1	25	Determining field and table names
1.1	26	{{/figureCaption}}
	27	{{/figure}}
	28
21.1	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:
1.1	30
21.1	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)
1.1	33
	34	{{figure}}
21.1	35	[[image:64_Erweiterte Abfrageverwaltung_Erweiterter Abfragemodus_775.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	36
	37	{{figureCaption}}
21.1	38	Advanced Query mode
1.1	39	{{/figureCaption}}
	40	{{/figure}}
	41
21.1	42	= Advanced Queries – Basic: SELECT with Assistant =
1.1	43
21.1	44	In this mode, the assistant helps you to create a query. It even helps you to link tables and correlate queries.
1.1	45
21.1	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.
1.1	47
21.1	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
1.1	50	{{figure}}
21.1	51	[[image:64_Erweiterte Abfrageverwaltung_Erweiterte Abfrage mithilfe des Assistenten erstellen_1315.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	52
	53	{{figureCaption}}
21.1	54	Create advanced Query using the wizard
1.1	55	{{/figureCaption}}
	56	{{/figure}}
	57
	58	{{aagon.infobox}}
21.1	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.
1.1	60	{{/aagon.infobox}}
	61
21.1	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).
1.1	63
21.1	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.
1.1	65
	66	{{figure}}
21.1	67	[[image:64_Erweiterte Abfrageverwaltung_Felder deaktivieren_309.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	68
	69	{{figureCaption}}
21.1	70	Disabling fields
1.1	71	{{/figureCaption}}
	72	{{/figure}}
	73
	74	{{aagon.infobox}}
21.1	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.
1.1	77	{{/aagon.infobox}}
	78
	79	{{aagon.warnungsbox}}
21.1	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.
1.1	81	{{/aagon.warnungsbox}}
	82
21.1	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.
1.1	84
	85	{{aagon.infobox}}
21.1	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.
1.1	87	{{/aagon.infobox}}
	88
	89	{{aagon.infobox}}
21.1	90	You can also copy an existing SQL statement from SQL Server Management Studio and incorporate it into the advanced Query.
1.1	91	{{/aagon.infobox}}
	92
21.1	93	== Example of an advanced query inserted in SQL Tab using Standard mode ==
1.1	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
21.1	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.
1.1	104
21.1	105	== Advanced Query – Expert: SELECT or SP for Result Set ==
1.1	106
21.1	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.
1.1	108
21.1	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
1.1	111	{{aagon.infobox}}
21.1	112	You can also copy an existing SQL statement from SQL Server Management Studio and insert it into the advanced query.
1.1	113	{{/aagon.infobox}}
	114
	115	{{aagon.warnungsbox}}
21.1	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).
1.1	117	{{/aagon.warnungsbox}}
	118
	119	{{figure}}
21.1	120	[[image:64_Erweiterte Abfrageverwaltung_Erweiterte Abfrage erstellen_906.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	121
	122	{{figureCaption}}
21.1	123	Create advanced Query
1.1	124	{{/figureCaption}}
	125	{{/figure}}
	126
	127	{{aagon.warnungsbox}}
21.1	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.
1.1	129	{{/aagon.warnungsbox}}
	130
21.1	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.
1.1	132
21.1	133	== Example of a manually entered Query (SELECT or SP for Result Set) ==
1.1	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
21.1	144	Queries of this kind return the IP addresses and computer names of Clients whose uninstall path begins with "C:".
1.1	145
	146	{{aagon.warnungsbox}}
21.1	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:
1.1	148	\\CLT_SW_SETUP.INSTALLDATE -2 AS DATETIME
	149	{{/aagon.warnungsbox}}
	150
21.1	151	= Continuing with examples of advanced Queries =
1.1	152
21.1	153	== Query for a report on notifications of new Managed Software packages ==
1.1	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
21.1	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.
1.1	167
	168	{{aagon.infobox}}
21.1	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.
1.1	170	{{/aagon.infobox}}
	171
21.1	172	== Query for listing specific software on clients ==
1.1	173
	174	{{aagon.infobox}}
21.1	175	When executing the SQL statement, the following query behaves almost like a function of a normal query.
1.1	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
21.1	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.
1.1	185
	186	{{aagon.infobox}}
21.1	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).
1.1	188	{{/aagon.infobox}}
	189
21.1	190	== Query for listing all Client Commands ==
1.1	191
	192	##SELECT DISTINCT description
	193	FROM SYS_SCRIPTS##
	194	##ORDER BY DESCRIPTION##
	195
21.1	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.
1.1	197
21.1	198	The SQL statement can be used as a basis for a report.
1.1	199
21.1	200	== Query to list Client Commands including the link locations (container, ACMP Kiosk, Job Collection) ==
1.1	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
21.1	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.
1.1	231
21.1	232	== Query for an overview of Windows Updates installed via the Update Collection ==
1.1	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
21.1	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.