[Modus] sql scripts

[Jim Barstow]

Title: Message

Here are the top10 views, adjusted to work with Modus3 (and hopefully fixed a bug introduced when modus3 came out with the names of the sieve filters that are part of the scaBase)   Should be a total of 4 database views.. I have tailored them a little bit to match my specific needs, but some tweaking and you can make them do whatever.   Sr. Network Engineer Diversified Solutions and Services, Inc. 22645 Canal Road, Suite B Orange Beach, AL 36561 (251)980-8968 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Suneel Jhangiani Sent: Tuesday, February 03, 2004 7:35 PM To: [EMAIL PROTECTED] Subject: [Modus] sql scripts Do you have Modus set to log Errors etc.. to SQL as well?   If so it can be scripted quite easily. If not routines to parse the text logfiles would be needs. I know someone else on this list wrote a small utility in Delphi called ‘EmailAnal’ for VopMail which did work under Modus v2, I’m not sure if anyone has tried it with Modus v3 or has a link.     Regards,   Suneel Jhangiani Inter-Computer Technology Ltd.   Modus3 Bug Buster Co-MVP There is an old saying that if a million monkeys typed on a million keyboards for a million years, eventually all the works of Shakespeare would be produced. Now, thanks to Usenet, we know this is not true.     From: Michael B. Smith [mailto:[EMAIL PROTECTED] Sent: 04 February 2004 01:22 To: [EMAIL PROTECTED] Subject: [Modus] sql scripts   I guess I'm looking for, in general:   1) Ways to give me the SIGNIFICANT errors out of the ERR log (I've gotten some traction using awk/sed/grep, but those are painful and remind me of college 20 years ago) and SVR3 and BSD4.1 (yes, I'm that old). Maybe I should learn perl or what's-that-other-new-fangled scripting language? Python? Boa? Who knows? ;-)   2) Daily totals that actually mean something from OPR, SIEVE, and VIRUS logs   3) User reports and counts that mean something from the database.   TOp 10's sound like a good place to start. They would at least give me some ideas.   I gotta admit, everything I start out with seems to begin "select trim(emailaddress)..." or VERY similar

<<image001.gif>>

��SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE VIEW dbo.NumOfMsgsByDate

AS

SELECT     TOP 100 PERCENT 
COUNT(CONVERT(varchar(10), StoreDate, 
101)) AS NumOfMsgs, CONVERT(varchar(10), 
StoreDate, 101) AS [Date]

FROM         dbo.tblMsgCatalog

GROUP BY CONVERT(varchar(10), StoreDate, 
101)

ORDER BY CONVERT(varchar(10), StoreDate, 
101) DESC



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE VIEW dbo.PercentageBySieveCategory

AS

SELECT TOP 100 PERCENT 

       CONVERT(DECIMAL(5,2),

       
CONVERT(DECIMAL,(COUNT(LEFT(RIGHT(Cause,LEN(Cause)-1),
 CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1))*100))/

       CONVERT(DECIMAL,(

              SELECT COUNT(*) FROM 
dbo.tblMsgCatalog WHERE MsgType = 2))) as 
Percentage, 

              CASE

              WHEN LOWER(LEFT(RIGHT(Cause, 
LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1)) = 'custom'

              THEN RIGHT(RIGHT(RIGHT(Cause, 
LEN(Cause)-2), LEN(RIGHT(Cause, 
LEN(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2))), LEN(RIGHT(RIGHT(Cause, 
Len(Cause)-2), LEN(RIGHT(Cause, 
Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2)))) - CHARINDEX('\', 
RIGHT(RIGHT(Cause, Len(Cause)-2), 
LEN(RIGHT(Cause, 
Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2))))) 

              WHEN LOWER(LEFT(  RIGHT(Cause, 
LEN(Cause)-2)  , CHARINDEX('\',  
RIGHT(Cause, LEN(Cause)-2)  )-1)) = 
'scabase'

              THEN LOWER(LEFT(  RIGHT(Cause, 
LEN(Cause)-(2+CHARINDEX('\', 
RIGHT(Cause,LEN(Cause)-2)))), 
CHARINDEX('\', 
RIGHT(Cause,LEN(Cause)-10))-1))

              ELSE LEFT(RIGHT(Cause, 
LEN(Cause)-1),CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1)

              END as Category

       FROM dbo.tblMsgCatalog 

       WHERE MsgType = 2 

       GROUP BY CASE 

              WHEN LOWER(LEFT(RIGHT(Cause, 
LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1)) = 'custom'

              THEN RIGHT(RIGHT(RIGHT(Cause, 
LEN(Cause)-2), LEN(RIGHT(Cause, 
LEN(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2))), LEN(RIGHT(RIGHT(Cause, 
Len(Cause)-2), LEN(RIGHT(Cause, 
Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2)))) - CHARINDEX('\', 
RIGHT(RIGHT(Cause, Len(Cause)-2), 
LEN(RIGHT(Cause, 
Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2))))) 

              WHEN LOWER(LEFT(  RIGHT(Cause, 
LEN(Cause)-2)  , CHARINDEX('\',  
RIGHT(Cause, LEN(Cause)-2)  )-1)) = 
'scabase'

              THEN LOWER(LEFT(  RIGHT(Cause, 
LEN(Cause)-(2+CHARINDEX('\', 
RIGHT(Cause,LEN(Cause)-2)))), 
CHARINDEX('\', 
RIGHT(Cause,LEN(Cause)-10))-1))

              ELSE LEFT(RIGHT(Cause, 
LEN(Cause)-1),CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1)

              END

       ORDER BY CASE 

              WHEN LOWER(LEFT(RIGHT(Cause, 
LEN(Cause)-1), CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1)) = 'custom'

              THEN RIGHT(RIGHT(RIGHT(Cause, 
LEN(Cause)-2), LEN(RIGHT(Cause, 
LEN(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2))), LEN(RIGHT(RIGHT(Cause, 
Len(Cause)-2), LEN(RIGHT(Cause, 
Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2)))) - CHARINDEX('\', 
RIGHT(RIGHT(Cause, Len(Cause)-2), 
LEN(RIGHT(Cause, 
Len(Cause)-2))-CHARINDEX('\', RIGHT(Cause, 
Len(Cause)-2))))) 

              WHEN LOWER(LEFT(  RIGHT(Cause, 
LEN(Cause)-2)  , CHARINDEX('\',  
RIGHT(Cause, LEN(Cause)-2)  )-1)) = 
'scabase'

              THEN LOWER(LEFT(  RIGHT(Cause, 
LEN(Cause)-(2+CHARINDEX('\', 
RIGHT(Cause,LEN(Cause)-2)))), 
CHARINDEX('\', 
RIGHT(Cause,LEN(Cause)-10))-1))

              ELSE LEFT(RIGHT(Cause, 
LEN(Cause)-1),CHARINDEX('\',RIGHT(Cause, 
LEN(Cause)-1))-1)

              END



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE VIEW dbo.Top10Filters

AS

SELECT     TOP 10 COUNT(Cause) AS 
FilterCount, CASE 

                     WHEN LEFT(Cause,2) = '\\' 

                     THEN 
RIGHT(Cause,(LEN(Cause)-9)) 

                     ELSE Cause 

                     END AS Cause

FROM         dbo.tblMsgCatalog

GROUP BY Cause

ORDER BY FilterCount DESC



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE VIEW 
dbo.Top10EmailAddressesBlockingMail

AS

SELECT     TOP 10 COUNT(EMailAddress) AS 
MessageCount, EMailAddress

FROM         dbo.tblRecipients

GROUP BY EMailAddress

ORDER BY MessageCount DESC



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO