SCCM SQL Query Help Please

PowerShell Help
1

by seanthehulk at 2012-09-25 15:48:53

Disclaimer, I have years of VBScript experience but just started working with PowerShell. I took Jason Helmick’s PowerShell Class a few weeks ago.

I wrote a function to query our SCCM Database for a provided computers Last Hardware Scan Date. My problem is the additional stuff I am getting with the returned data.

Variables $strSCCMDB and $strSCCMServer are defined globally
#==================================================================================================
# Function Name: Get-SMSLastHWScan
# Variables Passed: $strComputerName
# Value Returned: Time stamp of the last hardware scan
# Purpose: Query SCCM DB for the computers last hardware scan
# Comments:
#==================================================================================================
Function Get-SMSLastHWScan($strComputerName)
{
$SQLQuery = "SELECT v_GS_WORKSTATION_STATUS.LastHWScan FROM $strSCCMDB.dbo.v_GS_WORKSTATION_STATUS, `
$strSCCMDB.dbo.v_R_System WHERE v_R_System.Netbios_Name0 = ‘$strComputerName’ AND v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID"

# Create the SQL Connection Object
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server = $strSCCMServer; Database = $strSCCMDB; Integrated Security = True"

# Create SQL Command Object
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery
$SQLCommand.Connection = $SQLConnection

# Create the SQL Adapater Object
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand

# Populate the Data Set
$DataSet = New-Object System.Data.DataSet
$SQLAdapter.Fill($DataSet)

# Close the Connection
$SQLConnection.Close()

$global:dtmLastHWScan = $DataSet.Tables[0].LastHWScan # I assume I need to do something else here
} # End Function Get-SMSLastHWScan

Get-SMSLastHWScan("TestComputer")
$dtmLastHWScan

I am getting the number 1 which I assume is the column number and then the data as a Date object.

Example Output:
1

Tuesday, September 25, 2012 7:58:00 AM

Can somebody please share with me how I can just return that date object without the additional stuff in front of it?

Thank you kindly,

Sean Andrews
Solutions Engineer
CSC
by cmille19 at 2012-09-25 16:35:10
The number 1 is actually the number of rows being returned when you call the fill method on your data adapter. To suppress the output send to out-null:

$SQLAdapter.Fill($DataSet) | out-null
by seanthehulk at 2012-10-04 10:48:18
Worked like a charm, thank you.