SCCM SQL Query Help Please

Welcome Forums General PowerShell Q&A SCCM SQL Query Help Please

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 7 years, 8 months ago.

  • Author
  • #6360

    Topics: 1562
    Replies: 0
    Points: 0
    Rank: Member

    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)
    $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

    # Close the Connection

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


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

    Example Output:

    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

    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.

The topic ‘SCCM SQL Query Help Please’ is closed to new replies.