SOLVED Sql query - Custom Report for Collections

[Ruslan]

Hello,

I need sql query I'm tring write and looking internet coudn't find. Who is help me this situation.

I need dedicated report, Collection name, {ublisher, Soft name, version, hostname, username, install date, last usage date and install count

 

[Garth]

Hello,

I need sql query I'm tring write and looking internet coudn't find. Who is help me this situation.

I need dedicated report, Collection name, {ublisher, Soft name, version, hostname, username, install date, last usage date and install count

Start by looking at the built-in report, then adjusting them for you needs.

BTW install count will always be 1, so that is as useless column.

 

[Ruslan]

Thanks for answer. I firstly looked built-in report. And also customized for me, but I get error or net result for collection and publisher result.


-- Run this query against the ConfigMgr database
SELECT
csd.Name00,
csd.MachineID,
ExternalFileID = m.FileID,
sf.FileName,
sf.FileVersion,
sf.FileDescription,
sp.CompanyName,
sf.FileSize,
FileInstallationStatus = CASE WHEN si.ClientId IS NOT NULL THEN 'Installed' ELSE 'Not Installed' END,
mu.FullName,
StartDate = LEFT(m.TimeKey, 4) + '/' + RIGHT(m.TimeKey, 2) + '/01',
m.LastUsage,
m.UsageTime,
m.UsageCount,
m.TSUsageCount
FROM dbo.MonthlyUsageSummary AS m
JOIN Computer_System_DATA csd ON csd.MachineID = m.SystemItemKey
JOIN dbo.v_metereduser AS mu ON mu.MeteredUserID = m.MeteredUserID
JOIN SoftwareFile sf
ON sf.FileId = m.FileID
LEFT OUTER JOIN SoftwareProduct sp
ON sp.ProductId = sf.ProductId
LEFT OUTER JOIN SoftwareInventory si
ON si.ClientId = m.SystemItemKey
AND si.FileId = sf.FileId
AND si.ProductId = sf.ProductId
WHERE
-- csd.Name00 = 'name of computer' AND
-- sf.FileName LIKE '%AUTOCAD%' AND
sp.CompanyName LIKE '%AUTOCAD%' AND
-- m.LastUsage > GETDATE()-90 AND
1=1

 

[Ruslan]

I need so table report, can you help me this issue?

 

[Ruslan]

SELECT
SYS.Name0 AS 'Hostname',

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_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
GROUP BY
SYS.Name0, COL.Name, ARP.DisplayName0, ARP.Version0, ARP.Publisher0
ORDER BY
SYS.Name0

Can you help me this query added username, install date of soft, last usage date. I tried many option but coudnt find solution please help me

 

[Garth]

Thanks for answer. I firstly looked built-in report. And also customized for me, but I get error or net result for collection and publisher result.


-- Run this query against the ConfigMgr database

first off it is completely unsupported to query the tables directly. use only the supported views.

SQL Server views - Configuration Manager

A Microsoft SQL Server view is a virtual table whose contents are based on the result from a SQL query.

learn.microsoft.com

Exactly what error do you get?

 

[Garth]

Can you help me this query added username, install date of soft, last usage date. I tried many option but coudnt find solution please help me

It is not possible as there is no link between ARP data and usage data.

On top of that, as this query is written it will have a ton of duplicate data as you are counting each computer within each collection. Installcount will always be 1 because you have host name.

This query (as written) within my tiny lab of 43 computers only take over 5 minutes (and I abandon waiting for the results), as such you need to complete rethink what you are trying to do. As it will not work in anything but the tiniest of labs due to timing out.

You also need to read this post. https://www.recastsoftware.com/resources/how-to-perform-a-basic-software-audit-2/

 

[Ruslan]

first off it is completely unsupported to query the tables directly. use only the supported views.

SQL Server views - Configuration Manager

A Microsoft SQL Server view is a virtual table whose contents are based on the result from a SQL query.

learn.microsoft.com

Exactly what error do you get?

I used Other query but I have so problem when I run on sql or SCCM report panel
I didnt get LAst Used date and Launch Count but I all point added query and my design, can you say me what I wrong wrote

SELECT
SYS.Name0 AS 'Hostname',
COL.Name AS 'Collection',
SYS.User_Name0 AS 'User',
ARP.DisplayName0 AS 'Software Name',
ARP.Version0 AS 'Version',
ARP.Publisher0 AS 'Publisher',
ARP.InstallDate0 AS 'Install Date',
APP.LastUsedTime0 AS 'Last Used Date',
APP.LaunchCount0 AS 'Launch Count'
FROM
v_R_System SYS
LEFT JOIN
v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
LEFT JOIN
v_Collection COL ON FCM.CollectionID = COL.CollectionID
LEFT JOIN
v_GS_ADD_REMOVE_PROGRAMS_64 ARM ON SYS.ResourceID = ARM.ResourceID
LEFT JOIN
v_Add_Remove_Programs ARP ON SYS.ResourceID = ARP.ResourceID
LEFT JOIN
v_GS_CCM_RECENTLY_USED_APPS APP ON SYS.ResourceID = APP.ResourceID
WHERE
ARP.DisplayName0 LIKE '%Adobe Acrobat%Pro'
AND FCM.CollectionID = @DeviceCollectionID
AND NOT (ARP.DisplayName0 LIKE '%Update%' OR ARP.DisplayName0 LIKE '%Service Pack%' OR ARP.DisplayName0 LIKE '%.NET%')
ORDER BY
SYS.Name0;

 

[Garth]

Again, there is no link between ARP and usage data.

 

[Ruslan]

Again, there is no link between ARP and usage data.

Can you say me how link between, I dont know good sql. If you help I appreciate

 

[Garth]

Can you say me how link between, I dont know good sql. If you help I appreciate

You can't link between the two views. There is nothing that will tell you reliably what EXE exist within an ARP title.

 

[Ruslan]

But have you solution sql query,

for collection name, Publisher, Sotware name, Version, hostname, username, install date, last usage date, launch count ?

 

[Ruslan]

You can't link between the two views. There is nothing that will tell you reliably what EXE exist within an ARP title.

But have you solution sql query,

for collection name, Publisher, Sotware name, Version, hostname, username, install date, last usage date, launch count ?

 

[Garth]

But have you solution sql query,

for collection name, Publisher, Sotware name, Version, hostname, username, install date, last usage date, launch count ?

It can't be done!

 

[Ruslan]

Can you say me which view table link for install date, usage count and launch total ?

 

[Garth]

Can you say me which view table link for install date, usage count and launch total ?

Take a look at the ConfigMgr Docs for SQL Views. https://learn.microsoft.com/en-us/m...lviews/sql-server-views-configuration-manager