June 2017 – Lab Core | The Lab of MrNetTek

Windows – Run RSAT/Admin Tools from Workstation Computer

Posted onJune 29, 2017October 10, 2018

Use this command to launch the MSCs as a different user from a non-domain computer. Note, you may have to use the IP address for the server same if your machine doesn’t resolve the name.

runas /netonly /user:abc.domain.com\Username “mmc dsa.msc /server=LogonServerNameOrIP”

I made a simple little .cmd file…with the above command in it, created a shortcut for it, and then set that shortcut to always run as administrator. This makes launching ADUC much easier.

Disable Windows Defender

Posted onJune 28, 2017June 28, 2017

Disable in Group Policy

Type gpedit.msc and click OK to open the Local Group Policy Editor.
Browse the following path:
Computer Configuration > Administrative Templates > Windows Components > Windows Defender
On the right, double-click Turn off Windows Defender.
Select Enabled to disable Windows Defender.
Click Apply.
Click OK.

Disable in Registry

Open Registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows Defender
If you don’t see DWORD DisableAntiSpyware, right-click on an empty space, select New, and click on DWORD (32-bit) Value.
Name the key DisableAntiSpyware.
Double-click the newly created key, and set the value from 0 to 1.

Critical Skype Bug Lets Hackers Remotely Execute Malicious Code

Posted onJune 28, 2017June 28, 2017

http://thehackernews.com/2017/06/skype-crash-bug.html

A critical vulnerability has been discovered in Microsoft-owned most popular free web messaging and voice calling service Skype that could allow hackers to remotely execute malicious code and crash systems.

‘Petya’ Ransomware Outbreak Goes Global

Posted onJune 28, 2017June 28, 2017

Krebs On Security
https://krebsonsecurity.com/2017/06/petya-ransomware-outbreak-goes-global/
A new strain of ransomware dubbed “Petya” is worming its way around the world with alarming speed. The malware is spreading using a vulnerability in Microsoft Windows that the software giant patched in March 2017 — the same bug that was exploited by the recent and prolific WannaCry ransomware strain…

The Hacker News
http://thehackernews.com/2017/06/petya-ransomware-attack.html
Watch out, readers! It is ransomware, another WannaCry, another wide-spread attack…

US-Cert
https://www.us-cert.gov/ncas/current-activity/2017/06/27/Multiple-Petya-Ransomware
US-CERT has received multiple reports of Petya ransomware infections in many countries around the world. Ransomware is a type of malicious software that infects a computer and restricts users’ access to the infected machine until a ransom is paid to unlock it. Individuals and organizations are discouraged from paying the ransom, as this does not guarantee that access will be restored. Using unpatched and unsupported software may increase the risk of proliferation of cybersecurity threats, such as ransomware…

Check Point Blog
http://blog.checkpoint.com/2017/06/27/global-ransomware-attack-spreading-fast/
A massive attack erupted today (June 27) worldwide, with a high concentration of hits in Ukraine – including the Ukrainian central bank, government offices and private companies…

SandBlast Agent Forensic Analysis
http://freports.us.checkpoint.com/petyavar/

SCCM – SQL Queries

Posted onJune 25, 2017December 6, 2022

Also see SQL Certification

You can test your SQL queries in the SQL Server Management Studio or WQL queries under the CM Queries node.

Create SQL Query in SQL Server Management Studio

Create WQL Query under SCCM Monitoring Workspace

WQL Queries

* tested in the SCCM Console, under the Queries node

* many other queries in PDF

Return Specific Windows Build Version

SELECT DISTINCT
SMS_R_System.NetbiosName, SMS_G_System_OPERATING_SYSTEM.BuildNumber
FROM
SMS_R_System
INNER JOIN SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_OPERATING_SYSTEM.BuildNumber = "16299" and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

Join System, User, and Computer_System…return data

SELECT v_R_System.Name0,v_R_System.Operating_System_Name_and0, v_R_User.User_Name0,v_R_User.displayName0,v_R_User.Mail0, v_GS_COMPUTER_SYSTEM.Model0
FROM v_R_System
INNER JOIN v_R_User ON v_R_User.User_Name0 = v_R_System.User_Name0
INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId

Return Machines with PST Data

select distinct
SMS_R_System.Name,
SMS_G_System_SoftwareFile.FileName,
SMS_G_System_SoftwareFile.FilePath,
SMS_G_System_SoftwareFile.FileSize,
SMS_G_System_SoftwareFile.ModifiedDate
from
SMS_R_System
inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_SoftwareFile.FileName like "%.pst%"

Join SMS_R_System and SMS_R_User: Post

SELECT * FROM SMS_R_System INNER JOIN SMS_R_User ON SMS_R_User.Username = SMS_R_System.LastLogonUserName

SQL Queries

* tested in SQL Server Management Studio

Return Maintenance Windows General Info

SELECT c.Name, c.Comment,SW.IsEnabled, SW.CollectionID, c.MemberCount, SW.Description, SW.StartTime, SW.Duration
FROM v_ServiceWindow SW
JOIN v_Collection C ON C.CollectionID = SW.CollectionID
ORDER BY c.Name

Return ResourceID, Email Address, Computer Name

SELECT v_R_User.ResourceID, User_Principal_Name0, v_GS_COMPUTER_SYSTEM.Name0
FROM v_GS_COMPUTER_SYSTEM
LEFT JOIN v_R_User ON v_GS_COMPUTER_SYSTEM.UserName0 = v_R_User.Unique_User_Name0

Return Collection ID from Software Name

select distinct
v_Collection.Name as 'Software Name',
v_Collection.CollectionID
from v_Collection
join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
where v_Collection.Name = 'Visio Pro 2019'

Return Collection Name from Collection ID

select distinct
v_Collection.Name as 'Software Name',
v_Collection.CollectionID
from v_Collection
join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
where v_Collection.CollectionID = 'ABC00188'

Return SCCM Folders with IDs

select vsms_folders.Name as[FolderName], vFolderMembers.ContainerNodeID as [FolderID]
from vcollections
inner join vFolderMembers
on vCollections.siteid = vFolderMembers.InstanceKey
inner join vsms_folders
on vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID

Return All Collections Inside a Specific Folder

select vcollections.siteid as [Software Title], vcollections.collectionname as [CollectionName], vsms_folders.Name as[FolderName], vFolderMembers.ContainerNodeID as [FolderID]
from vcollections
inner join vFolderMembers
on vCollections.siteid = vFolderMembers.InstanceKey
inner join vsms_folders
on vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID
where vFolderMembers.ContainerNodeID = '12345678'
ORDER BY
'Software Title'

Return Computer and User

SELECT sys.Netbios_Name0, TopConsoleUser0
FROM v_R_System sys
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP um
ON um.ResourceID = sys.ResourceID
WHERE TopConsoleUser0 IS NOT NULL
ORDER BY sys.Netbios_Name0

Return All Available Packages

SELECT
Program.PackageID,
Package.Name 'Package',
Program.ProgramName 'Type',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM
[v_Program] as Program
LEFT JOIN
v_Package as Package on Package.PackageID = Program.PackageID
WHERE Program.ProgramName != '*'
--WHERE Program.ProgramName = 'Install'
ORDER BY
Package.Name

Create Custom Report with operating systems and serials

SELECT DISTINCT
a.ResourceID,
a.Name0 AS[Name],
a.User_Name0 AS[Username],
b.System_OU_Name0,
a.Last_Logon_Timestamp0 AS[Last Logon],
c.Manufacturer00 AS [Manufacturer],
c.SerialNumber00 AS[Serial],
c.SMBIOSAssetTag00 AS[Asset],
d.Caption00 AS[OS],
d.InstallDate00 AS[Install Date],
e.SMBIOSBIOSVersion00 AS[BIOS],
f.Model00 AS[Model]

FROM
v_R_System a

LEFT OUTER JOIN
System_System_OU_Name_ARR b ON b.ItemKey = b.ItemKey
INNER JOIN
System_Enclosure_DATA c ON c.MachineID = a.ResourceID
INNER JOIN
Operating_System_DATA d ON d.MachineID = a.ResourceID
INNER JOIN
PC_BIOS_DATA e ON e.MachineID = a.ResourceID
INNER JOIN
Computer_System_DATA f ON f.MachineID = a.ResourceID

Computers that have 30+ days old hardware information

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
b.LastHWScan
FROM
v_R_System a
INNER JOIN
v_GS_WORKSTATION_STATUS b
ON
a.ResourceID = b.ResourceID
WHERE
b.LastHWScan &lt;= Dateadd(day, -30, getdate())

Computers that have 30 days or less hardware information

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
b.LastHWScan
FROM
v_R_System a
INNER JOIN
v_GS_WORKSTATION_STATUS b
ON
a.ResourceID = b.ResourceID
WHERE
b.LastHWScan &gt;= Dateadd(day, -30, getdate())

Collection of clients not approved

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
a.ResourceType,
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.IsApproved
FROM
v_R_System a
INNER JOIN
v_CM_RES_COLL_SMS00001 b
ON
a.ResourceID = b.ResourceID
WHERE
b.IsApproved = '2'

Collection of clients that have been approved

SELECT
a.ResourceID,
a.Netbios_name0 AS[Name],
a.ResourceType,
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.IsApproved
FROM
v_R_System a
INNER JOIN
v_CM_RES_COLL_SMS00001 b
ON
a.ResourceID = b.ResourceID
WHERE
b.IsApproved = '1'

Collection of clients requiring a reboot

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System AS SMS_R_SYSTEM
INNER JOIN
vSMS_Update_ComplianceStatus AS c
ON
c.MachineID=SMS_R_SYSTEM.ResourceID
WHERE
c.LastEnforcementMessageID = 9

Collection of ConfigMgr clients waiting for another installation to finish

SELECT
SMS_R_SYSTEM.ResourceID AS[ResourceID],
SMS_R_SYSTEM.ResourceType AS[Type],
SMS_R_SYSTEM.Name0 AS[Name],
SMS_R_SYSTEM.SMS_Unique_Identifier0 AS[UniqueID],
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0 AS[Domain],
SMS_R_SYSTEM.Client0 AS[Name]
FROM
v_R_System AS SMS_R_System
INNER JOIN
vSMS_Update_ComplianceStatus AS c
ON
c.MachineID=SMS_R_System.ResourceID
WHERE
c.LastEnforcementMessageID = 3

Return list of computers with C: drive space great than 100 MB

SELECT
a.ResourceID,
a.ResourceType AS[ResourceID],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.Size0)&gt; 100)

All SCCM client computers with less than 1 GB free disk space on C:

SELECT
a.ResourceID,
a.ResourceType AS[ResourceType],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.FreeSpace0) &gt; 1000)

All SCCM client computers with less than 10 GB free disk space on C:

SELECT
a.ResourceID,
a.ResourceType AS[ResourceType],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.FreeSpace0)&lt; 10000)

All SCCM client computers with greater than 50 GB free disk space on C:

SELECT
a.ResourceID,
a.ResourceType AS[ResourceID],
a.Name0 AS[Name],
a.SMS_Unique_Identifier0 AS[UniqueID],
a.Resource_Domain_OR_Workgr0 AS[Domain],
a.Client0,
b.DeviceID0 AS[DriveLetter],
b.FreeSpace0 AS[FreeSpace],
b.Size0 AS[Size]
FROM
v_R_System a
INNER JOIN
v_GS_LOGICAL_DISK b
ON
a.ResourceID = b.ResourceId
WHERE
b.DeviceID0 = 'C:' AND ((b.FreeSpace0)&gt; 50000)

All computer objects with client installed in the specific site “001”

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
b.SMS_Installed_Sites0 AS[SiteCode]
FROM
v_R_System a
LEFT OUTER JOIN
v_RA_System_SMSInstalledSites b
ON
a.ResourceID = b.ResourceID
WHERE
b.SMS_Installed_Sites0 = '001' AND a.Client0 = '1'

All SCCM Clients Matching Version

Build 1702

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.00.8498.1007' --OR a.Client_Version0 like '5.00.8498.1008'

Build 1610

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.00.8458.1005' --OR a.Client_Version0 like '5.00.8458.1005'

Build 1606

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.00.8412.1006' --OR a.Client_Version0 like '5.00.8412.1006'

Build 1511

SELECT
a.ResourceID AS[ResourceID],
a.Name0 AS[Name],
a.Client0,
a.SMS_Unique_Identifier0,
a.Client0,
a.Client_Version0
FROM
v_R_System a
WHERE
a.Client_Version0 like '5.0.8325.1000' --OR a.Client_Version0 like '5.0.8325.1000'

Collection with all computers without a ConfigMgr client

SELECT
a.ResourceID,
a.ResourceType,
a.Name0,
a.SMS_Unique_Identifier0,
a.Resource_Domain_OR_Workgr0,
a.Client0
FROM
V_R_System as a
WHERE
a.Client0 is null

Return machine name, serial, and AD container

SELECT
a.Name0 AS[Name],
b.SerialNumber0 AS[Serial],
c.System_Container_Name0
FROM
v_R_System a
LEFT JOIN
v_GS_PC_BIOS b on b.ResourceID = a.ResourceID
INNER JOIN
System_System_Container_Name_A c on c.ItemKey = a.ResourceID
WHERE b.SerialNumber0 IS NOT NULL

Return computers in a specific domain

SELECT
a.Name0
FROM
v_R_System a
WHERE
a.Resource_Domain_OR_Workgr0 = 'AWESOME'

Collection for all Workstations

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation%'

Collection of all Windows 10 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 10.0'

Collection of all Windows 8.1 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 6.3'

Collection of all Windows 8 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 6.2'

Collection of all Windows 7 clients

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System as SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%workstation% 6.1'

Use this query to create collections for your various computer models

SELECT DISTINCT
a.Name0,
b.Manufacturer0,
b.Model0
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%Hewlett-Packard%' or b.Manufacturer0 like '%VMWare, Inc.%'

Collection of computers ending with odd numbers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like '%1'
or SMS_R_System.Name0 like '%3'
or SMS_R_System.Name0 like '%5'
or SMS_R_System.Name0 like '%7'
or SMS_R_System.Name0 like '%9'

Collection of computers ending with even numbers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like '%0'
or SMS_R_System.Name0 like '%2'
or SMS_R_System.Name0 like '%4'
or SMS_R_System.Name0 like '%6'
or SMS_R_System.Name0 like '%8'

Collection of computers ending with LAB

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like '%LAB'

Collection of computers beginning with LAB

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Name0 like 'LAB%'

Collection of computers without failing hard drive

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0,
V_GS_DISK.Status0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_DISK
ON
V_GS_DISK.ResourceId = SMS_R_System.ResourceId
WHERE
V_GS_DISK.Status0 != 'Pred Fail'
ORDER BY
SMS_R_System.Name0 DESC

Collection of computers with failing hard drive

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0,
V_GS_DISK.Status0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_DISK
ON
V_GS_DISK.ResourceId = SMS_R_System.ResourceId
WHERE
V_GS_DISK.Status0 = 'Pred Fail'
ORDER BY
SMS_R_System.Name0 DESC

Collection for all Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server%'

Collection of all Windows 2016 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 10.0'

Collection of all Windows 2012 R2 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.3'

Collection of all Windows 2012 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.2'

Collection of all Windows 2008 R2 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.1'

Collection of all Windows 2008 Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
WHERE
SMS_R_System.Operating_System_Name_and0 like '%server% 6.0'

Collection of all Domain Controllers

SELECT
*
FROM
v_R_System SMS_R_System
INNER JOIN
v_GS_COMPUTER_SYSTEM
ON
v_GS_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
WHERE
v_GS_COMPUTER_SYSTEM.Roles0 like '%Domain_Controller%'

Query based collection for all Microsoft Exchange Servers

SELECT
*
FROM
v_R_System
INNER JOIN
v_GS_SERVICE
ON
v_GS_SERVICE.ResourceId = v_R_System.ResourceId
WHERE
v_GS_SERVICE.Name0 like '%Microsoft Exchange %'

SQL Server Collections

2016

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2016%'

2014

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2014%'

2012

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2012%'

2008

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.Name0,
b.DisplayName0,
SMS_R_SYSTEM.SMS_Unique_Identifier0,
SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,
SMS_R_SYSTEM.Client0
FROM
v_R_System SMS_R_System
INNER JOIN
V_GS_ADD_REMOVE_PROGRAMS b ON b.ResourceID = SMS_R_System.ResourceID
WHERE
b.DisplayName0 like '%Microsoft SQL Server 2008%'

Query based collection based on IP subnet

SELECT
a.ResourceID,
b.Name0 AS[Name],
a.IP_Subnets0 AS[Subnet]
FROM
v_RA_System_IPSubnets a
INNER JOIN
v_R_System b ON b.ResourceID = a.ResourceID
WHERE
a.IP_Subnets0 LIKE '10.0.0.0'

Query based collection based on IP range

SELECT
a.ResourceID,
b.Name0 AS[Name],
a.IP_Addresses0 AS[IP]
FROM
v_RA_System_IPAddresses a
INNER JOIN
v_R_System b ON b.ResourceID = a.ResourceID
WHERE
a.IP_Addresses0 LIKE '10.1.0.1[1-9]'

All Dell Systems

SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%Dell%'

All Hewlett-Packard Systems

SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%HP%' or b.Manufacturer0 like '%Hewlett-Packard%'

All Lenovo Systems

SELECT
*
FROM
v_R_System a
INNER JOIN
v_GS_COMPUTER_SYSTEM b
ON
b.ResourceID = a.ResourceId
WHERE
b.Manufacturer0 like '%Lenovo%'

All Physical Systems

SELECT
a.ResourceID,
a.ResourceType,
a.Name0,
a.SMS_Unique_Identifier0,
a.Resource_Domain_OR_Workgr0,
a.Client0
FROM
v_R_System a
INNER JOIN
v_GS_SYSTEM_ENCLOSURE b
ON
b.ResourceID = a.ResourceId
WHERE
b.ChassisTypes0 = '23' or b.ChassisTypes0 = '17'

Pull Distribution Points with Source Distribution Points SQL Query

SELECT DISTINCT
dbo.v_DistributionPoints.ServerName AS [DP for Pull],
dbo.v_DistributionPoints.IsPeerDP,
dbo.v_DistributionPoints.IsPullDP,
dbo.vPullDPFullMap.PullDPNALPath AS [DP List],
dbo.v_DistributionPoints.IsPXE,
dbo.v_DistributionPoints.Description
FROM
dbo.vPullDPFullMap
INNER JOIN
dbo.v_DistributionPoints
ON dbo.vPullDPFullMap.SourceDPNALPath = dbo.v_DistributionPoints.NALPath

Report to list of all users laptops

SELECT DISTINCT
dbo.v_R_System.Name0 AS [Computer Name],
dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM
dbo.v_GS_SYSTEM_ENCLOSURE
INNER JOIN
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM
ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '8') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '9') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '10') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '11') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '12') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '14') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '18') OR
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '21')

All application list

SELECT
*
FROM fn_ListLatestApplicationCIs(1033)

SELECT
DateCreated,
DateLastModified,
DisplayName,
Manufacturer,
SoftwareVersion,
CreatedBy,
LastModifiedBy
FROM
fn_ListLatestApplicationCIs(1033)
GROUP BY
DateCreated,
DateLastModified,
DisplayName,
Manufacturer,
SoftwareVersion,
CreatedBy,
LastModifiedBy
ORDER BY
DateCreated DESC

All OS with Versions

SELECT DISTINCT
dbo.v_R_System.Netbios_Name0 as [Machine Name],
dbo.v_R_System.User_Name0 as [User Name],
dbo.v_R_System.AD_Site_Name0 as [AD Site],
dbo.v_R_System.User_Domain0 as [Domain],
dbo.v_GS_OPERATING_SYSTEM.Caption0 as [OS Name],
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 as [SP Name],
dbo.v_R_System.Operating_System_Name_and0 as [OS NT Version],
dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as [Build Number]
FROM
dbo.v_R_System
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM
ON
dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID

Software Updates Installed using SCCM or Manually

SELECT sys.Name0,ui.BulletinID, ui.ArticleID,ui.Title,
CASE
WHEN (ucs.Status=2 and ui.IsDeployed=0 ) then 'Required_General'
WHEN (ucs.Status=2 and ui.IsDeployed=1 ) then 'Required_ITICSDeploy'
WHEN (UCS.Status=3 and ui.IsDeployed=1 ) then 'Installed_SCCM'
WHEN (UCS.Status=3 and ui.IsDeployed=0 ) then 'Installed_Manual'
WHEN UCS.Status=0 then 'Unknown' end as 'Status',
CASE
WHEN ui.severity=10 THEN 'Critical'
WHEN ui.severity=8 THEN 'Important'
WHEN ui.severity=6 THEN 'Moderate'
WHEN ui.severity=2 THEN 'Low' WHEN ui.severity=0 THEN 'AddOn' end as 'Severity'
FROM
v_R_System sys
INNER JOIN
v_UpdateComplianceStatus UCS ON   sys.ResourceID = ucs.ResourceID
INNER JOIN
v_UpdateInfo UI ON   UCS.CI_ID = UI.CI_ID
WHERE
-UI.IsDeployed=1 and sys.Netbios_Name0='computer1'
ORDER BY Status

Notes

PowerShell – Add Schema, Add Table into a SQL Test Database
PowerShell – Create SQL Database
PowerShell – Add Entries into SQL Database

For my reference

Packages in SCCM DB; changing a program name

dbo.DeploymentSummary
dbo.SMSPackages_G
dbo.PkgPrograms_G
dbo.PkgPrograms_L

Office Build Numbers

Posted onJune 25, 2017June 25, 2017

OFFICE SUITE	VERSION / BUILD

Office 2016
Office 2016 RTM	16.0.4229.1024
Office 2016 RTM (Volume license)	16.0.4266.1003

Office 2013
Office 2013 RTM	15.0.4420.1017
Office 2013 SP1 32-bit 64-bit	15.0.4569.1506

Office 2010
Office 2010 RTM	14.0.4760.1000
Office 2010 SP1 32-bit 64-bit	14.0.6023.1000
Office 2010 SP2 32-bit 64-bit	14.0.7015.1000

Office 2007
Office 2007 RTM	12.0.4518.1014
Office 2007 SP1	12.0.6213.1000
Office 2007 SP2	12.0.6425.1000
Office 2007 SP3	12.0.6607.1000

Office 2003
Office 2003 RTM	11.0.5614.0
Office 2003 SP1	11.0.6361.0
Office 2003 SP2	11.0.7969.0
Office 2003 SP3	11.0.8173.0

SCCM – More Advanced BitLocker Password Handling

Posted onJune 23, 2017June 24, 2024

In the previous post, I discussed some of the general framework for storing BitLocker passwords in SCCM, such as expanding the SCCM DB using MOFs, editing MOFs, and importing your very own _Custom MOF class. Well, the main issue was that the ‘passwords’ had to be stored in the registry, not exactly best practice, but it got the job done.

In this post, I’ll discuss an even better approach to managing the BitLocker data…on the front end. Unfortunately, the back end is still expanding the DB and editing MOFs—there’s no escaping that. BUT…let’s talk about the front end. Rather than running a script which creates registry keys with stored BitLocker passwords, why not create a custom class with all the BitLocker details, and store the BitLocker properties in the SMS namespace? Sound fun? Well, let’s get started.

The first question is, how do you view things like classes and their details? I like to use WMI Explorer. WMI Explorer is a utility intended to provide the ability to browse and view namespaces, classes, instances, and properties in a single pane of view. I would normally use the tool to see what’s available…what kind of classes, methods, and properties I can use in scripting or programming. It’s not an everyday occurrence for me to create my own class, and then use that class in something like SCCM. But…it was fun. Let’s see some screenshots.

Here we have the WMI Explorer tool. It can explore cim, wmi, root, and other classes and namespaces.

Here is the tool with my SMS_SCCM_BitLocker class, sitting in the cimv2\sms namespace. It does use the infrastructure of WMI—but being specific—I always include the namespace, which is important in the world of SCCM.

Notice how you can see the populated properties, including BitLocker passwords. YAY! I scripted each of the properties to come from manage-bde (you could also use C# instead of a script to accomplish this). The two routines: One was for the general properties, and one was to return the passwords. You can add plenty of other properties as well.

These are the properties of my class.

If you’re going to be adding the BitLocker details to SCCM, here is the MOF output. I’ve also included it in the script below…so you can copy/paste. Just uncomment the MOF portion before saving the file.

Okay, here is the script, which I will come back to explain the finer points. But, basically, I use manage-bde to return the property details, vbscript to delete and create a new class in the SMS_ namespace, and vbscript to populate the details. Once I have everything completed in SCCM, I’ll create a new post detailing the final updates, where I expand the SCCM site DB, create the MOFs, and add the class to the Hardware Inventory in SCCM.

Script

on error resume next

Const wbemString = 8

Dim objArray(26,12)

strDrive = ""

strDriveName = ""

Set WshShell = CreateObject("Wscript.Shell")

Set objWshShell=CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWBEMLocation = CreateObject("WbemScripting.SWbemLocator")

Set oExec = WshShell.Exec("cscript //Nologo c:\windows\system32\manage-bde.wsf -status")

x = 0

Do While Not oExec.StdOut.AtEndOfStream

 strLine = oExec.StdOut.Readline()

 if ucase(left(strLine,6)) = "VOLUME" then

 if strDrive & <> "" then

 targetmif.writeline(" End Group")

 end if

 x = x + 1

 strDrive = mid(strLine,8,2)

 strLine = oExec.StdOut.Readline()

 strDriveName = strLine

 strLine = ""

 objArray(x,0) = strDrive

 objArray(x,1) = strDriveName

 objArray(x,10) = Now

 end if

 if strDrive & <> "" and trim(strLine) & <> "" then

 objItem = split(strLine,":")

 Select Case replace(trim(objItem(0))," ","_")

 Case "Size"

 objArray(x,2) = trim(objItem(1))

 Case "BitLocker_Version"

 objArray(x,3) = trim(objItem(1))

 Case "Conversion_Status"

 objArray(x,4) = trim(objItem(1))

 Case "Percentage_Encrypted"

 objArray(x,5) = trim(objItem(1))

 Case "Encryption_Method"

 objArray(x,6) = trim(objItem(1))

 Case "Protection_Status"

 objArray(x,7) = trim(objItem(1))

 Case "Lock_Status"

 objArray(x,8) = trim(objItem(1))

 Case "Automatic_Unlock" 

 objArray(x,9) = trim(objItem(1)) 

 end select

 end if

loop

'REMOVE OLD CLASSES

Set objCIMV2 = objWBEMLocation.ConnectServer(, "root\cimv2")
set objDetail = objCIMV2.Get("SMS_SCCM_BitLocker")
objDetail.Delete_

Set objCIMV2 = objWBEMLocation.ConnectServer(, "root\cimv2\SMS")
set objDetail = objCIMV2.Get("SMS_SCCM_BitLocker")
objDetail.Delete_

'CREATE NEW CLASS

Set objCIMV2 = objWBEMLocation.ConnectServer(, "root\cimv2\SMS")

Set objNew = objCIMV2.Get

objNew.Path_.Class = "SMS_SCCM_BitLocker"

objNew.Properties_.add "BitLocker_Numerical_Password", wbemString

objNew.Properties_.add "BitLocker_Password", wbemString

objNew.Properties_.add "Drive", wbemString

objNew.Properties_.add "DriveLabel", wbemString

objNew.Properties_.add "Size", wbemString

objNew.Properties_.add "BitLocker_Version", wbemString

objNew.Properties_.add "Conversion_Status", wbemString

objNew.Properties_.add "Percentage_Encrypted", wbemString

objNew.Properties_.add "Encryption_Method", wbemString

objNew.Properties_.add "Protection_Status", wbemString

objNew.Properties_.add "Lock_Status", wbemString

objNew.Properties_.add "Automatic_Unlock", wbemString

objNew.Properties_.add "ScriptLastRan", wbemString

objNew.Properties_("Drive").Qualifiers_.add "key", True

objNew.Put_

strLine = ""

' COLLECT DETAILS FOR EACH INSTANCE

Set objCIMV2 = objWBEMLocation.ConnectServer(, "root\cimv2\SMS")

For y = 1 To x

 Set objDetail = objCIMV2.Get("SMS_SCCM_BitLocker").SpawnInstance_

 objDetail.Drive = objArray(y,0)

 objDetail.DriveLabel = objArray(y,1)

 objDetail.Size = objArray(y,2)

 objDetail.BitLocker_Version = objArray(y,3)

 objDetail.Conversion_Status = objArray(y,4)

 objDetail.Percentage_Encrypted = objArray(y,5)

 objDetail.Encryption_Method = objArray(y,6)

 objDetail.Protection_Status = objArray(y,7)

 objDetail.Lock_Status = objArray(y,8)

 objDetail.Automatic_Unlock = objArray(y,9)

 objDetail.ScriptLastRan = objArray(y,10)

 'SET DRIVE OUTPUT FOR SPECIFIC DRIVE LETTER
 objWshShell.Run "%comspec% /C manage-bde.exe -protectors -get " & objDetail.Drive &
 " & temp.log",0,true

 strPassword = ""
 strNumerical = "" 

 'RETURN BITLOCKER PASSWORD
 'open file for reading
 Set outputManageBde = objFSO.OpenTextFile("temp.log")

 Do While Not outputManageBde.AtEndOfStream

 strLine = outputManageBde.ReadLine

 If InStr(strLine,"Password") & <> 0 Then

 strPassword = outputManageBde.ReadLine

 If InStr(strPassword, "ID:") Then

 strPassword = Trim(strPassword)

 strPassword = Right(strPassword, Len(strPassword)-4) 

 end if 

 End If 

 Loop

 'close file
 outputManageBde.Close

 'RETURN NUMERICAL PASSWORD
 'open file for reading
 Set outputManageBde = objFSO.OpenTextFile("temp.log")

 Do While Not outputManageBde.AtEndOfStream

 strLine = outputManageBde.ReadLine

 If InStr(strLine,"Password") & <> 0 Then

 strNumerical = outputManageBde.ReadLine

 If InStr(strNumerical, "ID:") Then

 strNumerical = Trim(strNumerical)

 strNumerical = Right(strNumerical, Len(strNumerical)-4) 

 end if 

 Exit Do
 End If 

 Loop

 'close file
 outputManageBde.Close

 objWshShell.Run "%comspec% /C del /q temp.log",0,false

 WScript.Sleep 250

 objDetail.BitLocker_Numerical_Password = Trim(strNumerical)
 objDetail.BitLocker_Password = Trim(strPassword) 

 ' WRITE DETAILS
 objDetail.Put_
Next

WScript.Quit(0)

' FROM WMI EXPLORER

' MOF

'class SMS_SCCM_BitLocker
'{
 'string BitLocker_Numerical_Password;
 'string BitLocker_Password;
 '[key] string Drive;
 'string DriveLabel;
 'string Size;
 'string BitLocker_Version;
 'string Conversion_Status;
 'string Percentage_Encrypted;
 'string Encryption_Method;
 'string Protection_Status;
 'string Lock_Status;
 'string Automatic_Unlock;
 'string ScriptLastRan;
'};

' SCRIPT TO RETURN PROPERTIES

'On Error Resume Next

'Const wbemFlagReturnImmediately = &h10
'Const wbemFlagForwardOnly = &h20

'Set wshNetwork = WScript.CreateObject("WScript.Network")
'strComputer = wshNetwork.ComputerName

'strQuery = "SELECT * FROM SMS_SCCM_BitLocker"

'Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\ROOT\cimv2\SMS")
'Set colItems = objWMIService.ExecQuery(strQuery, "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)

'For Each objItem in colItems

 'WScript.StdOut.WriteLine "Automatic_Unlock: " & objItem.Automatic_Unlock
 'WScript.StdOut.WriteLine "BitLocker_Numerical_Password: " & objItem.Bitlocker_Numerical_Password
 'WScript.StdOut.WriteLine "BitLocker_Password: " & objItem.BitLocker_Password
 'WScript.StdOut.WriteLine "BitLocker_Version: " & objItem.BitLocker_Version
 'WScript.StdOut.WriteLine "Conversion_Status: " & objItem.Conversion_Status
 'WScript.StdOut.WriteLine "Drive: " & objItem.Drive
 'WScript.StdOut.WriteLine "DriveLabel: " & objItem.DriveLabel
 'WScript.StdOut.WriteLine "Encryption_Method: " & objItem.Encryption_Method
 'WScript.StdOut.WriteLine "Lock_Status: " & objItem.Lock_Status
 'WScript.StdOut.WriteLine "Percentage_Encrypted: " & objItem.Percentage_Encrypted
 'WScript.StdOut.WriteLine "Protection_Status: " & objItem.Protection_Status
 'WScript.StdOut.WriteLine "ScriptLastRan: " & objItem.ScriptLastRan
 'WScript.StdOut.WriteLine "Size: " & objItem.Size
 'WScript.StdOut.WriteLine ""

'Next

Notes
PowerShell command to list the namespaces
Get-WmiObject -Namespace Root -Class __Namespace |
Select-Object -Property Name

Back up WMI – before – making any changes

WMI Security
To set the WMI user access permissions

Select Start > Run.
On the Run dialog, type wmimgmt.msc in the Open field.
Click OK to display the Windows Management Infrastructure (WMI) Control Panel.
In the left pane of the WMI Control Panel, highlight the WMI Control (local) entry, right-click, and select the Properties menu option. This displays the WMI Control (Local) Properties dialog box.
Select the Security tab in the WMI Control (Local) Properties dialog box.
In the namespace tree within the Security tab, expand the Root folder. This action lists the available WMI name spaces.
Click the CIMV2 namespace to highlight it.
Click Security to display the Security for ROOT\CIMV2 dialog box.
Click Add in the Security for ROOT\CIMV2 dialog box to display the Select Users or Groups dialog box.
Add the domain user account that will be used as your proxy data collection user account. This should be a domain account (not a local computer account), but it does not need to be an account with administrative access.
Click OK to close the Select Users or Groups dialog box and return to the Security for ROOT\CIMV2 dialog box. The user account you selected should now be listed in the Name list at the top of the dialog box.
Select the newly added user (if it is not already selected) and enable the following permissions:

Enable Account
Remote Enable

Enable the permissions by clicking the Allow box, if it is not already checked for that permission. The Enable Account permission should already be selected, but the Remote Enable permission will need to be selected.


Click OK to close the Security for ROOT\CIMV2 dialog box.

The permissions should now be properly set for the proxy data collection user account.

SCCM – Add BitLocker Passwords

Posted onJune 22, 2017July 18, 2019

My disclaimer

Be careful with modifying MOF files—make backups of your DB and relative MOF files before changing them. And, remember…this is the easy portion of the process. There are more advanced topics coming, which include expanding the namespace, merging queries, digging into WMI, modifying classes, and creating stored procedures in SQL. {hey, if it was easy, everyone would be doing it}

Also see: More Advanced Password Handling

After poking and prodding around in SCCM, I was sure I missed the part where BitLocker passwords could be added to the CM DB, to be easily audited in reports, or used by the support staff. But, upon further research, I realized Microsoft has intentionally left out BitLocker password support to force you to buy MBAM! Ohhhh…Microsoft, say it ain’t so. MS does offer a ‘status’ check for BitLocker, pretty useless, really. Look how anemic it looks…

I thought I would be clever, and ‘hack’ the client portion of MBAM (using the classes from the MSI, I could extrapolate more data, and import that data into the SCCM DB). But, even after all that work…which took a day to figure out, still no BitLocker passwords. Arrrg.

So, by this point, I was done trying to make MBAM work inside of SCCM, without having to actually purchase the product. I moved on to creating my own solution.

The first step is to modify two MOFs: Configuration and SMS_Def. The MOF format allows you to work with and to change WMI settings. Pretty useful for tweaking or adding inventory items to SCCM. You’re probably going to need help creating these MOFs, so check out: https://www.enhansoft.com/blog/how-to-use-regkeytomof

Now, the configuration MOF….

<CMInstallLocation>\Inboxes\clifiles.src\hinv\Configuration.mof

You may have to create this file, <CMInstallLocation>\Inboxes\clifiles.src\hinv\SMS_def.mof

I just added what you see here to the end of a file…and saved. Notice how I’m using the _Custom.

Now, in SCCM, I launched the Client Settings > Default Client Settings, and selected the Hardware Inventory option. I clicked Import and selected the modified SMS_Def.mof file.

The _Custom class I created appeared at the top. YAY! Okay, things are starting to look up.

The final thing I did was to force a policy download to a client computer….wait….and then force a hardware inventory scan.

How was I rewarded for my work? I now have BitLocker passwords in SCCM. 🙂

Are you wondering where those passwords came from? I already had a script I created which uses manage-bde to import the BitLocker passwords into the registry. Something I do need to point out: Is storing BitLocker passwords in the registry best practice? Perhaps not. If security is an issue for you, store the passwords in an encoded, obfuscated manner. See this method I created: http://eddiejackson.net/wp/?p=12710

With a little SQL knowledge, the BitLocker data could be encoded while in storage (and while in the local registry), and then decoded when running reports or queries in SCCM (using a SQL stored procedure). Either way, I…we…now have the ability to store and access the BitLocker passwords—without MBAM.

Notes

Next phase will be to add more data to my _Custom table (like data seen from the MBAM client classes (Win32_BitLockerEncryptionDetails and Win32_Reg_MBAMPolicy), but using PowerShell or C#), and work on adding functionality to a BitLocker Node in SCCM. This functionality would hopefully become a MBAM killer. If I get black bagged…send the detectives to Microsoft.

More about MOF: http://liashov.com/wp-content/uploads/2014/01/Start-to-Finish-Guide-to-MOF-Editing.pdf

SCCM – Configuring WORKGROUP Computers

Posted onJune 21, 2017April 13, 2018

While managing the CM push client in SCCM is relatively easy for domain computers, extra work has to be done to get workgroup computers reporting in.

To open up the lines of communication between the workgroup computer and SCCM, six main tasks need to be completed:

Set up Client Push Installation Properties
Set up Client Push Account
Set up Network Access Account
Set up Name Resolution for the workgroup computer
Enable Network Discovery
Set up Subnets

1 of 6 – Set up the Client Push Installation Properties

In SCCM, Administration > Overview > Site Configuration > Sites and select the site.
In the Home tab, click Settings > Client Installation Settings > Client Push Installation and the Client Push Installation Properties will show.
On the Installation Properties tab, fill in as Installation Properties: SMSSITECODE=001 SMSMP=sccm.DOMAIN.com.

2 of 6 – Set up the Client Push Access Account

The client push account permits the installation of the CM client when deployed from the server. Normally the server account is fine for domain machines, but when performing installations to out-of-band machines, the installation account needs to be localized. So, we’ll use the environmental variable account.

Click the Accounts tab.
Click the YellowStar > New Account and the Windows user Account popup will show.
Fill in with Username %COMPUTERNAME%\Administrator and the Password in the appropriate fields and click OK.

3 of 6 – Set up the Network Access Account

Next, set up the network access account which permits software deployment to workgroup computers, i.e., untrusted domains. Notice, we’re using the environmental variable account again.

Administration > Overview > Site Configuration > Sites, right-click on site, Configure Site Components > Software Distribution > Network Access Account
Select the Specify the account that accesses network locations, click the YellowStar > New Account and the Windows user Account popup will show.
Fill in with Username %COMPUTERNAME%\Administrator and the Password in the appropriate fields and click OK.

4 of 6 – Set up the Client Computer to Resolve to SCCM

On the client computer…we need to provide a manual method for name resolution, as there will be no DNS server.

Open a command prompt on the workgroup computer and run the following commands, or just open lmhosts.sam in notepad and save as lmhosts to the etc location.

cd windows\system32\drivers\etc
copy lmhosts.sam lmhosts

lmhosts file

Open the newly created lmhosts file in notepad and add the following information (with your specific site information):

10.0.0.99 YOURSCCMServer #PRE
10.0.0.99 “SMS_SLP \0x1A” #PRE
10.0.0.99 “MP_YOURSITECODE \0x1A” #PRE

* make sure you adhere to the 15 character requirement between the quotation mark and the forward slash. Meaning, from beginning to end, there should be a total of 15 places.

hosts file

Open the hosts file located in C:\Windows\System32\driver\etc using notepad and add the IP address and fully qualified domain name of the SCCM server.

10.0.0.99 SCCM.DOMAIN.com

Just for the sake of it, open an admin command prompt, run these commands to purge and reload the remote cache table

nbtstat –R

nbtstat -c

5 of 6 – Set up Network Discovery

In SCCM, Administration > Overview > Hierarchy Configuration > Discovery Methods, select Network Discovery.
Right-click on Network Discovery and click Properties.
On the General tab, check the Enable network discovery and click the radio button Topology, client, and client operating system.

6 of 6 – Set up Subnets

Move to the Subnets tab.
Click YellowStar, and enter the Subnet and Mask into New Subnet Assignment and click OK.

Finish

Okay, if everything went as planned, the end result should look something like this, a WORKGROUP computer reporting in:

* I did have to Approve LAB111 before the green check mark appeared.

Notes

More to come about building a PKI to manage off site computers…

Running the cm setup locally

Copy the ccmsetup folder from the server…or from a working client ccmsetup folder to the new client

From an admin prompt:
ccmsetup.exe /SOURCE:C:\client SMSSITECODE=001 /MP:sccm.DOMAIN.com

SCCM – 1702 CmdLet Updates

Posted onJune 15, 2017June 15, 2017

Removed cmdlets

The following cmdlets are no longer supported and have been removed:

Add-CMNokiaDeploymentType
Add-CMOutOfBandServicePoint
Add-CMSystemHealthValidatorPoint
Clear-CMAmtAuditLog
Disable-CMAmtAuditLog
Enable-CMAmtAuditLog
Enable-CMAutomaticAmtProvisioning
Get-CMAutomaticAmtProvisioningStatus
Get-CMCmdletUpdateCheck
Get-CMOutOfBandManagementComponent
Get-CMOutOfBandServicePoint
Get-CMSystemHealthValidatorPoint
Get-CMSystemHealthValidatorPointComponent
New-CMWiredProfileObject
New-CMWirelessProfileObject
Remove-CMAmtProvisioningData
Remove-CMNokiaDeploymentType
Remove-CMOutOfBandServicePoint
Remove-CMSystemHealthValidatorPoint
Send-CMCmdletUpdateCheck
Set-CMCmdletUpdateCheck
Set-CMNokiaDeploymentType
Set-CMOutOfBandManagementComponent
Set-CMOutOfBandServicePoint
Set-CMSystemHealthValidatorPointComponent
Update-CMAmtProvisioning

Support for importing the ConfigurationManager module by using the logical name

There is now support for importing the ConfigurationManager module by using a logical name or path.

If the C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin or equivalent path is added to the PSModulePath variable, the following can be used:

Import-Module ConfigrationManager

Otherwise, the following can be used:

Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager'

How to provide feedback or report issues

Many of the fixes and improvements described in this document are a result of customer feedback. To leave feedback and bug reports, use Microsoft Connect (a Microsoft Account is required) or UserVoice.

Known issues

These are known issues with the Cmdlet Library that are not resolved in this release.

Get-CMAadConditionalAccessPolicy and Set-CMAadConditionalAccessPolicy

64-bit PowerShell environment is required for these cmdlets.

Workaround

None

New cmdlets

These are newly-added cmdlets for this release that add new functionality or enhance the functionality of existing cmdlets.

iOS enrollment profile

New cmdlets have been added for configuring iOS enrollment profiles.

Get-CMIosEnrollmentProfile
New-CMIosEnrollmentProfile
Remove-CMIosEnrollmentPRofile
Set-CMIosEnrollmentProfile

Cloud management gateway cmdlets

New cmdlets have been added for configuring cloud management gateway site roles.

Add-CMCloudManagementGatewayConnectionPoint
Get-CMCloudManagementGateway
Get-CMCloudManagementGatewayConnectionPoint
New-CMCloudManagementGateway
Remove-CMCloudManagementGateway
Remove-CMCloudManagementGatewayConnectionPoint
Set-CMCloudManagementGateway
Set-CMCloudManagementGatewayConnectionPoint
Start-CMCloudManagementGateway
Stop-CMCloudManagementGateway

Data Warehouse Service point cmdlets

New cmdlets have been added for configuring Data Warehouse Service point site roles.

Add-CMDataWarehouseServicePoint
Get-CMDataWarehouseServicePoint
Remove-CMDataWarehouseServicePoint
Set-CMDataWarehouseServicePoint

Deployment cmdlets

Several new cmdlets have been written and improvements made around deployment and deployment monitoring scenarios.

Content distribution status

Get-CMDistributionStatus is a new cmdlet that can be used to get the distribution status of any content object such as applications, settings, or program packages.

Get deployments

Cmdlets have been created to get the object associated with an actual deployment.

Get-CMApplicationDeployment
Get-CMBaselineDeployment
Get-CMConfigurationPolicyDeployment
Get-CMPackageDeployment
Get-CMSoftwareUpdateDeployment
Get-CMTaskSequenceDeployment

Deployment creation

Cmdlets have been created for creating new deployments. These cmdlets supersede pre-existing Start-CM*Deployment cmdlets.

New-CMApplicationDeployment
New-CMBaselineDeployment
New-CMConfigurationPolicyDeployment
New-CMPackageDeployment
New-CMSoftwareUpdateDeployment
New-TaskSequenceDeployment

Improved object pipeline support

Set-CM*Deployment, Remove-CM*Deployment, and Get-CM*DeploymentStatus now fully support the object pipeline.

Start-CM<feature>Deployment cmdlets have been deprecated

The following Start-CM<feature>Deployment cmdlets have been deprecated. The replacement cmdlets may differ in parameter names but should have identical, and in some cases improved, functionality.

Start-CMApplicationDeployment (replaced by New-CMApplicationDeployment)
Start-CMPackageDeployment (replaced by New-CMPackageDeployment)
Start-CMBaselineDeployment (replaced by New-CMBaselineDeployment)
Start-CMConfigurationPolicyDeployment (replaced by New-CMConfigurationPolicyDeployment)
Start-CMTaskSequenceDeployment (replaced by New-CMTaskSequenceDeployment)
Start-CMSoftwareUpdateDeployment (replaced by New-CMSoftwareUpdateDeployment)

Get-CMResultantSettings

This cmdlet will retrieve the resultant client settings for a collection, device, or user.

Operating system upgrade package updates

New cmdlets have been added for creating and modifying operating system upgrade package update schedules.

Clear-CMOperatingSystemUpgradeUpdateSchedule
Get-CMOperatingSystemUpgradeUpdateSchedule
New-CMOperatingSystemUpgradeUpdateSchedule
Remove-CMOperatingSystemUpgradeUpdateSchedule

Remove-CMSoftwareUpdateFromGroup

This cmdlet will remove a software update from a software update group.

Cmdlet changes

The following changes have been made to existing cmdlets for this release. Changes may be new functionality, bug fixes, or deprecations, and may be breaking. If you use one of the cmdlets or feature areas listed in this section, please carefully review the changes to understand how they may affect your use.

Miscellaneous changes

Bugs that were fixed

Large SMS_EmbeddedPropertyList objects used by certain provider classes may not be properly consumed by the cmdlet framework, leading to undefined behavior when getting or setting these values.

Certain combinations of changes to antimalware policies or client settings can cause an invalid policy to be generated. When in this state, the SMS Provider will return an “Instance is not a valid client agent config” error.

Cmdlets for configuring management points and software update points have added the EnableCloudGateway parameter to enable these roles for use with the cloud management gateway.

Site maintenance window configuration

Non-breaking changes

CMMaintenanceWindow cmdlets now support configuring maintenance windows for sites. The output of Get-CMSite can be pipelined into New, Remove, or Set-CMMaintenanceWindow to configure the maintenance windows for a site.

Add-CMDeploymentType

Breaking changes

Support for creating Nokia deployment types has been removed.

Add-CMDeviceAfinityToUser

Bugs that were fixed

Cmdlet may fail unexpectedly with an ObjectNotFound error.

Add-CMDistributionPoint

Bugs that were fixed

Cmdlet allows you to configure a distribution point as Internet-capable when HTTPS is not enabled.

Non-breaking changes

Added AllowProxyTraffic parameter.

Previously unused InstallInternetServer parameter now changes distribution point configuration.

Deprecations

UseComputerAccount parameter has been deprecated. To use a computer account, set UserName to $null.

Add-CMEnrollmentPoint

Bugs that were fixed

Enrollment point role missing configuration settings in created object.

Add-CMEnrollmentProxyPoint

Non-breaking changes

Added ServiceHost parameter to allow specifying a remote enrollment point.

Add-CMIntuneSubscription

Bugs that were fixed

ContactEmail parameter cannot be set to null or empty value.

Add-CMMulticastServicePoint

Bugs that were fixed

UserName does not validate for correct DOMAIN\user formatting.

StartUdpPort and EndUdpPort parameters do not validate values for certain incorrect configurations.

StartIPAddress and EndIPAddress parameters do not validate values for certain incorrect configurations.

Non-breaking changes

Added MessageId, Module, Component, and FilterHashTable parameters for further filtering.

New-CMSoftwareUpdateAutoDeploymentRule

Bugs that were fixed

Remove-CMWindowsEnrollmentProfilePackage

Bugs that were fixed

AmbiguousParameterSet error may be raised when running the cmdlet.

Set-CMActiveDirectoryForest

Non-breaking changes

Added UserName parameter to allow for configuring the discovery account.

PassThru does not return an SMS_ConfigurationItem object.

UseComputerAccount parameter has been deprecated. To use a computer account, set UserName to $null.

Set-CMEmailNotificationComponent

Non-breaking changes

Added UseSsl parameter.

Set-CMFileReplicationRoute

Bugs that were fixed

FileReplicationAccountName parameter cannot be set to null or empty value.

Set-CMHierarchySetting

Non-breaking changes

Added ExclusionCollection, ExclusionCollectionId, ExclusionCollectionName, and EnableExclusionCollection parameters for configuring client upgrade exclusions.

Set-CMIntuneSubscription

Bugs that were fixed

ContactEmail parameter cannot be set to null or empty value.

Non-breaking changes

MaximumUserDevice parameter now supports a value between 1 and 15.

Set-CMIntuneSubscriptionWindowsPhoneProperty

Bugs that were fixed

Cmdlet may unexpectedly fail with an AetCleanupFailure error.

Set-CMMaintenanceTask

Bugs that were fixed

PassThru parameter does not cause an object to be returned.

Set-CMMulticastServicePoint

Bugs that were fixed

UserName parameter does not validate value for correct DOMAIN\user formatting.

Posted onJune 14, 2017August 23, 2019

To install the System Center Configuration Manager Cmdlet Library

Download ConfigMgr2012PowerShellCmdlets.msi .
After the download completes, double-click ConfigMgr2012PowerShellCmdlets.msi to run the setup wizard.

Using the System Center Configuration Manager Cmdlet Library

To start a Windows PowerShell session from the Configuration Manager console

In the Configuration Manager console, click the drop down menu.
Select Connect via Windows PowerShell.

To use the Configuration Manager module in a Windows PowerShell session

Start Windows PowerShell.
Change the directory to

\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin

Access the CmdLets

Adobe Flash Player APSB17-17 Multiple Memory Corruption Vulnerabilities

Posted onJune 13, 2017July 5, 2017

More: https://www.symantec.com/security_response/vulnerability.jsp?bid=99025

Risk

High

Date Discovered

June 13, 2017

Description

Adobe Flash Player is prone to multiple memory-corruption vulnerabilities. An attacker can exploit these issues to execute arbitrary code in the context of the user running the affected application. Failed exploit attempts will likely result in denial-of-service conditions.

Recommendations

Run all software as a nonprivileged user with minimal access rights.

To reduce the impact of latent vulnerabilities, run applications with the minimal amount of privileges required for functionality.

Deploy network intrusion detection systems to monitor network traffic for malicious activity.

Deploy NIDS to monitor network traffic for signs of anomalous or suspicious activity. This includes but is not limited to requests that include NOP sleds and unexplained incoming and outgoing traffic. This may indicate exploit attempts or activity that results from a successful exploit.

Do not accept or execute files from untrusted or unknown sources.

To limit exposure to these and other latent vulnerabilities, never handle files that originate from unfamiliar or untrusted sources.

Do not follow links provided by unknown or untrusted sources.

To reduce the likelihood of attacks, never visit sites of questionable integrity or follow links provided by unfamiliar or untrusted sources.

Run all software as a nonprivileged user with minimal access rights.

To reduce the impact of latent vulnerabilities, run applications with the minimal amount of privileges required for functionality.

Updates are available. Please see the references or vendor advisory for more information.