HP Systems Insignt Manager – Contract and Warranty SQL Query

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
This entry was posted in Hardware, HP, SIM.

2 Responses to HP Systems Insignt Manager – Contract and Warranty SQL Query

  1. Daniel says:

    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?

  2. Pablo says:

    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

Leave a Reply to Daniel Cancel reply