HP Systems Insignt Manager – Contract and Warranty SQL Query
UPDATE: 6-23-2010
You will need the following function added to your DB before running…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /****** 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 |
….
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...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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.
What version of SIM are you using? We do not seem to have the fn_ConvertDate function… Where does this come from?
You have to create the function to convert the date… Here is the one I am using:
USE [Insight_v50_0_171434378]
GO
/****** 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