Getting Started with SQL Job SchedulesGetting Started with SQL Job Schedules

SQL Query to identify Windows 10 Security Features for HP / DELL

Windows 10 Security Features

After extending deploying the DELL/HP packages, importing MOF files, and extending hardware inventory.

Packages to include all of the above will be available for download on my TechNet profile.

NOTE: if you only use dell or HP then please keep in mind of the Union statement….Only use the part of the query that you need.

Select
CS.Manufacturer0 [Manufacturer],
CS.Model0 [Model],
CS.Name0 [Name],
CPU.Is64Bit0 [64 Bit0],
/*The CPU.SecondLevelAddressTranslationExtensions field below is commented-out because it will only exist in environments with Win8 and later systems. Uncomment only in those environments*/
–CPU.SecondLevelAddressTranslationExtensions [SLAT], –This field will only exist for Win8 and later systems
DMA.Name0 [DMA Name],
DMA.Status0 [DMA Status],
DBIOS.Version0 + ‘ – ‘ + Convert(varchar(25), DBIOS.ReleaseDate0) [BIOS Version and Release Date],
Dell.AttributeName0 [BIOS Attribute], –Derived from the WMI attributes created by Dell CM and imported MOF
Dell.CurrentValue0 [BIOS Value], –Derived from the WMI attributes created by Dell CM and imported MOF
Dell.PossibleValuesDescription0 [Possible Values], –Derived from the WMI attributes created by Dell CM and imported MOF
TPM.IsEnabled_InitialValue0 [TPM Enabled], –Derived from the WMI attributes created by importing the TPM MOF
TPM.IsOwned_InitialValue0 [TPM Owned], –Derived from the WMI attributes created by importing the TPM MOF
TPM.IsActivated_InitialValue0 [TPM Activated], –Derived from the WMI attributes created by importing the TPM MOF
TPM.PhysicalPresenceVersionInfo0 [TPM Version] –Derived from the WMI attributes created by importing the TPM MOF
From
v_GS_Computer_System as CS
Join
v_GS_Processor as CPU on CS.ResourceID = CPU.ResourceIDLeft Join
v_GS_DMA_Channel as DMA on CS.ResourceID = DMA.ResourceID
Left Join
–Derived from the WMI classes created by the Dell CM Tool and imported MOF
v_GS_Dell_DCIM_BIOSEnumeration0 as Dell on CS.ResourceID = Dell.ResourceID
Left Join
v_GS_Dell_DCIM_BIOSElement0 as DBIOS on CS.ResourceID = DBIOS.ResourceID
Left join
–Derived from the WMI classes created by importing the TPM MOF
v_GS_TPM as TPM on TPM.ResourceID = CS.ResourceID
Where
Dell.AttributeName0 = ‘Secure Boot’ or Dell.AttributeName0 Like ‘%UEFI%’ or Dell.AttributeName0 = ‘Boot Mode’ or Dell.AttributeName0 like ‘%Virtual%’ or Dell.AttributeName0 like ‘Trusted Platform Module%’ or Dell.AttributeName0 like ‘%Direct IO%’
Union –Used to combine the results of these two queries into a single result-set
Select
CS.Manufacturer0 [Manufacturer],
CS.Model0 [Model],
CS.Name0 [Name],
CPU.Is64Bit0 [64 Bit0],
/*The CPU.SecondLevelAddressTranslationExtensions field below is commented-out because it will only exist in environments with Win8 and later systems. Uncomment only in those environments*/
–CPU.SecondLevelAddressTranslationExtensions [SLAT],
DMA.Name0 [DMA Name],
DMA.Status0 [DMA Status],
/* Due to how HP BIOS date/version detail is stored in the ConfigMgr database, this subquery is needed to ensure that HP systems, which haven’t submitted this data, aren’t excluded from the query results.*/
(Select Distinct Value0 from v_GS_HPBIOS_BIOSSTRING Where ResourceID = cs.ResourceID and Name0 = ‘BIOS Version & Date’) [BIOS Version and Release Date],
HP.Name0 [BIOS Attribute], –Derived from the WMI attributes created by importing the HP MOF
HP.CurrentValue0 [BIOS Value], –Derived from the WMI attributes created by importing the HP MOF
HP.PossibleValues0 [Possible Values], –Derived from the WMI attributes created by importing the HP MOF
TPM.IsEnabled_InitialValue0 [TPM Enabled], –Derived from the WMI attributes created by importing the TPM MOF
TPM.IsOwned_InitialValue0 [TPM Owned], –Derived from the WMI attributes created by importing the TPM MOF
TPM.IsActivated_InitialValue0 [TPM Activated], –Derived from the WMI attributes created by importing the TPM MOF
TPM.PhysicalPresenceVersionInfo0 [TPM Version] –Derived from the WMI attributes created by importing the TPM MOF
From
v_GS_Computer_System as CS
Join
v_GS_Processor as CPU on CS.ResourceID = CPU.ResourceID
Left Join
v_GS_DMA_Channel as DMA on CS.ResourceID = DMA.ResourceID
Left Join
–Derived from the WMI classes created by the imported HP MOF
v_GS_HPBIOS_BIOSENUMERATION as HP on CS.ResourceID = HP.ResourceID
Left join
–Derived from the WMI classes created by importing the TPM MOF
v_GS_TPM as TPM on TPM.ResourceID = CS.ResourceID
Where
HP.Name0 = ‘Secure Boot’ or HP.Name0 = ‘UEFI Boot Mode’ or HP.Name0 like ‘Virtualization Technology%’
Order By
cs.Manufacturer0, cs.Model0

ALSO CHECK: SCCM Revoked Clients Registration

chris buck

Founder of SCCMF12TWICE.

Lead SCCM Architect/Engineer for various environment all over the world. Primarily focused on reducing complexity of enterprise environments. This also includes migrating customers to the cloud, and to windows 10 with servicing upgrades.

Strong believer in data driven operations. If we can identify problems with analytics, and visualize it to management we can reduce the amount of problems, at the same time improve customers experience, reducing support costs.

Add comment

eighteen − two =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

%d bloggers like this: