Archive

Monthly Archives: February 2015

Need to get the warranty type and expiration date for all Dell computers and servers in the enterprise and save it to a SQL Server staging table for down stream consumption into the Asset Data Warehouse.

The Powershell script works, but I want the iteration results of the API call to write to a hash table and then make one connection to the database for insert there.


#Initializer server, database, connection, query, and data adapter
$serverName = “Instance Name”
$databaseName = “Database Name”

$Query = “SELECT SerialNumber FROM ViewWithAListOfSerialNumbers’;”

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString =”Server=$serverName;Database=$databaseName;trusted_connection=true;”
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Query,$Connection)
$DataSet = New-Object System.Data.DataSet
$DataAdapter.Fill($DataSet) | Out-Null
$Connection.Close()

# Create the function for the call to Dell
Function fnDellWarranty ($ServiceTag) {
$service = New-WebServiceProxy -Uri “http://xserv.dell.com/services/AssetService.asmx?WSDL”
$guid = [guid]::NewGuid()
$info = $service.GetAssetInformation($guid,’Dell Warranty’,$ServiceTag)

if($info -ne $Null){

$Result=@{
‘ServiceLevelDescription’=$info[0].Entitlements[0].ServiceLevelDescription.ToString()
‘EndDate’=$info[0].Entitlements[0].EndDate.ToShortDateString()
‘EntitlementType’=$info[0].Entitlements[0].EntitlementType.ToString()
‘StartDate’ = $($($info[0].Entitlements.StartDate.ToShortDateString()) | Sort-Object)[0]
‘DaysLeft’=$info[0].Entitlements[0].DaysLeft
‘ServiceProvider’=$info[0].Entitlements[0].ServiceProvider
‘ServiceTag’=$info[0].AssetHeaderData.ServiceTag
‘SystemType’=$info[0].AssetHeaderData.SystemType
‘SystemModel’=$info[0].AssetHeaderData.SystemModel
‘ShipDate’=$info[0].AssetHeaderData.SystemShipDate.ToShortDateString()
}

$obj = New-Object -TypeName psobject -Property $result

}

Return $obj
}

#add dataset to array
$SArray = $DataSet.tables[0].Rows

# Clear the Sql that will stage the data
IF ($SArray.count -gt 0) {
$Connection.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.Connection = $Connection
$cmd.CommandText = “TRUNCATE TABLE stage.DellWarranty;”
$cmd.ExecuteNonQuery()

$Connection.Close()}

$SArray | ForEach-Object {

#Run function
If(!($_.SerialNumber -eq $Null)) {

$fnR = $(fnDellWarranty $_.SerialNumber)

#set variables
$StartDate = $fnR.StartDate
$ShipDate = $fnR.ShipDate
$DaysLeft = $fnR.DaysLeft
$ServiceLevelDescription = $fnR.ServiceLevelDescription
$EndDate = $fnR.EndDate
$SystemType = $fnR.SystemType
$SystemModel = $fnR.SystemModel
$EntitlementType = $fnR.EntitlementType
$ServiceProvider = $fnR.ServiceProvider
$ServiceTag = $fnR.ServiceTag
}
#my mistake was in the loop. I had Return $SerialNumber, $StartDate, then nothing would write to the table. Once that was returned, this worked

if($fnR -ne $Null){
$Connection.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.Connection = $Connection
$cmd.CommandText = “SET NOCOUNT ON; INSERT INTO dbo.DellWarranty(SerialNumber,ServiceTag,StartDate,ShipDate,DaysLeft,ServiceLevelDescription,EndDate,SystemType,SystemModel,EntitlementType,ServiceProvider)`
VALUES(‘{0}’,'{1}’,'{2}’,'{3}’,'{4}’,'{5}’,'{6}’,'{7}’,'{8}’,'{9}’,'{10}’)”`
-f $_.SerialNumber, $ServiceTag,$StartDate,$ShipDate,$DaysLeft,$ServiceLevelDescription,$EndDate,$SystemType,$SystemModel,$EntitlementType,$ServiceProvide

$cmd.ExecuteNonQuery()

$Connection.Close()
}

}
$SerialNumber =$Null