July 16, 2009 at 9:23 am
Hi,
I am pretty new to sql in terms of updating or deleting databases. we currently use Symatnec antivirus and are noticing an increase in dupliacte computer names but they have different unique identifiers. what i am trying to do is create a statement that find dupliacte names and remove them from the table. Here is what i have written.
DELETE from SEM_COMPUTER
WHERE EXISTS
(SELECT COMPUTER_NAME, COMPUTERNAME AS 'COL2', COUNT(*) AS 'DUPS'
FROM DBO.SEM_COMPUTER
GROUP BY COMPUTER_NAME
HAVING (COUNT(*) > 1))
I ran this on a dupliacte table in certifiaction and it removed all items (ACK!). Can someone help me? Thanks in advance
Carl
July 16, 2009 at 10:46 am
For a meaningful reply / suggestions please post the information outlined in my signature block, that is a create table statement, some sample data, etc...
Congratulations on running your statement on a copy of the table, another method I have found valuable when updatting or deleting data is to run the T-SQL as a SELECT statement and review the results before changing it to perform an update or delete.
July 16, 2009 at 11:40 am
desktopteam (7/16/2009)
Hi,I am pretty new to sql in terms of updating or deleting databases. we currently use Symatnec antivirus and are noticing an increase in dupliacte computer names but they have different unique identifiers. what i am trying to do is create a statement that find dupliacte names and remove them from the table. Here is what i have written.
DELETE from SEM_COMPUTER
WHERE EXISTS
(SELECT COMPUTER_NAME, COMPUTERNAME AS 'COL2', COUNT(*) AS 'DUPS'
FROM DBO.SEM_COMPUTER
GROUP BY COMPUTER_NAME
HAVING (COUNT(*) > 1))
I ran this on a duplicate table in certifiaction and it removed all items (ACK!). Can someone help me? Thanks in advance
Carl
Yep... can do. But, since this is SQL Server 2000, there're lot's of easy tools missing compared to 2k5. Instead, we need to know just what BitBucket asked above. We need to know what the primary key for the table is, at the very least. It would be best if you provided the CREATE Table statement along with the indexes.
The reason why it sounds like we're running you through the wringer is because deleting dupes without a PK or something to distinguish the rows is a real PITA in SQL Server 2000. If we can find out what we need to know about your data, there's also a couple of easy ways.
Looking forward to your info...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 11:56 am
desktopteam (7/16/2009)
Hi,I am pretty new to sql in terms of updating or deleting databases. we currently use Symatnec antivirus and are noticing an increase in dupliacte computer names but they have different unique identifiers. what i am trying to do is create a statement that find dupliacte names and remove them from the table. Here is what i have written.
DELETE from SEM_COMPUTER
WHERE EXISTS
(SELECT COMPUTER_NAME, COMPUTERNAME AS 'COL2', COUNT(*) AS 'DUPS'
FROM DBO.SEM_COMPUTER
GROUP BY COMPUTER_NAME
HAVING (COUNT(*) > 1))
I ran this on a dupliacte table in certifiaction and it removed all items (ACK!). Can someone help me? Thanks in advance
Carl
Carl, as BitBucket stated some idea of what your tables are showing and some sample data will go a long ways towards helping us help you... In the meantime though, is this in the Symantec AV's enterprise management database? If so, I might suggest that you contact Symantec about any issues you are seeing with their database, rather than cleaning it up manually. They may have some good reasons for storing multiple copies of the same machine, perhaps different products reporting in like AV, Anti Spam, firewall, IDS etc... We use McAfee's solutions here and I currently have 3 distinct McAfee products install on my machine all pointing back to the same management database....
-Luke.
July 16, 2009 at 12:31 pm
Thanks everyone for your help.
I included a couple of screen shots of data. Screen 1 is regular output fromt the table the other picture is the result of a computer listed twice but with diffrerent unique ID's problaly from reloading the comptuer etc..
This Symantec 11 enterprise database to which all data is stored. I went to their forums and they were not much help. I haven't called premier support yet as my ego hasn't bruised enough to make the call 🙂
by the way i am running SQL 2008 enterprise
the table has many many columns so the snapshot is of items that may be unique (except domain_id)
Carl
July 16, 2009 at 12:41 pm
OK so it is the Symantec Enterprise database... Are you seeing these duplicate computernames in the Symantec Management GUI tools or just in the database? You may want to check into what their support agreement actually says as if you go monkeying with their database you may find yourself with a bruised ego and no support. I've been down this road with vendors before and it can get ugly.
The thing I'd want to know more than just how to get rid of the dups is why they are there in the first place. When did the dups show up, was it after a recent patch or something? if it has the same name but a different GUID that could be very much intentional for some reason on Symantec's part. Did you just install a new part of their suite or anything of the like?
Also for help on the SQL bit, you really need to read the first link in either Jeff's or Bit Bucket's signature. It will give you a better understanding of what we might need to help you.
Cheers,
-Luke.
July 16, 2009 at 1:30 pm
I agreee didn't want to mess with any of the standard tables they may have. It's something that has been appering over time, with over 15000 computers in our environment, computer reloads/replacements aren't common. i will call them in hopes of setting type of job to run weekley or something as going row by row would really really suck.
July 16, 2009 at 1:42 pm
OK so it's caused by machine's being reloaded and such and called the same thing?
They may already have a utility to clean up those types of duplicates. Additionally you'd want to check with them to see if there is an install switch you can use to overcome these issues in the future. I'm not certain about Symantec as I've no experience with their product line for the last 5-6 years, but I am aware that Mcafee has a switch that will remove the old object and/or not create a new object or something of the sort so that you don't end up with loads of dups in the database. I'd be rather surprised if Symantec doesn't also have something of the sort.
-Luke.
July 17, 2009 at 4:51 am
desktopteam (7/16/2009)
I agreee didn't want to mess with any of the standard tables they may have. It's something that has been appering over time, with over 15000 computers in our environment, computer reloads/replacements aren't common. i will call them in hopes of setting type of job to run weekley or something as going row by row would really really suck.
Dupe checks and removal of duplicate rows aren't really that hard if you'll provide the table creation script and a bit of data in the format that BitBucket and I have asked for. Bitmap screen shots really don't make life easy on us at all. Help us help you... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply