| DatabaseConnection |
Specifies the database on which the SQL query is to be executed. |
SCCM |
| InstallCollectionDetailsQuery |
SQL query to retrieve additional information about the collections |
Click to view
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 query to retrieve the collections |
Click to view
SELECT
CollectionID ,
Name
FROM
v_Collection
|
| ResourceQuery |
SQL query to retrieve ResourceIDs and computer names |
Click to view
SELECT
ResourceID,
Name0 AS Name
FROM
dbo.v_R_System
|
| SoftwareAllocationQuery |
SQL query to retrieve the software |
Click to view
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 query to determine whether the software has been assigned to the clients |
Click to view
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 |
The name under which the table should be created in the SCCM Manager database.
The tables are prefixed with “SA”.
For example, SA.Default |
Default |
