SOLVED sql query report for sccm

[Ruslan]

Hello,

I'm new working with sql query. Who help me this situation. I wrote query didn't get any error sql studio but on sccm didn't get result.


DECLARE @Collection NVARCHAR(255)


SELECT
SYS.Name0 AS 'Hostname',
ARP.DisplayName0 AS 'SoftwareName',
ARP.Version0 AS 'Version'

FROM
v_R_System SYS
JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
JOIN
v_Collection COL ON FCM.CollectionID = COL.CollectionID
JOIN
v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID

join v_MeteredFiles mf on arp.DisplayName0 =mf.MeteredFileID

WHERE
mf.SecurityKey = arp.ProdID0
AND fcm.Collectionid= @Collection
GROUP BY
SYS.Name0, ARP.DisplayName0, ARP.Version0
ORDER BY
SYS.Name0

 

[Ruslan]

Hello,

I'm new working with sql query. Who help me this situation. I wrote query didn't get any error sql studio but on sccm didn't get result.


DECLARE @Collection NVARCHAR(255)


SELECT
SYS.Name0 AS 'Hostname',
ARP.DisplayName0 AS 'SoftwareName',
ARP.Version0 AS 'Version'

FROM
v_R_System SYS
JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
JOIN
v_Collection COL ON FCM.CollectionID = COL.CollectionID
JOIN
v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID

join v_MeteredFiles mf on arp.DisplayName0 =mf.MeteredFileID

WHERE
mf.SecurityKey = arp.ProdID0
AND fcm.Collectionid= @Collection
GROUP BY
SYS.Name0, ARP.DisplayName0, ARP.Version0
ORDER BY
SYS.Name0

this query so working, but when add softweare metering list dont give result

declare @DeviceCollectionID nvarchar;

SELECT
SYS.Name0 AS 'ComputerName',
--COL.Name AS 'CollectionName',
ARP.DisplayName0 AS 'SoftwareName',
ARP.Version0 AS 'Version'
-- ARP.Publisher0 AS 'Publisher',
--COUNT(ARP.ResourceID) AS 'InstallationCount'
FROM
v_R_System SYS
JOIN
v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID
JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
JOIN
v_Collection COL ON FCM.CollectionID = COL.CollectionID
WHERE
ARP.DisplayName0 LIKE '%Adobe Creative Cloud%'
OR DisplayName0 LIKE '%Adobe Acrobat%Pro'
AND FCM.CollectionID = @DeviceCollectionID
GROUP BY
SYS.Name0, COL.Name, ARP.DisplayName0, ARP.Version0, ARP.Publisher0;

 

[Garth]

Hello,

I'm new working with sql query. Who help me this situation. I wrote query didn't get any error sql studio but on sccm didn't get result.


DECLARE @Collection NVARCHAR(255)


SELECT
SYS.Name0 AS 'Hostname',
ARP.DisplayName0 AS 'SoftwareName',
ARP.Version0 AS 'Version'

FROM
v_R_System SYS
JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
JOIN
v_Collection COL ON FCM.CollectionID = COL.CollectionID
JOIN
v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID

join v_MeteredFiles mf on arp.DisplayName0 =mf.MeteredFileID

WHERE
mf.SecurityKey = arp.ProdID0
AND fcm.Collectionid= @Collection
GROUP BY
SYS.Name0, ARP.DisplayName0, ARP.Version0
ORDER BY
SYS.Name0

The follow part of the query is not true and will not work. Please review the data within both views.

arp.DisplayName0 =mf.MeteredFileID

 

[Garth]

this query so working, but when add softweare metering list dont give result

declare @DeviceCollectionID nvarchar;

SELECT
SYS.Name0 AS 'ComputerName',
--COL.Name AS 'CollectionName',
ARP.DisplayName0 AS 'SoftwareName',
ARP.Version0 AS 'Version'
-- ARP.Publisher0 AS 'Publisher',
--COUNT(ARP.ResourceID) AS 'InstallationCount'
FROM
v_R_System SYS
JOIN
v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID
JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
JOIN
v_Collection COL ON FCM.CollectionID = COL.CollectionID
WHERE
ARP.DisplayName0 LIKE '%Adobe Creative Cloud%'
OR DisplayName0 LIKE '%Adobe Acrobat%Pro'
AND FCM.CollectionID = @DeviceCollectionID
GROUP BY
SYS.Name0, COL.Name, ARP.DisplayName0, ARP.Version0, ARP.Publisher0;

As stated before, There is no direct link between software metering data and ARP data.