Symantec Management Platform (Notification Server)

I am what you would consider an SQL beginner, I've only been dealing with it in depth for about 3 - 4 months. I am trying to create a report that will give a certain list of information about a list of machines. The issue I'm running into is that when I run the query that I have below, I will get 4 lines for each of the seperate items from vComputer. From what I can tell the issue is coming from my Resource Association and Item joins, because the duplicate lines seem to only change in those two columes. The two joins are looking for Asset Status and Machine Type. In the report these have to be in text form and not GUID form, which is why I am joining it to the item table. In the results I am seeing one line that has neither status nor machine type, another line that only has the status, another line that has only the machine type and the last line will display both. I get NULL in those columns otherwise. Does anyone have any ideas what I can do to remedy this issue?

Thanks,

Damon

select Distinct IGD.[Display Name] as [Owner],

vCom.Name,

IID.[System Number] as [Asset Tag],

ASItem.Name as [Assets Status],

IM.Manufacturer,

IM.Model,

CTItem.Name as [Computer Type],

ICCD.[Cost Center Code] as [Cost Center Owner],

IGD.[Department] as [Owner Department]

from vcomputer vCom

Left Outer Join Inv_Ownership_Details [IOD] On IOD._ResourceGuid = vCom.Guid

Left Outer Join Inv_Cost_Center_Ownership [ICCO] On ICCO._ResourceGuid = vCom.Guid

Left Outer Join Inv_Cost_Center_Details [ICCD] ON ICCD._ResourceGuid = ICCO.Owner

Left Outer Join Inv_Global_User_General_Details [IGD] On IGD._ResourceGuid = IOD.Owner

Left Outer Join Inv_Identity [IID] On IID._ResourceGuid = vCom.Guid

Left Outer Join Inv_Manufacturer [IM] On IM._ResourceGuid = vCom.Guid

Left Outer Join ResourceAssociation [ASRA] On ASRA.ParentResourceGuid = vCom.Guid

Left Outer Join Item [ASItem] On ASItem.Guid = ASRA.ChildResourceGuid and ASRA.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'

Left Outer Join ResourceAssociation [CTRA] On CTRA.ParentResourceGuid = vCom.Guid

Left Outer Join Item [CTItem] On CTItem.Guid = CTRA.ChildResourceGuid and CTRA.ResourceAssociationTypeGuid = '74C9AD4A-E384-477E-BD06-132A3368E58D'

Order By vCom.Name asc

Ok, without running the SQL query, I can tell you one of the main reasons why you would get duplicates. It may have to do with the associations, but it is mostly because of the columns you are selecting.

The 'distinct', in your select statement is not for the column you are selecting right after it. The 'distinct' is for all the columns. Basically, it is saying row 1 should not read (columns left to right) like any other row in the results. So, if row 1 column A results is a 1 and row 1 column B result is 2, then no other row can have a column A result as 1 AND a column B result as 2.

In your query, you are selecting 9 columns. So, all 9 columns in row 1 are unique and no other row in the result set is the same as row 1.

Make sense?

If you look at your duplicates, all 9 columns, for each duplicate row, will have at least 1 thing different.

I think I understand what you are saying and it makes sense, but that is what is confusing me about this. I have compared the rows of my results with the select distinct and there are no other differences other than what I listed in the last post. I have tried changing my Resource Association to Item joins to regular joins as opposed to Left Joins and that gives me one line per machine but I am missing any machine that doesn't have a status or a machine type associated with it.

Does the built-in report 'Assets by Owner's Department' (at Reports > Service and Asset Management > Assets) work for you?  If not, you can clone it and edit the SQL to include the information you need.

But this already includes Owner, Department, Name of device, Asset Type, Status, Manufacturer, Model, Serial and more.

That report is pretty much exactly what I need and cloning it is probably what I should have done to start. I ended up being able to get it working by just using the Join as opposed to the Left Join. I determined that I wasn't missing any data that I needed, so using that Join wasn't going to be an issue.