This is the query I came up with for my customers environment when trying to evaluate their SCCM Collection evaluation problems. Example 5: Run a query and display verbose output Using powershell to gather disk used into a sql table. SQL Query for Distribution Point Free Space December 20, 2020 April 24, 2020 by Jeff In this SQL query I return the Server Name, Site Code, Description, Domain Name, Volume, Total (GB), Free (GB) and % Free. SCCM Query Collection List. The following script can be used to perform a simple PING test to a list of computers using PowerShell. SCCM – Query for Windows 10 by Build Numbers Posted on October 8, 2018 October 23, 2018 Author MrNetTek In SCCM Console > Monitoring > Queries, Copy/paste this as a new query. Simply create the script (I call mine PingTest.ps1″) and in the same folder create a Computers.txt with a list of devices you want to ping. The following Get-SCCMData function uses this cmdlet to connect to SCCM DB and run predefined SQL queries: Posted on February 18, 2018 February 22, 2018 Author MrNetTek. This is a SQL query written in PowerShell. It is implemented in SqlServer module and can be added to custom PowerShell modules for usage of other scripts. ConfigMgr locked object, powershell configmgr sql, powershell sccm sql, powershell unlock object, SCCM locked object 2 Comments Have you ever come across a lovely message like this from the SCCM 2012 console? We use the .NET assembly system.data.odbc.odbcconnection to use ODBC connections present on the system to query the database. All of your core SCCM Site Systems is a good example. All queries tested in SCCM Current Branch 1902. ODBC connections in Powershell 4.0 and higher SCCM Server 2012 WMI Query For Missing Microsoft Patches On Specific Host. Let’s run a SELECT query against a table in the MS SQL database: SCCM Clients Collections Clients not approved select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, … ... SCCM - Query Powershell Version. Powershell 4 introduced new cmdlets that make it very easy to create and manage ODBC connections. T-SQL Queries in PowerShell Using System.Data.OleDb. Then it calls Get-Item to retrieve a SQL Management Object Server object for use as the ServerInstance parameter of Invoke-Sqlcmd. Absolutely. This post explains how to work with ODBC connections in Powershell. Also I will do another blogpost with the sql queries to identify a number of other items to include orphaned collections, SCCM Collection update types, etc… There is a screenshot in the bottom of this … 1. SCCM Queries just query WMI on a SCCM Site Server with the SMS Provider role installed. 3. It connects to the SCCM DB and returns Custom_Data for C and D drives. 2 (Powershell) Method to return a list of groups where a specific user has 'write member' security. Invoke-SqlCmd can be used to query SQL DB of SCCM. SCCM – PowerShell – SQL Query. This command uses Set-Location to navigate to the SQL ServerWindows PowerShell provider path for an instance of the SQL Database Engine. You can query it yourself, it's. Get-WmiObject -namespace root/SMS/Site_XXX ` -Query "SELECT * FROM SMS_Device" Keep in mind that SCCM queries use a slightly different naming context, so you'll need to change SMS_R_System to something … Here is a sample PowerShell script to connect SQL Server using System.Data.OleDb class. Since PowerShell can access .NET Framework classes, you can use classes from System.Data.OleDb to execute T-SQL queries. … Read more 0.