SQL Queries

1)  SQL query to find the SCCM environment with Client Version and machine counts .

SQL Query :

select sys.Client_Version0, “SCCM Family”=
case sys.Client_Version0 , “SCCM Client Version”
when ‘5.00.7958.1000’ then ‘SCCM 2012 R2 R2 (5.00.7958.1000)’
when ‘5.00.8325.1000’ then ‘SCCM 1511 (5.00.8325.1000)’
when ‘5.00.8412.1000’ then ‘SCCM 1606 (5.00.8412.1000)’
else ‘Others(non-Clients)’
End,count(*) [Total]
from v_R_System sys
where sys.Name0 not like ‘unknown’ and
sys.Client_Version0 not like ” and sys.Client_Version0 not like ‘0.0%’
group by sys.Client_Version0
order by 3 desc

SCCM ClientVersion SCCM Family Total
5.00.8412.1007 SCCM 1606(5.00.8412.1007) 3
5.00.8325.1000 SCCM 1511 (5.00.8325.1000) 4
5.00.7958.1000 SCCM 2012 R2 R2 (5.00.7958.1000) 2

2) If we want to fetch information for hostname and OS details . We can write a SQL query which will pull details from SCCM Database as Hostname , Operating System Version , Service Pack & IP Address.

SQL Query :

Select distinct v_GS_SYSTEM.Name0 as HostName,
v_GS_OPERATING_SYSTEM.Caption0 as OS ,
v_GS_OPERATING_SYSTEM.CSDVersion0 as ServicePack,
v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 as IPAdderess
FROM v_GS_SYSTEM INNER JOIN
v_GS_OPERATING_SYSTEM ON v_GS_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
v_GS_NETWORK_ADAPTER_CONFIGUR ON v_GS_OPERATING_SYSTEM.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID
JOIN v_FullCollectionMembership fcm on fcm.resourceid=v_GS_SYSTEM.resourceid
WHERE fcm.Collectionid= @collection and (v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 is not null)

HostName OS ServicePack IPAdderess
Test01 Microsoft Windows Server 2008 R2 Standard Service Pack 1 10.x.x.233, fe80::951b:2405:7f5b:722f
Test02 Microsoft Windows Server 2008 R2 Standard Service Pack 1 10.x.x.233, fe80::951b:2405:7f5b:722f
Test03 Microsoft Windows Server 2008 R2 Standard Service Pack 1 10.x.x.233, fe80::951b:2405:7f5b:722f

3) How to create a report to find number of machines count installed with Specific Windows Operating System.

SQL Query :

Select distinct
SD.Operating_System_Name_and0 OSVersion, Caption0 OperatingSystem,
Count (SD.Operating_System_Name_and0) ‘Total’
From v_GS_OPERATING_SYSTEM , v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Where
v_GS_OPERATING_SYSTEM.Resourceid = SD.Resourceid
Group By SD.Operating_System_Name_and0,Caption0
Order By SD.Operating_System_Name_and0,Caption0

OSVersion OperatingSystem Total
Microsoft Windows NT Advanced Server 6.0 Microsoft® Windows Server® 2008 Enterprise 1
Microsoft Windows NT Advanced Server 6.1 Microsoft Windows Server 2008 R2 Enterprise 2
Microsoft Windows NT Workstation 10.0 Microsoft Windows 10 Pro 1
Microsoft Windows NT Workstation 5.1 Microsoft Windows XP 1
Microsoft Windows NT Workstation 6.1 Microsoft Windows 7 3

4) Can we have a report where we can find the free space of drive and total disk space along with RAM Size , so that if we have any maintenance and where free disk space is a concern we can act immediately and inform SCCM Admin &  Sys Admin?

Yes , we can do it 🙂

Can we more customize have  SCCM report based on Collection ID.

Yes , we can 🙂  

SQL Query :

SELECT DISTINCT s.Netbios_Name0 AS ComputerName,
convert(decimal(20,0),round((1.0*m.TotalPhysicalMemory0/1024)/1024,0)) as ‘RAM(GB)’
, ld.deviceid0 AS DriveLetter,
convert(decimal(20,0),round(1.0*ld.Size0/1024,0)) as ‘DriveSize(GB)’,
convert(decimal(20,0),round(1.0*ld.FreeSpace0/1024,0)) as ‘FreeSpace(GB)’
FROM v_R_System_Valid s INNER JOIN v_GS_PROCESSOR pr ON s.ResourceID = pr.ResourceID INNER JOIN v_FullCollectionMembership_Valid SYS ON s.ResourceID = SYS.ResourceID INNER JOIN v_GS_LOGICAL_DISK LDISK ON s.ResourceID = LDISK.ResourceID

INNER JOIN v_FullCollectionMembership_Valid SYS ON s.ResourceID = SYS.ResourceID INNER JOIN v_GS_LOGICAL_DISK LDISK ON s.ResourceID = LDISK.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM gs ON s.ResourceID = gs.ResourceID
INNER JOIN v_GS_NETWORK_ADAPTER ON s.ResourceID = v_GS_NETWORK_ADAPTER.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID
INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION ip ON s.ResourceID = ip.ResourceID
INNER JOIN v_GS_LOGICAL_DISK AS ld ON s.ResourceID = ld.ResourceID
WHERE  SYS.Collectionid= @collection AND ld.DriveType0=3

ComputerName RAM(GB) DriveLetter DriveSize(GB) FreeSpace(GB)
Win7_Test01 8 C: 80 40
Win2k8_Test01 2 C: 65 20
Win10_Test01 4 C: 90 50

5) How to find the list of Server installed with Microsoft Office Suits or Microsoft Office Component like (MS Web 2003 Component etc.). The report will be helpful for Admins to uninstall the Office component so that we wont exhaust or over use licenses , For small organization it will be helpful to track and remove the MS Office from the servers.

SQL Query :

select distinct
r.Name0 ‘Computer Name’, User_Name0 ‘User Name’,
p.DisplayName0 ‘Microsoft Office Version’,
v_GS_OPERATING_SYSTEM.Caption0 as OS
from V_R_System r
join v_GS_OPERATING_SYSTEM ON r.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
inner join v_ADD_REMOVE_PROGRAMS p
on r.ResourceID = p.ResourceID
where
(p.DisplayName0 like ‘%Microsoft Office Professional Plus 2013%’ or
p.DisplayName0 like ‘%Microsoft Office Professional Plus 2007%’ or
p.DisplayName0 like ‘%Microsoft Office Professional Plus 2010%’
)
and Operating_System_Name_and0 like ‘%Server%’
order by r.Name0

Computer Name User Name Microsoft Office Version OS
Win2k8_Test01 admin Microsoft Office Professional Plus 2010 Microsoft Windows Server 2008  Standard
Win2012_Test01 admin Microsoft Office Professional Plus 2007 Microsoft Windows Server 2012 Standard
Win2k82_Test01 admin Microsoft Office Professional Plus 2013 Microsoft Windows Server 2008 R2 Standard

6) Create a report to find the list of hostname installed with Java Version . Can we filter down the report specific to Servers and Workstation respectively ? Yes for sure we can able to achieve it 🙂

SQL Query :

select distinct
r.Name0 ‘Computer Name’, User_Name0 ‘User Name’,
p.DisplayName0 ‘Java Version’,
v_GS_OPERATING_SYSTEM.Caption0 as OS
from V_R_System r
join v_GS_OPERATING_SYSTEM ON r.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
inner join v_ADD_REMOVE_PROGRAMS p
on r.ResourceID = p.ResourceID
where
(p.DisplayName0 like ‘Java%’
and p.DisplayName0 not like ‘Java%Development%’
and p.DisplayName0 not like ‘JavaScript%’
and p.DisplayName0 not like ‘%Env%’
and p.DisplayName0 not like ‘%Standard%’
)
and Operating_System_Name_and0 like ‘%Server%’

( When we want to get report for OS specific to Workstation comment this line and remove with Operating_System_Name_and0 like ‘%Workstatation%’ )

Computer Name User Name Java Version OS
Win2k8_Test01 test.user Java 7 Update 11 (64-bit) Microsoft Windows Server 2008  Standard
Win2012_Test01 admin Java Auto Updater Microsoft Windows Server 2012 Standard
Win2k82_Test01 test01 Java(TM) 6 Update 29 Microsoft Windows Server 2008 R2 Standard

7) I believe most of Sys Admins came across a situation where Microsoft Audit is in place with in a week and management will ask you to pull hardware/software inventory report on urgent basis . If in case your IT ORG is using multiple asset management and inventory tool , then how you will decide which one to use and get all details on ASAP.

Let’s discuss in detail , which tool shall we use to fetch inventory information ?

a) If we can see below report fetched from Antivirus Tool, SAM (Software Asset Management) or LanDesk , we can find few column information are same rest column values are different.

b) To address this issue , what we exactly need report is the intersection of all the columns in a single report. How we achieve it ? There come the power of SCCM and SQL query where we can fetch all the information in a single report. Awesome 🙂

SAMTool Comparison.png

SQL Query :

select distinct
FirstSet.Computername,
SecondSet.LastUserLoggedOn,
SecondSet.OS,SecondSet.ServicePack,
SecondSet.DomainName,
FirstSet.Manufacturer,
FirstSet.Name,
FirstSet.[Number of CPUs],
FirstSet.[Number of Cores per CPU],
FirstSet.[Logical CPU Count],
FirstSet.HyperThreadEnabled,
SecondSet.LastHWScan
from
(
SELECT
DISTINCT(CPU.SystemName0) AS [Computername],
CPU.Manufacturer0 AS Manufacturer,
CPU.Name0 AS Name,
COUNT(CPU.ResourceID) AS [Number of CPUs],
CPU.NumberOfCores0 AS [Number of Cores per CPU],
CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count],
CPU.IsHyperthreadEnabled0 AS HyperThreadEnabled
FROM [dbo].[v_GS_PROCESSOR] CPU
GROUP BY
CPU.SystemName0,
CPU.Manufacturer0,
CPU.Name0,
CPU.NumberOfCores0,
CPU.NumberOfLogicalProcessors0,
CPU.IsHyperthreadEnabled0
) as FirstSet
inner join
(
SELECT distinct
v_GS_SYSTEM.Name0 as Computername,
v_GS_OPERATING_SYSTEM.Caption0 as OS ,
v_GS_OPERATING_SYSTEM.CSDVersion0 as ServicePack,
v_GS_NETWORK_ADAPTER_CONFIGURATION.DNSDomain0 as DomainName,
v_GS_WORKSTATION_STATUS.LastHWScan as LastHWScan,
CS.UserName0 as LastUserLoggedOn
FROM v_GS_SYSTEM INNER JOIN
v_GS_OPERATING_SYSTEM ON v_GS_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
v_GS_NETWORK_ADAPTER_CONFIGUR ON v_GS_OPERATING_SYSTEM.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID
Inner Join v_GS_NETWORK_ADAPTER_CONFIGURATION ON v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID

INNER JOIN v_GS_WORKSTATION_STATUS ON v_GS_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
join dbo.v_GS_COMPUTER_SYSTEM CS on v_GS_SYSTEM.ResourceID = CS.ResourceID
join dbo.v_FullCollectionMembership FCM on v_GS_SYSTEM.ResourceID = FCM.ResourceID
Where
FCM.CollectionID = ‘SMS00001’
) as SecondSet
on FirstSet.Computername = SecondSet.Computername
order by FirstSet.Computername

sqlresult.png

8) Report to find the list of hostname NOT INSTALLED with SCCM Agent in your SCCM environment.

SQL Query :

select distinct
SYS.Name0 as ‘HostName’,
ISNULL(SYS.User_Name0, ‘None’) as ‘Login ID’,
ISNULL(SYS.User_Domain0, ‘None’) as ‘Domain’,
ISNULL(USR.Full_User_Name0, ‘None’) as ‘Full Name’,
case
when SYS.Client0 = 1 then ‘Yes’
when SYS.Client0 = 0 then ‘No (or Inactive)’
when SYS.Client0 is null then ‘None’
else convert(varchar(2), SYS.Client0)
end as ‘Client Installed’,
SYS.Client_Version0 as ‘Client Version’
from
v_R_System SYS
join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
join v_Collection CN on FCM.CollectionID = CN.CollectionID
left outer join v_R_User USR on SYS.User_Name0 = USR.User_Name0
where
CN.Name = ‘All Systems’ or (
SYS.Client0 = 0 and
SYS.Client0 is null
)
order by SYS.Client_Version0

HostName Login ID Domain Full Name Client Installed Client Version
MAKTEST_NEW None None None None NULL
PATCHTESTVM None None None None NULL
PATCHTESTVM01 None None None None NULL
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s