Archives

Did You Know That M2M Logs Deletions?

In troubleshooting the problem in the above post, I came across an undocumented (as far as I know) procedure to log details about deleted records. I’m not referring to the ECM auditing available from the CSPROD Screen.

What is the M2M Delete Log and how does it work?

The M2M Tech showed me a table called M2MDELETELOG in the M2M database (versions 5.X and later) which logs deletions from tables. I was thrilled to learn this because the normal M2M audits are very limited. M2M logs the deletion date, workstation which called for it, and a text field which typically includes the user name, table, and screen involved. I traced the deletion of the accounting years through this table in the last problem.

Upon further digging, I found a stored procedure called LogDeletes which actually inputs the values.

That’s great. Is there a catch?

Well, unfortunately M2M did not go far enough with this. Take a look at the LogInfo field for a sample deletion in which I deleted a sales order:

Unqualified M2MDELETE by USER in SOMAST from form frmSo Parameters: NONE

What’s wrong with that? Most of the pertinent information is in one field. The user, table, and screen are all in that one text field and must be broken out to have statistical value. I know the user deleted a record in SOMAST, but I can’t determine which one, so it would have been nice to have the identity column of the deleted record. This of course has limited value because the identity column tells you nothing pertinent. You would have to examine a previous back up and track back to the useful record identification such as the sales order number.

Furthermore, there is a problem when you examine the stored procedure code. Can you spot it?

USE [M2MDATA01];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE LogDeletes (@LogInfo varchar(256))
AS
BEGIN
	DECLARE @RecCount int, @Min int, @Max int, @Target int
 
	SET @RecCount = (SELECT COUNT(*) FROM M2MDeleteLog)
 
	IF @RecCount > 20000
	BEGIN
		SET @Min = (SELECT MIN(RecordID) FROM M2mdeletelog) 
		SET @Max = (SELECT MAX(RecordID) FROM M2MDeleteLog)
 
		SET @Target = ((@Max - @Min) /2 ) + @Min
 
		DELETE FROM M2MDeleteLog WHERE RecordID < @Target
	END
 
	INSERT INTO M2MDeleteLog (UserName, UserSPID, LogDate, Workstation, LogInfo)
	VALUES (SYSTEM_USER, @@SPID, GETDATE(), HOST_NAME(), @LogInfo)
END
GO

The point is that most of it is entirely unnecessary. Other than the insertion statement, everything else should be accomplished using a scheduled job. Each and every time you delete a record in M2M, it assigns variables and checks the record count of the delete log. This wastes time and should only be done on a periodic basis.

So what? How much time would that waste? Well, after I stripped the insert statement from it and ran it on my server it took about a 1/10th of a second every time it ran. In the month of December this process ran almost 8000 times for just one of the companies on my server. This may sound excessive, but if you delete one sales order with 10 line items which have 10 releases each, you have deleted in excess of 100 records. So, in just one of my companies almost 15 minutes of time that month was wasted. Time is money, and this doesn’t account for the wasted memory being used by the variables either.

Even as the code is written, it isn’t written well. Since the cap is 20,000 records, all the system needs to do is take the last RecordID, subtract 10,000 from it, and delete anything older than that. I appreciate the effort at clean up, which does not occur with the ECM audits, but I wish it was better implemented.

A Possible Future Project

As a DBA, one of my biggest concerns is one of my users becoming disgruntled and deleting important records in M2M. Imagine the following scenario. Patty in Sales becomes unhappy with the company and looks for another job. After she secures one, she starts deleting random open Sales Orders in M2M over a period of a few hours. How long would it take to detect the problem? If your company is typical, I would guess several weeks. Since my company has sales orders which remain open for years, it could take longer than that. If you discover three weeks later that you have missing sales orders, Patty has already left and you have no recourse. You are going to have to manually audit every single sales order, check to see if it’s still in your system, and re-enter them with new sales order numbers. It is way too late to perform a system restore.

Therefore, I’m thinking about creating a scheduled job to export this table, extract the values out of the LogInfo field, and check the resulting table for suspicious behavior on a regular basis. The job could run every 15 minutes or so, check the prior time period’s deletes, and notify the admin via e-mail when some threshold was reached. If I did so, I would of course share it on the blog.

What do you folks think?

2 comments to Did You Know That M2M Logs Deletions?

  • roleki

    It’d be nice if they logged what was deleted, specifically, rather than just flag that somebody deleted something from somewhere at some point. It’s only useful if you catch it immediately, unless you want to find out who deletes the most stuff or other useless stats.

  • Debbie

    I agree… It is too easy for someone to delete whole directories because we have to give so much access and permissions in order to operate our business.

Leave a Reply to roleki

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>