Last updated on September 28th, 2022 at 09:37 am
This question comes up from time to time, where someone want to query MEMCM using WQL and runs into problems. I have done allot of work with WQL recently. What you will find when you query WMI with WQL is that you will easily hit WMI timeouts or Quota Violation from WMI. There are a few tricks to reduce these errors but ultimately there is not going to be an easy work around. Instead, you will want to query the Microsoft Endpoint Manager Configuration Manager (MEMECM / MECM / SCCM) SQL Server database (db) directly using PowerShell using a SQL query. This article will show you How to use PowerShell to query SQL Database using a MEMCM database as the example.
How to use PowerShell to query SQL Database
The actual PowerShell to query the MECM db isn’t that difficult. Below is an example that will query all of ResourceID and NetBIOS Names you have within your MECM environment.
$SQLServer = “memcm”
$db = “cm_dev”
$TSQL = “Select RV.ResourceId, rv.Netbios_name0 from dbo.v_r_system RV”
$Results = Invoke-Sqlcmd -Query $TSQL -ServerInstance $SQLServer -Database $db
$Results
You can see from the results that it will list each and every computer name with its resouceid. With this basic PowerShell script, you do anything you want. You can query CM directly via WQL or PowerShell module / command do preform actions such as adding a computer to a collection. Aka you can mix and match how you access the MECM data to help ensure that your script is fast!
Does mixing and match add to the complexity? Yes, it does but the trade offs are speed. If you are only querying a small amount of data then stay with WQL queries. But as soon as you try to query large
SQL Tips
Here is a list of supported SQL views that you can queries directly with PowerShell SQL Server views in Configuration Manager, since the view list can change over time and docs are almost always the last thing updates. This article will tell you the currently supported views, functions, etc. that are supported to be queried by PowerShell, What Are the Supported SQL Server Views to Use with SCCM Reporting?
It is important to query only the supported views, this article will give you more details as you why you want to do that. Why Is It Important to Use Supported SQL Server Views with SCCM Reporting?
Any of the queries you find on Askgarth.com, you should be able to copy and paste into PowerShell but remember to watch or for stylized quote marks.
PowerShell Tip
Meanwhile if you receive this error message “Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SqlPsPostScript.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170”
Change your PowerShell execution policy and try the script again.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.