SOLVED Report Fetching through SQL Query

[vin418]

Hi Team,

I need help in fetching report from SCCM by using SQL query, the report will include system name, Site Name, Last logon user, last modified date, file path, software name and version.
I was able to fetch the report but not able to get the correct version, when included it took 20 min to give the result.

Below is the SQL Query:

SELECT
v_GS_SYSTEM.Name0 as 'Computer Name',
ActiveDirectorySites.ADSiteName as 'AD Name',
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 as 'Application Name',
v_SoftwareProduct.ProductVersion as 'Version',

COUNT (*) as Qty

FROM
v_SoftwareProduct,ActiveDirectorySites, v_GS_SYSTEM
INNER JOIN
v_GS_ADD_REMOVE_PROGRAMS ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID

WHERE
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like '%Measurlink%'

GROUP BY
v_GS_SYSTEM.Name0,
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,
ActiveDirectorySites.ADSiteName,
v_SoftwareProduct.ProductVersion

 

[Garth]

It is not supported to query the tables directly.
The query took a long time as you don't have join setup.

Select
R.Name0 as 'Computer Name',
R.AD_Site_Name0 as 'AD Name',
ARP.DisplayName0 as 'Application Name',
ARP.Version0 as 'Version',
COUNT (*) as Qty
FROM
v_R_System R
INNER JOIN v_ADD_REMOVE_PROGRAMS ARP ON R.ResourceID = ARP.ResourceID
WHERE
ARP.DisplayName0 like '%Measurlink%'
GROUP BY
R.Name0,
R.AD_Site_Name0,
ARP.DisplayName0,
ARP.Version0

 

[vin418]

This worked perfectly as a charm.