Symantec Management Platform (Notification Server)

Hello,

I need help from some SQL experts.  I'm trying to create the following report but am having trouble with two things:

I NEED:

1. Hardware Serial Number needs to be unique - We have a lot of duplicate serials in this report because NS is reporting on the same device with seperate MACs because some of our laptops will connect via wireless and wired connections
2. Hardware Serial Number cannot contain NULL - We have approx. 60 devices out of 8000 that have NULL values due to various reasons (inventory not reporting correctly, some old "white-box" PC's in our environment etc...).  I thought my statement in the WHERE clause below would take care of it, but NULLs are still showing up.

Thank you for any help you can offer!

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [vri2_Computer].[Guid],
   'Computer' AS 'SMTYPE',
   [dca5_AeX AC Identification].[Hardware Serial Number],
   [dca4_HW Logical Device].[Device ID],
   [dca4_HW Logical Device].[Device Class],
   [dca4_HW Logical Device].[Description],
   [dca4_HW Logical Device].[Manufacturer],
   [dca4_HW Logical Device].[Model],
         CASE
         WHEN [dca4_HW Logical Device].[Model] LIKE '7483%' THEN 'ThinkCentre M58p'
  WHEN [dca4_HW Logical Device].[Model] LIKE '4187RWU' THEN 'IdeaPad S9e'
  WHEN [dca4_HW Logical Device].[Model] LIKE '831921U' THEN 'NetVista Personal Computer'
  WHEN [dca4_HW Logical Device].[Model] LIKE '644912U' THEN 'ThinkCentre A61e'
  WHEN [dca4_HW Logical Device].[Model] LIKE '8215D1U' THEN 'ThinkCentre M52'
  WHEN [dca4_HW Logical Device].[Model] LIKE '964523U' THEN 'ThinkCentre M55e'
  WHEN [dca4_HW Logical Device].[Model] LIKE '88089HU' THEN 'ThinkCentre M55p'
  WHEN [dca4_HW Logical Device].[Model] LIKE '6073A2U' THEN 'ThinkCentre M57p'
  WHEN [dca4_HW Logical Device].[Model] LIKE '7052A8U' THEN 'ThinkCentre M91p'
  WHEN [dca4_HW Logical Device].[Model] LIKE '8183%' THEN 'ThinkCentre S50'
  WHEN [dca4_HW Logical Device].[Model] LIKE '8171%' THEN 'ThinkCentre S51'
  WHEN [dca4_HW Logical Device].[Model] LIKE '0301%' THEN 'ThinkPad Edge 15'
  WHEN [dca4_HW Logical Device].[Model] LIKE '259842U' THEN 'ThinkPad L512'
  WHEN [dca4_HW Logical Device].[Model] LIKE '4444%' THEN 'ThinkPad L512'
  WHEN [dca4_HW Logical Device].[Model] LIKE '18362NU' THEN 'ThinkPad R50'
  WHEN [dca4_HW Logical Device].[Model] LIKE '28885RU' THEN 'ThinkPad R51'
  WHEN [dca4_HW Logical Device].[Model] LIKE '18602BU' THEN 'ThinkPad R52'
  WHEN [dca4_HW Logical Device].[Model] LIKE '1859%' THEN 'ThinkPad R52'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2847%' THEN 'ThinkPad SL510'
  WHEN [dca4_HW Logical Device].[Model] LIKE '7417%' THEN 'ThinkPad T400'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2808%' THEN 'ThinkPad T400s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2516DCU' THEN 'ThinkPad T410'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2901%' THEN 'ThinkPad T410s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '291242U' THEN 'ThinkPad T410s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2516ADU' THEN 'ThinkPad T410s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2904G2U' THEN 'ThinkPad T410s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2904HCU' THEN 'ThinkPad T410s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2904HDU' THEN 'ThinkPad T410si'
  WHEN [dca4_HW Logical Device].[Model] LIKE '23738ZU' THEN 'ThinkPad T42'
  WHEN [dca4_HW Logical Device].[Model] LIKE '41742AU' THEN 'ThinkPad T420s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2668%' THEN 'ThinkPad T43'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2687DTU' THEN 'ThinkPad T43'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2242%' THEN 'ThinkPad T500'
  WHEN [dca4_HW Logical Device].[Model] LIKE '22413XU' THEN 'ThinkPad T500'
  WHEN [dca4_HW Logical Device].[Model] LIKE '20892MU' THEN 'ThinkPad T500'
  WHEN [dca4_HW Logical Device].[Model] LIKE '2089CTO' THEN 'ThinkPad T500'
  WHEN [dca4_HW Logical Device].[Model] LIKE '224356U' THEN 'ThinkPad T500'
  WHEN [dca4_HW Logical Device].[Model] LIKE '424329U' THEN 'ThinkPad T520'
  WHEN [dca4_HW Logical Device].[Model] LIKE '1951%' THEN 'ThinkPad T60'
  WHEN [dca4_HW Logical Device].[Model] LIKE '87424GU' THEN 'ThinkPad T60'
  WHEN [dca4_HW Logical Device].[Model] LIKE '200767U' THEN 'ThinkPad T60'
  WHEN [dca4_HW Logical Device].[Model] LIKE '200793U' THEN 'ThinkPad T60p'
  WHEN [dca4_HW Logical Device].[Model] LIKE '6458%' THEN 'ThinkPad T61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '766406U' THEN 'ThinkPad T61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '765901U' THEN 'ThinkPad T61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '64659UU' THEN 'ThinkPad T61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '766301U' THEN 'ThinkPad T61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '646067U' THEN 'ThinkPad T61p'
  WHEN [dca4_HW Logical Device].[Model] LIKE '427623U' THEN 'ThinkPad W520'
  WHEN [dca4_HW Logical Device].[Model] LIKE '74595EU' THEN 'ThinkPad X200'
  WHEN [dca4_HW Logical Device].[Model] LIKE '745433U' THEN 'ThinkPad X200'
  WHEN [dca4_HW Logical Device].[Model] LIKE '74585SG' THEN 'ThinkPad X200'
  WHEN [dca4_HW Logical Device].[Model] LIKE '74553XG' THEN 'ThinkPad X200'
  WHEN [dca4_HW Logical Device].[Model] LIKE '3680FBU' THEN 'ThinkPad X201'
  WHEN [dca4_HW Logical Device].[Model] LIKE '3626F6U' THEN 'ThinkPad X201'
  WHEN [dca4_HW Logical Device].[Model] LIKE '42912YU' THEN 'ThinkPad X220'
  WHEN [dca4_HW Logical Device].[Model] LIKE '64781TU' THEN 'ThinkPad X300'
  WHEN [dca4_HW Logical Device].[Model] LIKE '405717U' THEN 'ThinkPad X301'
  WHEN [dca4_HW Logical Device].[Model] LIKE '1706M8U' THEN 'ThinkPad X60'
  WHEN [dca4_HW Logical Device].[Model] LIKE '7673%' THEN 'ThinkPad X61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '767474U' THEN 'ThinkPad X61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '7675K2U' THEN 'ThinkPad X61'
  WHEN [dca4_HW Logical Device].[Model] LIKE '766734U' THEN 'ThinkPad X61s'
  WHEN [dca4_HW Logical Device].[Model] LIKE '415711U' THEN 'ThinkStation S20'
 ELSE [dca4_HW Logical Device].[Model] END AS CorrectModel,  
   [dca8_HW Computer System].[Device ID] AS [Device ID1],
   [dca8_HW Computer System].[Identifying Number],
   usr.[User],
   [dca5_AeX AC Identification].[Domain],
   [dca5_AeX AC Identification].[Name] AS [Name1],
   [dca5_AeX AC Identification].[OS Name],
   [dca6_OS Operating System].[Install Date],
   tcp.[MAC Address],
   tcp.[IP Address],
   [dca9_HW Chassis].[Chassis Package Type],
   CASE
        WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '1' THEN 'Other'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '2' THEN 'Laptop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '3' THEN 'Desktop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '4' THEN 'Desktop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '6' THEN 'Desktop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '7' THEN 'Desktop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '8' THEN 'Laptop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '9' THEN 'Laptop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '10' THEN 'Laptop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '12' THEN 'Laptop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '13' THEN 'Desktop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '15' THEN 'Desktop'
  WHEN [dca9_HW Chassis].[Chassis Package Type] LIKE '21' THEN 'Laptop'
                                WHEN [dca9_HW Chassis].[Chassis Package Type] IS NULL THEN 'Desktop'

        END AS SMSUBTYPE
       
   
FROM
   [vComputer] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_HW_Logical_Device] AS [dca4_HW Logical Device]
       ON ([vri2_Computer].[Guid] = [dca4_HW Logical Device].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca5_AeX AC Identification]
       ON ([vri2_Computer].[Guid] = [dca5_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_OS_Operating_System] AS [dca6_OS Operating System]
       ON ([vri2_Computer].[Guid] = [dca6_OS Operating System].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Computer_System] AS [dca8_HW Computer System]
       ON ([vri2_Computer].[Guid] = [dca8_HW Computer System].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Chassis] AS [dca9_HW Chassis]
       ON ([vri2_Computer].[Guid] = [dca9_HW Chassis].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS tcp ON tcp._ResourceGuid = [vri2_Computer].Guid AND tcp._id =
                          (SELECT     TOP (1) _id
                            FROM          dbo.Inv_AeX_AC_TCPIP AS t
                            WHERE      ([vri2_Computer].Guid = _ResourceGuid) AND ([IP Address] NOT IN ('127.0.0.1', '0.0.0.0', '')) AND (NOT ([IP Address] IS NULL))
                            ORDER BY Routable DESC, DHCPEnabled DESC)   
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS usr ON usr._ResourceGuid = [vri2_Computer].Guid AND usr._id =
                          (SELECT     TOP (1) _id
                            FROM          dbo.Inv_AeX_AC_Primary_User AS p
                            WHERE      ([vri2_Computer].Guid = _ResourceGuid)
                            ORDER BY _id DESC)

WHERE
      [dca4_HW Logical Device].[Device ID] = [dca8_HW Computer System].[Device ID]
      
      AND ([dca9_HW Chassis].[Chassis Package Type] = '3'
        OR[dca9_HW Chassis].[Chassis Package Type] = '4'
        OR[dca9_HW Chassis].[Chassis Package Type] = '6'
        OR[dca9_HW Chassis].[Chassis Package Type] = '7'
        OR[dca9_HW Chassis].[Chassis Package Type] = '8'
        OR[dca9_HW Chassis].[Chassis Package Type] = '9'
        OR[dca9_HW Chassis].[Chassis Package Type] = '10'
        OR[dca9_HW Chassis].[Chassis Package Type] = '15');

Attachment(s)

Export.xlsx   701 KB 1 version

CASE

WHEN [dca5_AeX AC Identification].[Hardware Serial Number] LIKE '%' THEN 'Computer'

END AS SMTYPE,

Can you replace this with this text:

'Computer' as 'SMTYPE'

It seems as though you just need this text to appear in a column, but don't care about the Hardware Serial Number value.  I don't think this has anything to do with your WHERE clause but it's worth a guess.

Keep in mind DISTINCT is going to give you DISTINCT everything.  If you have Bob from Ohio, Sue from Florida, Bob from Minnesota, and Eric from Oregon, SELECT DISTINCT FirstName will give you the results Bob, Sue, Eric.  If you SELECT DISTINCT FirstName, State you're going to get Bob Ohio, Sue Florida, Bob Minnesota, Eric Oregon.  You're selecting unique results from ALL columns.  I could further extend this for them to have careers, and SELECT DISTINCT FirstName, State, Career you're going to get multiple Bobs from Minnesota since one is a plumber, one is a electrician, and one is a carpenter.  (I guess you could call them builders.)

Your fix depends on what you're using this report for.  Personally, I'd just drop MAC Address.  This report is so massive I wonder what's needed on it.  If it is needed, I'd add Inv_AeX_AC_TCPIP.Device so that it makes sense to the user why they're seeing duplicates for a single computer.

If you have computers with NULL Serial Numbers, have you tried changing your WHERE to 'IS NULL' instead of 'IS NOT NULL'?  If these same NULL computers are listed, then reversing to IS NOT NULL should eliminate them.  If different computers are listed, you know you may have something else going on -- like a blank value being reported as NULL, and you could try WHERE [Hardware Serial Number] <> ''

Does any of this help?  It's tough to tell without having my hands data in a large environment this week.

You could use MAC Address from vComputer instead, which will only have one result per GUID, in contrast to Inv AeX TCPIP which can have multiple records per GUID.

Thank you for the response

• I've taken DISTINCT out of my orginal post.  I didn't mean to include it. 
• I can't remove 'END' because it's part of the CASE statement.  You are correct.  This is just to populate another column and doesn't have anything to do with goals I'm trying to achieve in this thread.
• If I change the WHERE clause to include 'IS NULL' it responds with the results you might expect and gives output for rows that do not contain a serial number.  I don't understand why it will not do the reverse.
• I like your MAC Address idea so i changed my FROM statement to read against vComputer as opposed to vRM_Computer_Item.  This should effectively do the same thing right?  Unfortunatley, it's producing the same results.

FROM
   [vComputer] AS [vri2_Computer]  ...
     

Thank you again for your help!

You need to change your SELECT statement:

[dca3_AeX AC TCPIP].[MAC Address],
to
[vComputer].[MAC Address]

Furthermore, you should be able to replace the entire CASE/<blah>/END AS 'SMTYPE' portion with 'Computer' as 'SMTYPE'.  This defines a column SMTYPE with value Computer.  (This is also handy if you have multiple NS databases, because when you merge queries you can do stuff like 'NS6' as 'Source' in one query, 'NS7' as 'Source' in the other query, then know what environment you're talking about.)

Do these two changes make a difference?
 

why wouldnt you use vcomputer ? It really looks like you are recreating vcomputer, it has the serial number in it. Also Vcomputer only has 1 MAC in it. I would not use TCPIP at all as it will create duplicate machines. Vcomputer already does a top 1 from tcip for you

vasset has the machine type as computer or virtual machine type already

also I would create a view for your model number to Name to clean up your queary

also why do you want to remove 60 machines with incomplete inventory?

You are getting the same results because you are still joining to Inv_AeX_AC_TCPIP for the subnet mask. vComputer pulls it's information from Inv_AeX_AC_TCPIP as well. The reason it has only one row per computer is because in the join it includes a subquery that limits the results to 1 row per _ResourceGuid. You are going to have to do something similar if you want the subnet mask too. Here is the join that vComputer uses:

LEFT OUTER JOIN dbo.[Inv_AeX_AC_TCPIP] tcp
     ON tcp.[_ResourceGuid] = r.[Guid]
     AND tcp.[_id] =     (
          SELECT TOP 1 t.[_id]
          FROM [Inv_AeX_AC_TCPIP] t
          WHERE r.[Guid]= t.[_ResourceGuid]
               AND t.[IP Address] NOT IN ('127.0.0.1', '0.0.0.0', '')
               AND NOT t.[IP Address] IS NULL
          ORDER BY t.Routable DESC, t.DHCPEnabled DESC
          )

I've updated my Select statement (above) based on the feedback you all have provided.  I've also atached the report results as well (export.xls).  I can't THANK YOU enough.  I'm almost there.  I still have 53 NULL Serials and just a few duplicates. I have developed other ways to deal with the NULL values on the SQL side so I'm much more concerned with the remaining duplicate values.

mclemson:

• I replaced my Case statement with 'Computer' as 'SMTYPE'

Tyler:

• Great idea, I incorporated some of that into my FROM statement above and it removed many duplicates

sdmayhew:

• Yes, in many ways I'm re-creating vComputer.  When I try to alter vComputer for my purposes the report continually errors out.  The log provides very ambiguous errors.  I think it has to do with the amount of views I'm pulling from when doing that.
• It's not so much that I wouldn't want to report on them just becuase they have incomplete inventory.  This data is being used to import into another database to be used by another application.  The serial number is the primary key in the other database, and therefore cannot be NULL.

Thank you all again for your help.  Like I mentioned... very close.  Appreciate any help you can offer!

Gary

Regarding DISTINCT, it will remove duplicate rows where the values in all the columns of the result set are the same. Usually these occur because in one or more tables you are joining to there are multiple rows per the identifier you are joining on (Guid/ResourceGuid) that have one or more columns where the values are different. Often these differing values are in columns that you aren't even querying, and so your results will show rows where everything is duplicate and you are left scratching your head wondering where the extra rows are coming from.

Use DISTINCT and it will remove those rows.

As for NULL serial numbers. You are pulling these from the Inv_AeX_AC_Identification table using a LEFT OUTER JOIN. Furthermore, your WHERE clause is not limiting the results to the serial number not being NULL. This means that if there is no data in that table for a particular resource, then any columns from the outer joined table for that resource will have NULL values.

If you don't want NULL values then you will either have to replace the NULL values with other data (using the ISNULL() function), or you need to filter the results by either making your JOIN an INNER JOIN or adding to the WHERE clause something like:

AND [dca5_AeX AC Identification].[Hardware Serial Number] IS NOT NULL

You will get slightly better performance by changing the LEFT OUTER JOIN to an INNER JOIN instead though (it only scans the index on the guid column for the JOIN). However, looking at the table definition, it does seem to allow NULL values in that column. So if there is data in that table, but the serial number is NULL, then it will still show up as NULL in your results. If this turns out to be the case, then it is better to use the filter in the where clause.

Hope this helps.