A query specifies which database or management-system operation should run. In PowerShell automation, it narrows a command to the required object, such as one service, or carries SQL that creates and updates tables. section 9.6.3 section 26.3
Build queries around a repeatable command-and-check workflow:
- Choose the class, table, or operation that matches the task rather than retrieving unnecessary objects. section 9.6.3
- Store longer SQL in a readable here-string and expose it with verbose output when troubleshooting syntax. section 26.3
- Execute the query through the appropriate command object, then inspect return values or errors instead of assuming success. section 9.6.3 section 26.3
Selecting one service
A CIM query such as SELECT * FROM Win32_Service WHERE Name='BITS' gives Invoke-CimMethod the specific service instance needed to change its startup credentials. Adding ComputerName prepares the same operation for another machine. section 9.6.3
$CimMethodParameters = @{
Query = "SELECT * FROM Win32_Service WHERE Name='BITS'"
Method = "Change"
Arguments = @{
'StartName' = 'DOMAIN\User'
'StartPassword' = 'P@ssw0rd'
}
ComputerName = $env:computername
}
Invoke-CimMethod @CimMethodParameters
Inspect the complete parameterized Invoke-CimMethod example, including the query, method, arguments, and computer name. section 9.6.3
Listing 26.3 Adding code for table creation
function New-DiskInfoSQLTable {
[CmdletBinding()]
param()
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $DiskInfoSqlConnection
$conn.Open()
$sql = @"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='diskinfo' AND
xtype='U')
CREATE TABLE diskinfo (
ComputerName VARCHAR(64),
DiskSize BIGINT,
DriveType TINYINT,
FreeSpace BIGINT,
DriveID CHAR(2),
DateAdded DATETIME2
)
"@
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandText = $sql
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
}
$DiskInfoSqlConnection =
"Server=localhost\SQLEXPRESS;Database=Scripting;Trusted_Connection=True;"
Export-ModuleMember -Function Get-DiskInfo
Export-ModuleMember -Variable DiskInfoSqlConnection
Inspect a SQL here-string that conditionally creates a disk-information table through SqlConnection and SqlCommand. section 26.3
