Archive

Third Party Apps and SQL

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

A vendor installed an instance of SQL Server Express that supports an important application. Assume the vendor thought the Builtin\Administrators login was still a default for 2008 as it was for 2005.  The sa password is unknown.  How to gain access to the database server?

Added myself to the Windows Administrator Group.

The service owner is ‘Network Service’.

Still no connection but will keep testing ideas.