Monday, July 31, 2017

Multiple SQL Queries and Queries Views for SCCM 2012

This summary is not available. Please click here to view the post.

List of Packages in SCCM

List of Packages  in SCCM
---------------------------------------------
SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID

SCCM Client Status report.



SCCM Client Status report.

SELECT S.Name0 as 'Computer Name', S.User_Name0 as 'User Name',
case when S.Decommissioned0='0'
then 'Not Decommissioned' when S.Decommissioned0='1'
then 'Decommissioned' end as 'Decommissinoed Status',
S.Last_Logon_Timestamp0,
Case when CS.ClientActiveStatus='1'
then 'Active' When CS.ClientActiveStatus='0'
then 'Inactive' end as 'Client Active Status'
FROM v_R_System S inner Join v_CH_ClientSummary CS on S.ResourceId=CS.ResourceID

Detection rule for Outlook and Skype for SCCM 2012



Detection rule for Outlook and Skype SCCM 2012 :


%ProgramFiles%\Microsoft Office\Office16
lync.exe

%ProgramFiles%\Microsoft Office\Office16
OUTLOOK.EXE


WQL- List Of Machines With Workstation 10.0

WQL- List Of Machines With Workstation 10.0

select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client
from SMS_R_System
where OperatingSystemNameandVersion like '%Workstation 10.0%'







Tuesday, July 25, 2017

Region wise available machines report

Region wise available machines report :-



select distinct

sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0,
Usr.Mail0,
sys.USer_domain0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0


where sys.resource_domain_or_workgr0 = 'AS'

Drivers (Disk) Space information for machines & Collection

Drivers (Disk ) Space information for machines :


Select v_R_System.name0,

v_R_System.Resource_Domain_OR_Workgr0 as Domain,
v_GS_LOGICAL_DISK.FreeSpace0,
v_GS_LOGICAL_DISK.Size0,
v_GS_LOGICAL_DISK.Caption0,
v_GS_LOGICAL_DISK.DriveType0,
v_R_System.Last_Logon_Timestamp0

from v_R_System

inner Join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID

where v_GS_LOGICAL_DISK.DriveType0 = '3'
order by v_R_System.name0






For Collection :-



SELECT distinct
SYS.Name,
v_r_system.user_name0,
sys.SiteCode,
SYS.Domain,
LDISK.Description0,
LDISK.DeviceID0,
LDISK.VolumeName0,
LDISK.FileSystem0,
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',
LDISK.Size0,
LDISK.FreeSpace0

FROM
v_FullCollectionMembership_Valid SYS
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
join v_r_system on SYS.ResourceID= v_r_system.resourceID
Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
WHERE LDISK.DriveType0 = 3 and  sys.CollectionID = 'SMS00001'

Particular ( Machine wise ) machine software installed report

Particular ( Machine wise )  machine software installed report :-


SELECT DISTINCT
  v_R_System.Netbios_Name0 AS "Machine Name",
  v_r_system.User_Name0,
  ARP.DisplayName0 AS"Product Name",
  ARP.Version0 AS"Version" ,
  ARP.InstallDate0 AS"Installation Date",
  ARP.ProdID0 AS"Product ID",
 ARP.Publisher0 AS"Publisher",
v_r_system.Last_Logon_Timestamp0
FROM  v_R_System
  JOIN v_GS_ADD_REMOVE_PROGRAMS ARP on v_R_System.ResourceID = ARP.ResourceID
  JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
 
                WHERE
  v_R_System.Netbios_Name0 IN ('USWSTLUSX37806')  
AND ARP.DisplayName0 NOT LIKE 'Hotfix for %'
AND ARP.DisplayName0 NOT LIKE 'Security Update for %'
AND ARP.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND ARP.DisplayName0 NOT LIKE 'Update for Office %'
AND ARP.DisplayName0 NOT LIKE 'Update for Outlook %'
AND ARP.DisplayName0 NOT LIKE 'Update for Windows %'
AND ARP.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND ARP.DisplayName0 NOT LIKE 'Hotfix%'
ORDER BY v_R_System.Netbios_Name0

Outlook App enforcement report report

Outlook  App enforcement report report :-


select distinct  fn.machinename as 'ComputerName',vrs.user_name0 as 'UserID' ,fn.applicationname as 'DisplayName', fn.deploymenttypename as deploymenttypename,
errorcode,
case when fn.appstate='1000' then 'Success'
when fn.appstate='1001' then 'Already Compliant'
when fn.appstate='1002' then 'Simulate Success'
when fn.appstate='2000' then 'In progress'
when fn.appstate='2001' then 'Waiting for content'
when fn.appstate='2002' then 'Installing'
when fn.appstate='2003' then 'Restart to continue'
when fn.appstate='2004' then 'Waiting for maintenance window'
when fn.appstate='2005' then 'Waiting for schedule'
when fn.appstate='2006' then 'Downloading dependent content'
when fn.appstate='2007' then 'Installing dependent content'
when fn.appstate='2008' then 'Restart to complete'
when fn.appstate='2009' then 'Content downloaded'
when fn.appstate='2010' then 'Waiting for update'
when fn.appstate='2011' then 'Waiting for user session reconnect'
when fn.appstate='2012' then 'Waiting for user logoff'
when fn.appstate='2013' then 'Waiting for user logon'
when fn.appstate='2014' then 'Waiting To Install'
when fn.appstate='2015' then 'Waiting Retry'
when fn.appstate='2016' then 'Waiting For Presentation Mode'
when fn.appstate='2017' then 'Waiting For Orchestration'
when fn.appstate='2018' then 'Waiting For Network'
when fn.appstate='2019' then 'Pending App-V Virtual Environment Update'
when fn.appstate='2020' then 'Updating App-V Virtual Environment'
when fn.appstate='3000' then 'Requirements not met'
when fn.appstate='3001' then 'Host Platform Not Applicable'
when fn.appstate='4000' then 'Unknown'
when fn.appstate='5000' then 'Deployment failed'
when fn.appstate='5001' then 'Evaluation failed'
when fn.appstate='5002' then 'Deployment failed'
when fn.appstate='5003' then 'Failed to locate content'
when fn.appstate='5004' then 'Dependency installation failed'
when fn.appstate='5005' then 'Failed to download dependent content'
when fn.appstate='5006' then 'Conflicts with another application deployment'
when fn.appstate='5007' then 'Waiting Retry'
when fn.appstate='5008' then 'Failed to uninstall superseded deployment type'
when fn.appstate='5009' then 'Failed to download superseded deployment type'
when fn.appstate='5010' then 'Failed to updating App-V Virtual Environment'
end as 'Laststatusmessage'

 from fn_AppDTClientSummarizedState('') fn join v_r_system vrs on vrs.name0=fn.machinename
  where fn.applicationname  like 'Microsoft Outlook 2016%' and
  fn.applicationname not like 'Microsoft Outlook 2016 XXXXXXXXX'
  and fn.appstate not like '3000' and fn.deploymenttypename like '%Install%'

  order by fn.machinename

Query for installed machines report 'Adobe Flash Player '

Query for  installed machines report  'Adobe Flash Player ' :-



Select distinct sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, arp.DisplayName0
 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 'Adobe Flash Player %'
 order by sys.Netbios_Name0

LinkPoint installed machines report

    LinkPoint installed machines report :-




Select distinct sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, arp.DisplayName0 ,
 v_R_User.Full_User_Name0, v_R_User.Mail0,arp.installdate0

 FROM v_r_system sys
 JOIN v_R_User on sys.User_Name0 = v_R_User.User_Name0
 JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
 JOIN v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID
 WHERE DisplayName0 like 'LinkPoint%'
 order by sys.Netbios_Name0

Installed software's report for the collection ID

Installed software's report for the collection ID :-

Declare @CollID char(8)      
Set @CollID = 'ING00898'  
SELECT Distinct
COMP.Name0 AS 'Machine Name',      
 COMP.UserName0 AS 'Username',
 v_R_system.User_Domain0 AS ' Domain Name',

COMP.Manufacturer0 AS 'Make',      
 COMP.Model0 AS 'Model',      
COMP.SystemType0 AS 'Architecture',    
COMP.TotalPhysicalMemory0/1024 AS 'RAM',      
 ARP.Publisher0 AS 'Publisher',      
ARP.DisplayName0 AS 'Program',    
ARP.Version0 AS 'Version',      
ARP.InstallDate0 AS 'Install Date',    
 ARP.ProdID0 AS 'Product ID'

FROM v_Add_Remove_Programs ARP      
JOIN v_FullCollectionMembership FCM            
on ARP.ResourceID = FCM.ResourceID      
JOIN v_GS_COMPUTER_SYSTEM COMP          
on ARP.ResourceID = COMP.ResourceID
JOIN v_R_System on v_R_System.ResourceID = ARP.ResourceID      
WHERE FCM.CollectionID = @CollID          
AND ARP.DisplayName0 IS NOT NULL          
AND ARP.ProdId0 NOT LIKE '%(KB%)%'          
AND ARP.DisplayName0 NOT LIKE '%Update%'

IE Installed version with OS

IE Installed version with OS :-


SELECT DISTINCT
dbo.v_R_System.Netbios_Name0,
ARP.FileName,
ARP.FileVersion,
CASE
WHEN ARP.FileVersion LIKE '4.%' THEN 'IE 4'
WHEN ARP.FileVersion LIKE '5.%' THEN 'IE 5'
WHEN ARP.FileVersion LIKE '6.%' THEN 'IE 6'
WHEN ARP.FileVersion LIKE '7.%' THEN 'IE 7'
WHEN ARP.FileVersion LIKE '8.%' THEN 'IE 8'
WHEN ARP.FileVersion LIKE '9.%' THEN 'IE 9'
When ARP.FileVersion LIKE '10.%' Then 'IE 10'
When ARP.FileVersion LIKE '11.%' Then 'IE 11'
ELSE '??' END AS 'Internet Explorer Ver',
dbo.v_FullCollectionMembership.SiteCode,

CASE
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'B%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'A%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'C%' THEN 'Japan'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'D%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'E%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'F%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'G%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'H%' THEN 'Belgium'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'I%' THEN 'Germany'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'J%' THEN 'France'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'K%' THEN 'Italy'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'L%' THEN 'PORTUGAL'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'M%' THEN 'SPAIN'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'N%' THEN 'UK'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'O%' THEN 'UK'
ELSE 'Unidentified' END AS 'Country',
v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_R_System

 INNER JOIN dbo.v_GS_SoftwareFile AS ARP ON dbo.v_R_System.ResourceID = ARP.ResourceID
 INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID
 LEFT JOIN dbo.v_GS_WORKSTATION_STATUS AS HWSCAN ON dbo.v_R_System.ResourceID = HWSCAN.ResourceID
 Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
WHERE (ARP.FileName = 'iexplore.exe') AND (ARP.FilePath LIKE '%:\prog%internet%')
 and (sitecode != 'NULL') and (v_GS_OPERATING_SYSTEM.Caption0 != '%server%')
GROUP BY ARP.FileName, ARP.FileVersion,
dbo.v_FullCollectionMembership.SiteCode, dbo.v_R_System.Netbios_Name0,ARP.FilePath,v_GS_OPERATING_SYSTEM.Caption0

Count of installed software's

Count of installed software's  :

select
    DisplayName0,
    Version0,
    Count (Distinct arp.ResourceID) AS 'No Of Installed'
From
    dbo.v_Add_Remove_Programs ARP
Where
    DisplayName0 in ( 'Microsoft Office Professional Edition 2003','Microsoft Office Standard Edition 2003','Microsoft Office Enterprise 2007',
'Microsoft Office Standard 2007','Microsoft Office Professional Plus 2007',
'Microsoft Office Project Professional 2003','Microsoft Office Project Standard 2003',
'Microsoft Office Visio Professional 2003',
'Microsoft Office Visio Professional 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Standard 2007',
'Microsoft Visual Studio 2005','Microsoft Visual Studio 2008',
'Microsoft Visual Studio 2005 Professional Edition – ENU','Microsoft Visual Studio 2005 Team Suite – ENU','Microsoft Visual Studio 2008 Professional Edition – ENU',
'Microsoft Visual Studio 6.0 Enterprise Edition','Microsoft SQL Server 2005','Microsoft SQL Server 2000',
'Microsoft Project 2000 SR-1','Microsoft Project 2000','Microsoft Office 2000 SR-1 Professional','Microsoft Office 2000 SR-1 Standard','Microsoft Exchange','Microsoft Visio 2010 Professional','Microsoft Visio 2010 Standard')

Group by
    DisplayName0,
    Version0
Order by
    DisplayName0,
    Version0

Office all version report

Office all version report :


SELECT Distinct
b.Netbios_Name0,
b.User_Name0,
b.User_Domain0,
v_GS_OPERATING_SYSTEM.Caption0 ,
case when v_gs_processor.addresswidth0 = 64 then '64bit OS'
when v_gs_processor.addresswidth0=32 then '32bit OS'
end as [Operating System Type],
CASE
WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003'
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007'
WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010'
WHEN a.FileVersion LIKE '15.%' THEN 'Office 2013'
WHEN a.FileVersion LIKE '16.%' THEN 'Office 2016'
ELSE 'Lower  Version' END AS 'Office Version',
a.FileName,
a.FileVersion,
a.FilePath,
v_GS_OPERATING_SYSTEM.Caption0
FROM
v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID=b.ResourceID
join v_gs_processor ON b.ResourceID = v_gs_processor.ResourceID
WHERE FileName = 'outlook.exe'

GROUP BY
b.Netbios_Name0,
b.User_Name0,
b.User_Domain0,
a.FileName,
a.FileVersion,
a.FilePath,
v_GS_OPERATING_SYSTEM.Caption0,
v_gs_processor.addresswidth0
ORDER BY
b.Netbios_Name0

Office installed machine report for 64 & 32 - bit machines

Office installed machine report for 64 - bit machines :


SELECT DISTINCT v_GS_SYSTEM.Name0 as 'Machine Name',v_R_System.User_Name0,
v_GS_SYSTEM.Domain0, v_R_system.User_Domain0,v_R_System.operatingSystem0,
v_R_system.User_Domain0,v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
FROM v_GS_SYSTEM
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS_64 ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
 Inner join v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIKE '%Microsoft Office Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) Like 'Microsoft Office Professional%'or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIke 'Microsoft Office Enterprise %'





Office installed machine report for 32 - bit machines :


SELECT DISTINCT v_GS_SYSTEM.Name0 AS ' Machine Name', v_R_System.User_Name0,v_GS_SYSTEM.Domain0, v_R_system.User_Domain0,
v_R_System.operatingSystem0, v_R_system.User_Domain0,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_GS_SYSTEM
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
Inner join v_R_System ON v_GS_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft % Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) Like 'Microsoft % Professional%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIke 'Microsoft % Enterprise %'

Order by v_GS_SYSTEM.Name0

Query to find the machine names for the Email ID's

This summary is not available. Please click here to view the post.

Query is to find the Machine and Email ID for Full user name

Query is to find the Machine and Email ID for Full user name :


Select distinct Netbios_name0,USer_domain0,v.User_Name0,Mail0,v_R_User.Full_User_Name0,Last_Logon_Timestamp0
 from v_r_System v
 inner join v_R_User on v_r_user.User_Name0 = v.User_Name0
 where v_R_User.Full_User_Name0  in ('Donn xxxxxxxx','xxxxxxxxxx','xxxxxxxxx','xxxxxxxxxx',
'xxxxxxxxx Davis','Darla xxxxxxxx')

Query for Global Hardware and Software Scan report :-

Query for Global Hardware  and Software Scan report :-

SELECT Distinct

v_R_System.Name0 AS [netbios_name0],
v_r_system.User_Name0,

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Machine Boot Up Time],

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan Date],

V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE]  ,

v_R_System.Last_Logon_Timestamp0

FROM v_R_System

INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID






H\W and S|W scan report for the particular file  :


SELECT Distinct

v_R_System.Name0 AS [netbios_name0],
v_r_system.User_Name0,

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Machine Boot Up Time],

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan Date],

V_GS_LASTSOFTWARESCAN.LASTSCANDATE AS [LAST SOFTWARE SCAN DATE]  ,

v_GS_SoftwareFile.FileName AS [Display Name] ,
v_R_System.Last_Logon_Timestamp0

FROM v_R_System

INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_LastSoftwareScan ON v_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID
INNER JOIN v_GS_SoftwareFile ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)


Where v_GS_SoftwareFile.FileName = 'WINPROJ.EXE'








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'

WQL Query for VISIO and Projects installed machine report

select SMS_R_System.Name, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonUserDomain, SMS_G_System_INSTALLED_SOFTWARE.InstallDate, SMS_G_System_INSTALLED_SOFTWARE.TimeStamp, SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName from  SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Standard%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Professional%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Visio Premium%" or SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Office Visio%" order by SMS_R_System.Name




---------------------------------------------------

' Projects '

------------------------------------------------------------

select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonUserDomain, SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate, SMS_G_System_ADD_REMOVE_PROGRAMS.TimeStamp from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project Standard%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Project Professional%" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Office Project%" order by SMS_R_System.Name

Tuesday, July 18, 2017

SQL QUERY for Display name is for Adobe Shockwave Player

Example for Adobe Shockwave Player:

select v_R_System.ResourceID,
v_R_System.ResourceType,
v_R_System.Name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0,
v_R_SYSTEM.Client0
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceI
d where v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from  v_R_System inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Adobe%Shockwave%Player%' )

Qury for Hardware scan with machines information

SELECT distinct sys.Netbios_Name0 AS AssetID, sys.User_Name0 AS [User Name], usr.Full_User_Name0 AS [Full User Name], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Make, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number], sys.AD_Site_Name0 AS [AD Site Location],
vWorkstationStatus.LastHardwareScan AS [Last Hardware Scan], MAX(v_AgentDiscoveries.AgentTime) AS Discovery_time

FROM v_AgentDiscoveries

INNER JOIN v_R_User AS usr ON v_AgentDiscoveries.ResourceId = usr.ResourceID
 RIGHT OUTER JOIN v_GS_PC_BIOS
 INNER JOIN v_R_System_Valid AS sys ON v_GS_PC_BIOS.ResourceID = sys.ResourceID
 INNER JOIN v_GS_COMPUTER_SYSTEM ON sys.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
 INNER JOIN vWorkstationStatus ON v_GS_COMPUTER_SYSTEM.ResourceID = vWorkstationStatus.ResourceID ON usr.User_Name0 = sys.User_Name0
GROUP BY sys.Netbios_Name0, sys.User_Name0, sys.AD_Site_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0, vWorkstationStatus.LastHardwareScan, usr.Full_User_Name0

Query for Domain wise with Full usernames

Full usernames:-
---------------------------------------
select

sys.netbios_name0, usr.Full_User_Name0,
usr.Unique_User_name0,
usr.User_Name0
from v_r_user Usr
join v_r_system sys on sys.user_name0=usr.User_name0
where sys.resource_domain_or_workgr0 = 'NA'





Application NOT Installed machine names query

Application NOT Installed machine names
-------------------------------------------------------------------------

select v_R_System.ResourceID,
v_R_System.Netbios_Name0 as 'ComputerName',
v_r_system.Full_Domain_Name0 as 'Domain Name',
v_R_System.Name0,
User_name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0,
v_R_SYSTEM.Client0
from v_R_System
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
where v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from  v_R_System inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Microsoft Outlook MUI (English) 2016%' AND  v_GS_ADD_REMOVE_PROGRAMS.prodID0 Like '%XXXXXXXX%' AND
V_GS_ADD_REMOVE_PROGRAMS.version0 like '%XXXXXXXXX%')


-------------------------------------------------------------------------------------------------------



select v_R_System.ResourceID,
v_R_System.Netbios_Name0 as 'ComputerName',
v_r_system.Full_Domain_Name0 as 'Domain Name',
v_R_System.Name0,
User_name0,
v_R_System.SMS_Unique_Identifier0,
v_R_System.Resource_Domain_OR_Workgr0 as 'Region',
v_R_SYSTEM.Client0
 from v_R_System
where v_R_System.Netbios_Name0 not in

(select distinct v_GS_COMPUTER_SYSTEM.Name0
FROM v_R_System

INNER JOIN v_GS_SoftwareFile ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

inner Join v_GS_ADD_REMOVE_PROGRAMS on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID =  v_R_System.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId


Where v_GS_SoftwareFile.FileName = 'outlook.exe'and ProdID0 = '{XXXXXXXXXX}' and Version0 = 'XXXXXXXXXXXX')








Application installation report for a OUTLOOK.EXE

SELECT DISTINCT
v_R_System.Netbios_Name0 as 'ComputerName'
,v_r_system.Full_Domain_Name0 as 'Domain Name'
, v_gs_softwareFile.FilePath
,v_gs_softwarefile.filename
,v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
,v_GS_ADD_REMOVE_PROGRAMS.DisplayName0

FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)

inner Join v_GS_ADD_REMOVE_PROGRAMS
on (v_GS_ADD_REMOVE_PROGRAMS.ResourceID =  v_R_System.ResourceID)


WHERE v_GS_SoftwareFile.FileName = 'outlook.exe' and V_GS_ADD_Remove_programs.DisplayName0 like 'Microsoft Office Professional Plus 2016'

SQL Query To Return Add OR Remove Programs Installed On A Specified Machine


SQL Query To Return Add OR Remove Programs Installed On A Specified Machine

SQL Query:

Select

P.DisplayName0,

P.ProdId0,

P.Publisher0,

P.Version0

From v_Add_Remove_Programs P

Join v_R_System S on P.ResourceId = S.ResourceId

Where Name0 = ‘MachineName’



-------------------------------------------------------------------------------------------


SQL Query To Retrieve Clients Last Boot up Date

SQL Query To Retrieve Clients Last Boot up Date:
-------------------------------------------------

Select

SD.Name0 ‘Machine Name’,

SD.User_Name0 ‘Last Logged on User Name’,

Convert(VarChar(10), OS.LastBootUpTime0, 101)  ‘Last Boot Date’

From v_R_System SD

Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID

Order By ‘Machine Name’





Application installation report with product code

select Name0, netbios_name0,user_name0,ProdID0 ,Displayname0, installdate0,publisher0,version0 from v_r_system
inner join v_Add_Remove_Programs
on v_Add_Remove_Programs.ResourceID=v_R_System.ResourceID

where ProdID0 = 'XXXXXXXXXXXX'

-------------------------------------------------------------------------

Select *from v_Add_Remove_Programs
where ProdID0 = 'XXXXXXXXX' and Version0 = 'XXXXXXXX'
--------------------------------------------------------------------------------

SQL Query For Windows Media Player Versions:
--------------------------------------------------

SQL Query:

Select Distinct

v_R_System.Name0,

v_GS_SoftwareFile.FileName,

v_GS_SoftwareFile.FileDescription,

v_GS_SoftwareFile.FileVersion

From v_R_System

Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID

Where FileName = ‘wmplayer.exe’

SQL query for Application app enforcement deployment status in SCCM 2012

Application app enforcement deployment status with installation errors

select  distinct vrs.user_name0,fn.machinename,fn.applicationname,fn.deploymenttypename,fn.Enforcementtime,errorcode,
case when fn.appstate='1000' then 'Success'
when fn.appstate='1001' then 'Already Compliant'
when fn.appstate='1002' then 'Simulate Success'
when fn.appstate='2000' then 'In progress'
when fn.appstate='2001' then 'Waiting for content'
when fn.appstate='2002' then 'Installing'
when fn.appstate='2003' then 'Restart to continue'
when fn.appstate='2004' then 'Waiting for maintenance window'
when fn.appstate='2005' then 'Waiting for schedule'
when fn.appstate='2006' then 'Downloading dependent content'
when fn.appstate='2007' then 'Installing dependent content'
when fn.appstate='2008' then 'Restart to complete'
when fn.appstate='2009' then 'Content downloaded'
when fn.appstate='2010' then 'Waiting for update'
when fn.appstate='2011' then 'Waiting for user session reconnect'
when fn.appstate='2012' then 'Waiting for user logoff'
when fn.appstate='2013' then 'Waiting for user logon'
when fn.appstate='2014' then 'Waiting To Install'
when fn.appstate='2015' then 'Waiting Retry'
when fn.appstate='2016' then 'Waiting For Presentation Mode'
when fn.appstate='2017' then 'Waiting For Orchestration'
when fn.appstate='2018' then 'Waiting For Network'
when fn.appstate='2019' then 'Pending App-V Virtual Environment Update'
when fn.appstate='2020' then 'Updating App-V Virtual Environment'
when fn.appstate='3000' then 'Requirements not met'
when fn.appstate='3001' then 'Host Platform Not Applicable'
when fn.appstate='4000' then 'Unknown'
when fn.appstate='5000' then 'Deployment failed'
when fn.appstate='5001' then 'Evaluation failed'
when fn.appstate='5002' then 'Deployment failed'
when fn.appstate='5003' then 'Failed to locate content'
when fn.appstate='5004' then 'Dependency installation failed'
when fn.appstate='5005' then 'Failed to download dependent content'
when fn.appstate='5006' then 'Conflicts with another application deployment'
when fn.appstate='5007' then 'Waiting Retry'
when fn.appstate='5008' then 'Failed to uninstall superseded deployment type'
when fn.appstate='5009' then 'Failed to download superseded deployment type'
when fn.appstate='5010' then 'Failed to updating App-V Virtual Environment'
end as 'Laststatusmessage'

 from fn_AppDTClientSummarizedState('') fn join v_r_system vrs on vrs.name0=fn.machinename

  where fn.applicationname  like '%OFFICE02013ENGC2A4%' and fn.appstate not like '3000' and fn.deploymenttypename in ('OFFICE02013ENGC2A4-Win7','OFFICE02013ENGC2A4-Win8')

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...