MERO

How would you rate MERO?

Excellent...a Must Have!
8
42%
Helpful, useful, worth it.
9
47%
I could take it or leave it.
0
No votes
I tried it, but didn't like it.
1
5%
It's useless...don't bother.
1
5%
 
Total votes: 19
ccgeek
Posts: 24
Joined: Tue Feb 07, 2006 2:28 am
Location: Georgia, USA
Contact:

ME Ent SQL Direct Access

Postby ccgeek » Sat Feb 17, 2007 9:48 am

I took the original lovely mero code and rewrote it to directly access the SMTP_ACCESS table in ME Ent. This code has no logging and no screen interaction; however, the rest of the business logic is untouched.

The only thing a person would have to do is create a new table in the ME database called mero, and change the connection string. Other than that, all the old rules apply.

To add the new table to your ME database use the following code:

Code: Select all



CREATE TABLE mero(
   id int IDENTITY(1,1) NOT NULL,
   mode int NOT NULL,
   addressmask varchar(15) NOT NULL,
   status int NOT NULL,
   account varchar(50) NOT NULL,
   accessright varchar(50)NOT NULL,
   threat int NOT NULL,
   date_mod smalldatetime NOT NULL,
   date_create smalldatetime NOT NULL,
   CONSTRAINT [PK_mero] PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY]
)






This is the modified mero VBS script:

Code: Select all


Option Explicit
Private Const lMaxDel = 100
Dim Conn 'As Connection
Dim rs 'As Recordset
Dim i 'As Long
Set Conn = WScript.CreateObject("ADODB.Connection")
Set rs = WScript.CreateObject("ADODB.RecordSet")
   Conn.Open "Provider=SQLOLEDB.1;Server=your_server;Database=me_database;Integrated Security=SSPI;"
      Conn.Execute "UPDATE m SET m.threat = ABS(m.threat - (CASE WHEN m.threat < 1 THEN 1 ELSE 0 END)), m.date_mod = GETDATE() FROM mero m JOIN smtp_access s ON s.addressmask = m.addressmask"
      Conn.Execute "INSERT INTO mero (mode, addressmask, status, account, accessright, threat, date_mod, date_create) SELECT mode, addressmask, status, account, accessright, 1, GETDATE(), GETDATE() FROM smtp_access WHERE addressmask NOT IN (SELECT addressmask FROM mero)"
      Conn.Execute "DELETE FROM mero WHERE date_mod < DateAdd(d, -90, GETDATE()) AND threat < 0"
      Conn.Execute "UPDATE mero SET threat = (CASE WHEN threat < 5 THEN 0 - threat ELSE threat END), date_mod = GETDATE() WHERE DateDiff(d, DateAdd(d, 1, GETDATE()), date_create) % 60 = 0 AND threat < 5"
      rs.Open "SELECT COUNT(id) AS rcount FROM mero WHERE threat > 0 AND threat < 5", Conn, 0, 1
         i = CLng(rs("rcount") * 0.9)
      rs.Close
      If lMaxDel < i Then i = lMaxDel
      Conn.Execute "DELETE FROM smtp_access"
      Conn.Execute "INSERT INTO smtp_access (mode, addressmask, status, account, accessright) SELECT mode, addressmask, status, account, accessright FROM (SELECT addressmask, mode, account, accessright, status, threat, date_mod FROM mero WHERE threat > 4 UNION ALL SELECT TOP " & i & " addressmask, mode, account, accessright, status, threat, date_mod FROM mero WHERE threat > 0 AND threat < 5) AS temp ORDER BY threat DESC, date_mod, addressmask"
      Conn.Execute "UPDATE mero SET threat = (CASE WHEN threat < 0 THEN threat ELSE 0 - threat END) WHERE addressmask NOT IN (SELECT addressmask FROM smtp_access)"
   Conn.Close
Set rs = Nothing
Set Conn = Nothing

Last edited by ccgeek on Mon Feb 19, 2007 6:24 am, edited 1 time in total.

ccgeek
Posts: 24
Joined: Tue Feb 07, 2006 2:28 am
Location: Georgia, USA
Contact:

ME Ent SQL Direct Access

Postby ccgeek » Mon Feb 19, 2007 6:08 am

This is a further step into madness; a MS SQL Server Mero Stored Procedure.

This code uses the same SQL database that ME uses. Simply execute the following two statements. They will create the mero table and the stored procedure. Once these are created, setup your SQL agent to run the procedure at the schedule of your choosing. It seems the original author chose 4 times a day as a good number.

Again, the original Mero logic has not been altered; it runs just like the VB script with the only exceptions of logging, user interaction. The only other change that should be noted is the rounding. In my tests it seems that SQL rounds different than VB Script.

Any questions, comments, or criticisms are welcome.

Create Database Table:

Code: Select all


CREATE TABLE mero(
   id int IDENTITY(1,1) NOT NULL,
   mode int NOT NULL,
   addressmask varchar(15) NOT NULL,
   status int NOT NULL,
   account varchar(50) NOT NULL,
   accessright varchar(50)NOT NULL,
   threat int NOT NULL,
   date_mod smalldatetime NOT NULL,
   date_create smalldatetime NOT NULL,
   CONSTRAINT [PK_mero] PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY]
)




Create Stored Proc:

Code: Select all


CREATE PROC proc_mero
AS
DECLARE @top INT
DECLARE @max INT
SET @max = 100
UPDATE m SET m.threat = ABS(m.threat - (CASE WHEN m.threat < 1 THEN 1 ELSE 0 END)), m.date_mod = GETDATE() FROM mero m JOIN smtp_access s ON s.addressmask = m.addressmask
INSERT INTO mero (mode, addressmask, status, account, accessright, threat, date_mod, date_create) SELECT mode, addressmask, status, account, accessright, 1, GETDATE(), GETDATE() FROM smtp_access WHERE addressmask NOT IN (SELECT addressmask FROM mero)
DELETE FROM mero WHERE date_mod < DateAdd(d, -90, GETDATE()) AND threat < 0
UPDATE mero SET threat = (CASE WHEN threat < 5 THEN 0 - threat ELSE threat END), date_mod = GETDATE() WHERE DateDiff(d, DateAdd(d, 1, GETDATE()), date_create) % 60 = 0 AND threat < 5
SET @top = (SELECT COUNT(id) * 0.9 AS rcount FROM mero WHERE threat > 0 AND threat < 5)
SET @top = (SELECT CASE WHEN @max < @top THEN @max ELSE @top END)
DELETE FROM smtp_access
EXECUTE ('INSERT INTO smtp_access (mode, addressmask, status, account, accessright) SELECT mode, addressmask, status, account, accessright FROM (SELECT addressmask, mode, account, accessright, status, threat, date_mod FROM mero WHERE threat > 4 UNION ALL SELECT TOP ' + @top + ' addressmask, mode, account, accessright, status, threat, date_mod FROM mero WHERE threat > 0 AND threat < 5) AS temp ORDER BY threat DESC, date_mod, addressmask')
UPDATE mero SET threat = (CASE WHEN threat < 0 THEN threat ELSE 0 - threat END) WHERE addressmask NOT IN (SELECT addressmask FROM smtp_access)



Marconius
Posts: 47
Joined: Wed Oct 11, 2006 6:55 pm

Postby Marconius » Tue May 22, 2007 5:04 pm

Does anyone have an index of what the threat levels represent in the mdb? I want to manually clear out some entries as I am reaching almost 27,000 and it seem like it is taking a lot longer to process the table. I have looked over quite a few of the logs as well and I don't see where MERO has ever dropped any entries. I don't think a good portion of the 27000 entries have reoffended in the last 90 days, but it doesn't seem like MERO is getting rid of stale entries.

Thanks

Marconius
Posts: 47
Joined: Wed Oct 11, 2006 6:55 pm

Postby Marconius » Tue May 22, 2007 5:52 pm

I dig a little deeper and I find something kind of odd. I looked in the mdb and sorted by threat level, so I pick an enrtry that is at a -4 threat level and I find where it says it was "last modified" and it says 4-10-07. So I look in the log from 4-10-07 and I search for the IP to see where it appears from the SMTP-DENY to see where it reoffended. What I find is that the first instance of it is MERO adding to the list. It didn't come from the SMTP-DENY file. So the last modified date was MERO putting it back into the list for some reason, which I don't see a connect for it to go in there with... Then in the next entry it is in the SMTP-DENY and MERO updates it and removes it, but I don't see where it atually shows up in the SMTP-DENY by itself. I see MERO add it in and then removes it itself on the next couple of runs. If I look in the mdb also, the last modified dates are no older than in March of this year, but I have creation dates from when I first installed the program in 06. Any thoughts?

vishal2332
Posts: 1
Joined: Sat Oct 22, 2011 11:43 am

Re: MERO

Postby vishal2332 » Sat Oct 22, 2011 11:55 am

helo,

Am not able to get this MERO STUFF anywhere.

can someone please post the link.
Regards,
Vishal Shinde

Bobt
Posts: 1
Joined: Thu Oct 25, 2012 12:57 pm

Re: MERO

Postby Bobt » Thu Oct 25, 2012 1:09 pm

Does anyone have a copy of this? The website that all the links point to is gone. I realize it's old but it still looks useful.

Thanks,
Bob

Who is online

Users browsing this forum: No registered users and 2 guests