Tuesday, July 25, 2017

SQL query for PROJECT and VISIO

PROJECT : -


Select distinct sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0
 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

 where
 arp.DisplayName0 like '%Project%' and arp.DisplayName0 Not Like '%Update%'
and arp.DisplayName0 Not Like '%Security%'
and arp.DisplayName0 Not Like '%Service Pack%'
 order by sys.Netbios_Name0


'Project' with Display Name :-

Select distinct sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0,arp.Version0
 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

 where
 arp.DisplayName0 = 'Microsoft Office Project Standard 2010'or
 arp.DisplayName0 =  'Microsoft Project Professional 2010' or
 arp.DisplayName0 = 'Microsoft Project Standard 2010' or
 arp.DisplayName0 = 'Microsoft Office Project Standard 2010'
  order by sys.Netbios_Name0

VISIO:-


Select distinct sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, arp.DisplayName0, arp.installdate0
 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

 where
 arp.DisplayName0 like '%Visio%' and arp.DisplayName0 Not Like '%Update%'
and arp.DisplayName0 Not Like '%Security%'
and arp.DisplayName0 Not Like '%Service Pack%'
 order by sys.Netbios_Name0



VISIO With Display Names:-


SELECT DISTINCT
v_R_System.Netbios_Name0 as 'ComputerName' ,
v_R_System.User_Name0,
v_r_system.Full_Domain_Name0 as 'Domain Name'
,v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
,v_GS_ADD_REMOVE_PROGRAMS.Version0
,V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE]

FROM v_R_System
inner Join v_GS_ADD_REMOVE_PROGRAMS
on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID =  v_R_System.ResourceID)
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID

Where

v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Viewer 2013'
 or
 v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Office Visio 2010' or
 v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Standard 2010' or
 v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Viewer 2016' or
 v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Microsoft Visio Professional 2010'

No comments:

Post a Comment

site backup job manually

Start a scheduled SCCM 2012 site backup job manually You might want to start a scheduled SCCM 2012 site backup job manually. off co...