By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

COUNT Duplicates using partial string

P: 1
I have a .sql script that works great for eliminating duplicate returned rows, except that there are some fields where the Message has a counter value in it like this:
The VMware VirtualCenter Agent service terminated unexpectedly. It has done this 328 time(s). ...
The VMware VirtualCenter Agent service terminated unexpectedly. It has done this 327 time(s). ...

So as you can see, it sees each as being unique. I would like to just take the first 35 or so characters of that field and use it to determine if it is a duplicate or not, along with the other fields that are there.

Here is the script:
==============================
SELECT
ComputerName,
EventType As Err#,
SourceName AS Name,
EventId,
count(*) AS Times,
message
INTO %FileOut%
FROM \\%SRVname%\system
WHERE SUB (to_int(TimeGenerated), to_int(System_TimeStamp())) > -86400
AND sourcename not in
('Print' ;
'MRxSmb' ;
'w32time' ;
'TermServDevices') AND
EventType <= 2
GROUP BY ComputerName,Err#,EventID,Name,Message
HAVING Times >= 1
ORDER BY Times desc
======================================

Any ideas on how to do this?
Mar 6 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.