Ms Visio MS Project Custom Report
Question
Hi All, Is there any SQL query for add or remove Ms Visio and MS Project application with Last login detail of user that is locally installed on user device?
SQL query
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Hi All, Is there any SQL query for add or remove Ms Visio and MS Project application with Last login detail of user that is locally installed on user device?
SQL query
Answers ( 7 )
Thanks Karthik & Guru,
Still it’s not giving me exact detail that is required.
Thanks
Saurabh
Hi,
Starting from SCCM 1906 version (v_Add_Remove_Programs and v_Add_Remove_Programs_X64 views are no more exists in SCCM Database).
You need to use only V_GS_Installed_Software view to get the installed applications details. using this view either 32 bit and 64 bit applications will be captured on same view.
I will share the updated SQL query for your requirement ASAP.
Thanks
Karthikeyan
Declare @Collection varchar(8)
Set @Collection = ‘SMS00001’ — specify scope collection ID
Select
Distinct VRS.Name0 as ‘MachineName’,
Os.Caption0 as ‘OperatingSystem’,
St.SystemType00 as ‘OSType’,
VRS.AD_Site_Name0 as ‘ADSite’,
VRS.Full_Domain_Name0 as ‘Domain’,
VRS.User_Name0 as ‘UserName’,
v_R_User.Mail0 as ‘EMailID’,
App.ARPDisplayName0 as ‘DisplayName’,
App.InstallDate0 as ‘InstalledDate’,
App.ProductVersion0 as ‘Version’
from V_R_System VRS
LEFT JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID
LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID
LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID
LEFT JOIN v_FullCollectionMembership as Col on VRS.ResourceID = Col.ResourceID
LEFT JOIN v_R_User on VRS.User_Name0 = v_R_User.User_Name0
Where (App.ARPDisplayName0 like ‘%visio%’ or App.ARPDisplayName0 like ‘%project%’)
and Col.CollectionID = @Collection
and VRS.Client0 = 1 and VRS.Obsolete0 = 0
order by VRS.Name0, App.ProductVersion0
Thanks Karthik,
I will wait for the query.
Declare @Collection varchar(8)
Set @Collection = ‘SMS00001’ — specify scope collection ID
Select
Distinct VRS.Name0 as ‘MachineName’,
Os.Caption0 as ‘OperatingSystem’,
St.SystemType00 as ‘OSType’,
VRS.AD_Site_Name0 as ‘ADSite’,
VRS.Full_Domain_Name0 as ‘Domain’,
VRS.User_Name0 as ‘UserName’,
v_R_User.Mail0 as ‘EMailID’,
App.ARPDisplayName0 as ‘DisplayName’,
App.InstallDate0 as ‘InstalledDate’,
App.ProductVersion0 as ‘Version’
from V_R_System VRS
LEFT JOIN v_GS_INSTALLED_SOFTWARE App ON VRS.ResourceID = App.ResourceID
LEFT JOIN Computer_System_DATA St on VRS.ResourceID = st.MachineID
LEFT JOIN v_GS_OPERATING_SYSTEM Os on VRS.ResourceID = Os.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on VRS.ResourceID = HWSCAN.ResourceID
LEFT JOIN v_FullCollectionMembership as Col on VRS.ResourceID = Col.ResourceID
LEFT JOIN v_R_User on VRS.User_Name0 = v_R_User.User_Name0
Where (App.ARPDisplayName0 like ‘%visio%’ or App.ARPDisplayName0 like ‘%project%’)
and Col.CollectionID = @Collection
and VRS.Client0 = 1 and VRS.Obsolete0 = 0
order by VRS.Name0, App.ProductVersion0
You may try below one and tweak per your needs. Hope it helps.
SELECT DISTINCT
sys.Netbios_Name0 AS [Host name],
arp.DisplayName0 AS [Software],
arp.ProdID0 AS [Product Code],
arp.Version0 AS [Software Version],
arp.InstallDate0 AS [INstalled Date],
arp.Publisher0 AS [Manufacturer],
last.LastHardwareScan AS [Last HW Scan],
–sys.User_Name0 AS [LastLogOn],
–sys.AD_Site_Name0 AS [AD Site],
–os.Caption0 AS [OperatingSystem],
–OS.Installdate0 as OSInstallDate,
–os.CSDVersion0 AS [ServicePack],
fcm.Domain AS [Domain]
FROM v_R_System sys
INNER JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
INNER JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID
INNER JOIN vWorkstationStatus Last ON sys.ResourceID = last.ResourceID
WHERE
fcm.CollectionID=’SMS00001′
AND (arp.DisplayName0 LIKE (‘Visio%’)
OR arp.DisplayName0 LIKE (‘Project%’)
OR arp.DisplayName0 LIKE (‘Winzip%’))
ORDER BY arp.DisplayName0, [Software Version]
Thanku Guru,
But it’s not giving me the exact report that I want.