SCCM SQL Query Help Please

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 5 months ago.

  • Author
    Posts
  • #6360

    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.

You must be logged in to reply to this topic.