Video Screencast Help

Help needed in removing duplicates from report - NS 6

Created: 04 Feb 2013 • Updated: 13 Feb 2013 | 5 comments
This issue has been solved. See solution.

Hi,

I did try to search on forums to see if i can find a way to remove duplicates from the report that i am generating in my altiris ns 6, as i'm not that proficient in SQL, wanted a minimal way to edit my exisitng query to see if it can be done..but was unable to find much in the forums... 

here is sql query that i'm running - 

 

SELECT distinct T1.[_ResourceGuid] AS 'GUID', T1.[Name] AS 'Computer Name', T3.[Serial number] AS 'Serial number', T1.[Last Logon User] AS 'User', T1.[OS Name] AS 'OS', T2.[Name] AS 'Software', T3.[Status] as 'Status', T4.[Exchange Display Name] as 'Display Name', T2.[Version] as 'Version'        
 FROM ([Inv_AeX_AC_Identification] T1          
 JOIN [Inv_AeX_OS_Add_Remove_Programs] T2          
 ON T1.[_ResourceGuid] = T2.[_ResourceGuid]          
 JOIN [vAsset] T3          
 on  T1.[_ResourceGuid] = T3.[_ResourceGuid]          
 JOIN [Inv_AeX_EU_Contact_Detail] T4        
 on T1.[_ResourceGuid] = T4.[_ResourceGuid]       
  )          
   where ((T2.[name] like '%Java%'   
and T2.[name] not like 'Java Auto Updater'   
    
  )          
  and T2.[Version] not like '')  
--and T3.[Serial number] like '456ZF3J'          
 ORDER BY T1.[Name] ASC
 
i went through couple of posts where they spoke about using "distinct" in a specfic way, so that i can get unique results, i've learnt that the above statement would give me multiple results with reference to "GUID, Serial Number, User etc"
 
http://www.symantec.com/connect/forums/sql-help-reporting-unique-values-and-removing-duplicates
 
http://www.symantec.com/connect/forums/duplicates-sql-report
 
would appreciate if there is some way i can get rid of duplicates without much edition...
 
Thanks,
vikram
 
 
 
 

Comments 5 CommentsJump to latest comment

mclemson's picture

What are you trying to report on?  I can't tell from your post what is duplicated.  For example, suppose I had a table of favorite foods:

Mike....cheeseburger
Mike....pizza
Vikram...palak paneer
Mike...pizza

If you write this query:
SELECT DISTINCT FirstName FROM Table

You will get these results:
Mike
Vikram

If you write this query, however:
SELECT FirstName, FaveFood FROM Table

You will get these results:
Mike....cheeseburger
Mike....pizza
Vikram...palak paneer
Mike....pizza

If you add DISTINCT, the duplicate result of "Mike pizza" is removed.
SELECT DISTINCT FirstName, FaveFood FROM Table
Mike....cheeseburger
Mike....pizza
Vikram...palak paneer

Mike is still listed twice because there's the combination of "Mike pizza" and "Mike cheeseburger"

 

So the question to ask is: What are you trying to find with your query, and what sort of result are you wanting to ignore?  What will you do with the results of your query once you refine it?  Do you want no more than a single row per unique computer, but you're receiving multiple?  If so, compare columns of results for any computer listed multiple times and find out what column is different.  You can then modify your query to ignore the value you didn't want or drop the column entirely.  Most likely, you're getting duplicates because of multiple values in Inv_AeX_OS_Add_Remove_Programs.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

vikod's picture

Hi Mike,

Thank you for your reply, and the example (liked the Palak Paneer touch :)) gave a much better overview of the distinct command :)

I was trying to extract a report of all Java Versions installed on computers in my network, the duplicates that got generated in the end result ( i could figure out) was due to this table

"JOIN [Inv_AeX_EU_Contact_Detail] T4 on T1.[_ResourceGuid] = T4.[_ResourceGuid] "

the report was populating multiple entries of the same machine due to multiple exchange profiles exisiting on the machine - which was showing up under the "Display Name" column.

"Inv_AeX_OS_Add_Remove_Programs" table did populate multiple entries which were valid cos of different versions of Java installed on the computer.

Got rid of this table though - Inv_AeX_EU_Contact_Detail - reason why i had it in the first place was to populate Full Names of users also.

Not sure which table i can use to populate names without any duplicates..

Thanks again,

Cheers,
Vikram

mclemson's picture

I don't have an NS6 environment to play with, but if you want to know the Full Name of the Last Logon User, join Inv_AeX_EU_Contact_Detail on Inv_AeX_AC_Identification on a unique piece of data they share.  If Last Logon User is the network ID and network ID exists in Inv_AeX_EU_Contact_Detail, you could use this.  Right now you are joining Inv_AeX_EU_Contact_Detail based on ResourceGuid which will pull in any Full Name associated with the computer.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

SOLUTION
JoeVan's picture

I took your SQL and reformated / re-variablized a bit to make it easier to follow.  I would suggest pulling the full name like this:

SELECT DISTINCT Ident.[_ResourceGuid] AS 'GUID'

    , Ident.[Name] AS 'Computer Name'

    , Asset.[Serial number] AS 'Serial number'

    , Ident.[Last Logon User] AS 'User'

    , Ident.[OS Name] AS 'OS'

    , ARP.[Name] AS 'Software'

    , Asset.[Status] as 'Status'

    ,( SELECT TOP 1 Contact.[Exchange Display Name]

        FROM [Inv_AeX_EU_Contact_Detail] Contact

        WHERE Ident.[_ResourceGuid] = Contact.[_ResourceGuid]

            --AND Contact.[Exchange Common User Name] = Ident.[Last Logon User]

        ORDER BY Contact.[_id] DESC

    ) AS 'Display Name'

    , ARP.[Version] as 'Version'

FROM [Inv_AeX_AC_Identification] Ident

    JOIN [Inv_AeX_OS_Add_Remove_Programs] ARP ON Ident.[_ResourceGuid] = ARP.[_ResourceGuid]

    JOIN [vAsset] Asset on Ident.[_ResourceGuid] = Asset.[_ResourceGuid]

WHERE ((ARP.[name] like 'Java%'

    and ARP.[name] not like 'Java Auto Updater'

    ) and ARP.[Version] not like '')

ORDER BY Ident.[Name] ASC

 

The problem with this, as Mike mentions is that you may have multiple exchange profiles on a machine.  My query will eliminate dups but only take the most recent exchange profile name.  If one of the fields in the contact detail table matches the username format of the machine you can filter it something like I have in the commented line and that may be more accurate:

--AND Contact.[Exchange Common User Name] = Ident.[Last Logon User]

You will also get duplicate machine entries in the case that a machine has more than one Java version installed such as a 32-bit and 64-bit Java version installed, but I assume that is desireable. 

 

Joe VanHollebeke
Systems Engineer

vikod's picture

Thank you so much guys - getting my hands dirty with SQL is fun :), the guidance is much appreciated!

@Joe - Query did what i needed.

 

Cheers,

Vikram