Dell Warranty Api call save to Sql Database Table

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: