It was time to renew our warranty contacts with HP and we needed a quick and efficient way to gather and submit all of our HP hardware’s information to our vendor for renewal. The canned “Warranty-Contract – Servers” query is not very useful as there are no serial numbers, product name, product numbers, addresses or contact information in the report. If you try to add the other columns it outputs to separate tables, proving to be a useless report. These other pieces of information are hidden in other views and tables in the Insight database. After much tinkering around and much help from our DBA we came up with the following query (note a new view is required to be created in the DB)…
Sponsors, article continues below...
SELECT UPPER(a.DeviceName) DeviceName , b.IPAddress, b.ProductName, b.ProductId, b.OSVendor, b.SerialNumber, b.AssetTag, b.ROMVersion, b.MemorySize, b.CPUs, -- a.startDate StartDate , -- a.EndDate EndDate , dbo.fn_ConvertDate(a.startDate) StartDate , dbo.fn_ConvertDate(a.EndDate) EndDate , a.ExpirationStatus , a.responseTime ResponseTime, a.coverageWindow Coverage, a.serviceLevel ServiceLevel, c.CSInfo, c.PCName, c.address1, c.address2, c.city, c.state, c.postalCode, c.country, c.custTimeZone, c.PCPhone, c.PCEmail FROM dbo.R_WarrantyContract a LEFT JOIN ( SELECT DeviceKey, IPAddress, ProductName, ProductId, OSVendor, SerialNumber, AssetTag, ROMVersion, MemorySize, NumberOfCPU CPUs FROM dbo.R_Inventory GROUP BY DeviceKey, IPAddress, ProductName, ProductId, OSVendor, SerialNumber, AssetTag, ROMVersion, MemorySize, NumberOfCPU ) b ON a.DeviceKey = b.DeviceKey LEFT JOIN ( SELECT DeviceKey, CSInfo, address1, address2, city, state, postalCode, country, custTimeZone, PCName, PCPhone, PCEmail FROM EXT_CustSiteNContactsInfo ) c ON a.DeviceKey = c.DeviceKey ORDER BY c.CSInfo
I had to create a new “mickey mouse” view do to time constraints, in which the above query selects from.
SELECT dbo.devices.Name AS DeviceName, dbo.devices.DeviceKey, SysCustomer.companyName AS CSInfo, SysCustomer.address1, SysCustomer.address2, SysCustomer.city, SysCustomer.state, SysCustomer.postalCode, SysCustomer.country, SysCustomer.custTimeZone, ISNULL(PC.contactSalutation, N'') + N' ' + ISNULL(PC.contactFirstName, N'') + N' ' + ISNULL(PC.contactLastName, N'') AS PCName, PC.contactPhone AS PCPhone, PC.contactEmail AS PCEmail, ISNULL(SC.contactSalutation, N'') + N' ' + ISNULL(SC.contactFirstName, N'') + N' ' + ISNULL(SC.contactLastName, N'') AS SCName, SC.contactPhone AS SCPhone, SC.contactEmail AS SCEmail, ISNULL(PSC.contactSalutation, N'') + N' ' + ISNULL(PSC.contactFirstName, N'') + N' ' + ISNULL(PSC.contactLastName, N'') AS PSCName, PSC.contactPhone AS PSCPhone, PSC.contactEmail AS PSCEmail, - 1 AS SnapshotID FROM dbo.devices INNER JOIN (SELECT dbo.SIM_SysCustomer.companyName, dbo.SIM_SysCustomer.address1, dbo.SIM_SysCustomer.address2, dbo.SIM_SysCustomer.city, dbo.SIM_SysCustomer.state, dbo.SIM_SysCustomer.postalCode, dbo.SIM_SysCustomer.country, dbo.SIM_SysCustomer.custTimeZone, dbo.deviceAssocObjects.nodeID AS SysNodeId FROM dbo.SIM_SysCustomer INNER JOIN dbo.deviceAssocObjects ON dbo.deviceAssocObjects.assocObjectLUID = dbo.SIM_SysCustomer.customerLuid INNER JOIN dbo.devices AS devices_4 ON dbo.deviceAssocObjects.nodeID = devices_4.nodeLuid) AS SysCustomer ON dbo.devices.nodeLuid = SysCustomer.SysNodeId LEFT OUTER JOIN (SELECT dbo.SIM_SysContact.contactSalutation, dbo.SIM_SysContact.contactFirstName, dbo.SIM_SysContact.contactLastName, dbo.SIM_SysContact.contactPhone, dbo.SIM_SysContact.contactEmail, deviceAssocObjects_3.nodeID AS PCNodeId FROM dbo.SIM_SysContact INNER JOIN dbo.deviceAssocObjects AS deviceAssocObjects_3 ON deviceAssocObjects_3.assocObjectLUID = dbo.SIM_SysContact.contactLuid AND deviceAssocObjects_3.assocType = 0 INNER JOIN dbo.devices AS devices_3 ON deviceAssocObjects_3.nodeID = devices_3.nodeLuid) AS PC ON PC.PCNodeId = dbo.devices.nodeLuid LEFT OUTER JOIN (SELECT SIM_SysContact_2.contactSalutation, SIM_SysContact_2.contactFirstName, SIM_SysContact_2.contactLastName, SIM_SysContact_2.contactPhone, SIM_SysContact_2.contactEmail, deviceAssocObjects_2.nodeID AS PCNodeId FROM dbo.SIM_SysContact AS SIM_SysContact_2 INNER JOIN dbo.deviceAssocObjects AS deviceAssocObjects_2 ON deviceAssocObjects_2.assocObjectLUID = SIM_SysContact_2.contactLuid AND deviceAssocObjects_2.assocType = 1 INNER JOIN dbo.devices AS devices_2 ON deviceAssocObjects_2.nodeID = devices_2.nodeLuid) AS SC ON SC.PCNodeId = dbo.devices.nodeLuid LEFT OUTER JOIN (SELECT SIM_SysContact_1.contactSalutation, SIM_SysContact_1.contactFirstName, SIM_SysContact_1.contactLastName, SIM_SysContact_1.contactPhone, SIM_SysContact_1.contactEmail, deviceAssocObjects_1.nodeID AS PCNodeId FROM dbo.SIM_SysContact AS SIM_SysContact_1 INNER JOIN dbo.deviceAssocObjects AS deviceAssocObjects_1 ON deviceAssocObjects_1.assocObjectLUID = SIM_SysContact_1.contactLuid AND deviceAssocObjects_1.assocType = 2 INNER JOIN dbo.devices AS devices_1 ON deviceAssocObjects_1.nodeID = devices_1.nodeLuid) AS PSC ON PSC.PCNodeId = dbo.devices.nodeLuid
I ran the query in MS SQL Server Management Studio, exported the results to excel and shot it off to my vender. I might clean this up and just put it all in a single view someday. When I do I’ll update this post.
UPDATE: 6-23-2010
You will need the following function added to your DB before running…
/****** Object: UserDefinedFunction [dbo].[fn_ConvertDate] Script Date: 06/22/2010 15:21:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fn_ConvertDate] (@Datetime BIGINT) RETURNS DATETIME AS BEGIN DECLARE @LocalTimeOffset BIGINT; DECLARE @AdjustedLocalDatetime BIGINT; SELECT @LocalTimeOffset = DATEDIFF(SECOND,GETDATE(),GETUTCDATE()) SELECT @AdjustedLocalDatetime = (@Datetime – @LocalTimeOffset)/1000 RETURN (SELECT DATEADD(SECOND,@AdjustedLocalDatetime, CAST(’1970-01-01 00:00:00′ AS datetime))) END; GO
thanks for posting!! I am trying it and I am getting:
Arithmetic overflow error converting expression to data type int.
any ideas of what I maybe doing wrong?
I was able to get around the error by checking if the length is not greater than 13. That was the pattern i saw you can try this modification to the function if you like
/****** Object: UserDefinedFunction [dbo].[fn_ConvertDate] Script Date: 06/22/2010 15:21:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_ConvertDate] (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
IF LEN(CAST(@DATETIME as varchar)) <= 13
BEGIN
DECLARE @LocalTimeOffset BIGINT
DECLARE @AdjustedLocalDatetime BIGINT
DECLARE @ReturnDate DATETIME
SELECT @LocalTimeOffset = DATEDIFF(SECOND,GETDATE(),GETUTCDATE())
SELECT @AdjustedLocalDatetime = (@Datetime – @LocalTimeOffset) / 1000
SELECT @ReturnDate = DATEADD(SECOND,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime))
END
ELSE
BEGIN
SELECT @ReturnDate = CAST('1970-01-01 00:00:00' AS datetime)
END
RETURN @ReturnDate
END