| DatabaseConnection |
Legt die Datenbank fest auf der die SQL-Abfrage ausgeführt werden sollen. |
SCCM |
| InstallCollectionDetailsQuery |
SQL-Abfrage um zu den Collections weitere Informationen auszulesen |
Aufklappen zum anschauen
SELECT DISTINCT
cd.CollectionID ,
ISNULL(cd.Description, '') AS 'Description' ,
ISNULL(cd.Tag, '') AS 'Tag' ,
ISNULL(ss.[IsTaskSequence], 0) AS 'IsTaskSequence', (CASE WHEN (ss.[IsTaskSequence] IS NOT NULL) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) AS IsStandard
FROM
[CollectionDescriptions] cd
LEFT JOIN
[StandardSoftware] ss
ON
cd.[CollectionID] = ss.CollectionID
UNION
SELECT DISTINCT
ss.CollectionID ,
ISNULL(cd.Description, '') AS 'Description' ,
ISNULL(cd.Tag, '') AS 'Tag' ,
ISNULL(ss.[IsTaskSequence], 0) AS 'IsTaskSequence',
CAST(1 AS BIT) AS IsStandard
FROM
[StandardSoftware] ss
LEFT JOIN
[CollectionDescriptions] cd
ON
cd.[CollectionID] = ss.[CollectionID]
|
| InstallCollectionQuery |
SQL-Abfrage zum auslesen der Collections |
Aufklappen zum anschauen
SELECT
CollectionID ,
Name
FROM
v_Collection
|
| ResourceQuery |
SQL-Abfrage zum auslesen der ResourceIds und Computernamen |
Aufklappen zum anschauen
SELECT
ResourceID,
Name0 AS Name
FROM
dbo.v_R_System
|
| SoftwareAllocationQuery |
SQL-Abfrage zum auslesen der Software |
Aufklappen zum anschauen
SELECT DISTINCT
sa._id AS _id ,
ISNULL(inv.Publisher0, '') + ISNULL(inv.DisplayName0, '') + ISNULL(inv.Version0, '') AS SoftwareKey ,
ISNULL(inv.Publisher0, '') + ' ' + ISNULL(inv.DisplayName0, '') + ' ' + ISNULL(inv.Version0, '') AS SoftwareName,
sa.TargetName AS TargetName ,
sa.TargetID AS TargetID ,
sa.IsObsolete AS IsObsolete ,
sa.IsEnabled AS IsEnabled ,
sa.IsStandard AS IsStandard ,
sa.Category AS Category ,
sa.LastUpdate AS LastUpdate
FROM
[DBO].v_Add_Remove_Programs inv
LEFT JOIN
SCCMManager.dbo.SA_Default sa
ON
sa.SoftwareKey = ISNULL(inv.Publisher0, '') + ISNULL(inv.DisplayName0, '') + ISNULL(inv.Version0, '')
ORDER BY
SoftwareName
|
| SoftwareToKeyResourceQuery |
SQL-Abfrage zum auslesen Informationen ob die Software den Clients zugewiesen wurde |
Aufklappen zum anschauen
SELECT DISTINCT
inv.ResourceID ,
SCCMManager35.dbo.fn_GetMD5Hash(ISNULL(inv.Publisher0, '') + ISNULL(inv.DisplayName0, '') + ISNULL(inv.Version0, '')) AS SoftwareKey ,
ISNULL(inv.Publisher0, '') + ' ' + ISNULL(inv.DisplayName0, '') + ' ' + ISNULL(inv.Version0, '') AS SoftwareName,
0 AS IsMatch ,
sa_matched._id ,
sa_matched.TargetName ,
sa_matched.TargetID ,
sa_matched.IsObsolete ,
sa_matched.IsEnabled ,
sa_matched.IsStandard ,
sa_matched.Category ,
sa_matched.LastUpdate
FROM
[DBO].v_Add_Remove_Programs inv
LEFT JOIN
SCCMManager.[DBO].[SA_Default] sa_matched
ON
sa_matched.SoftwareKey = ISNULL(inv.Publisher0, '') + ISNULL(inv.DisplayName0, '') + ISNULL(inv.Version0, '')
WHERE
inv.ResourceID = {0}
--AND sa_matched.IsEnabled = 1
ORDER BY
SoftwareName
|
| TableName |
Der Name mit dem die Tabelle in der SCCM Manager Datenbank erstellt werden soll.
Die Tabellen bekommen den Prefx "SA".
Z.B. SA.Default |
Default |
