Query to find all hardware models – including Lenovo

I needed to find all the hardware models in our environment. Lenovo stores their model name in ‘version’ instead of ‘model’ like other vendors. I ran the following SQL code on our SCCM/MECM database to get the data I needed. This code can be converted to a report in SSRS or used in PowerShell.

Note: Change the collectionID

with CTE AS
(
SELECT  GCS.Manufacturer0
    ,CASE WHEN GCS.Manufacturer0 = 'LENOVO' THEN CSP.Version00 ELSE GCS.Model0 END AS 'Model'
FROM v_GS_COMPUTER_SYSTEM GCS
    JOIN COMPUTER_SYSTEM_PRODUCT_DATA CSP on GCS.ResourceID = CSP.MachineID
JOIN fn_rbac_FullCollectionMembership('Disabled')  fcm ON GCS.ResourceID=fcm.ResourceID 
WHERE fcm.CollectionID='<collectionID>'
)

select Manufacturer0,model,count(model) as count from cte group by Manufacturer0,model
order by count DESC

If you want to run this in PowerShell be sure to use the Invoke-Sqlcmd2 module.

What .exe was blocked in Windows Firewall?

By default Windows does not log what executable is being blocked by Windows Firewall. In order to log what executable is being blocked run the following command as an administrator:

auditpol /set /subcategory:"{0CCE9226-69AE-11D9-BED3-505054503030}" /success:disable /failure:enable

Read more “What .exe was blocked in Windows Firewall?”

Get notified about duplicate host names in SCCM

As part of the general maintenance and upkeep of SCCM we have a “Duplicate Host Names” collection in SCCM.

This collection finds duplicate host names but unfortunately does not notify us when one is found. The script below will send an e-mail when the collection membership is greater than 0. We have it set up as a scheduled task that runs daily.

Read more “Get notified about duplicate host names in SCCM”

Test your SCCM application deployment using PsExec

When getting an application ready for deployment it is typically much faster to test that the install works as expected by running it as SYSTEM. When an application is ran from Software Center it is ran under the SYTEM context. Manually running the install as SYSTEM means you do not need to wait for your content to refresh or for your policies to refresh. This allows you to test and debug much quicker.

Read more “Test your SCCM application deployment using PsExec”