The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Record deleted records to audit table
Posted: 05 Aug 09 1:48 PM
|
I need to record to an audit table who, what and when related to deleted records( system and custom.) SQL Profile tells me this jerk "sysdba" seems to be responsible for most of them  . SLX Profile could tell me if it was running on the machine of the user doing this. I was leaning toward a global script, possibly with secure events???? 
Some one has to have faced this before: anyone beaten it?
Thanks for your time and talent, Michael |
|
|
|
Re: Record deleted records to audit table
Posted: 05 Aug 09 3:19 PM
|
Unless you are using Web Client, SQL Profiler should show you the IP Address (and/or host name) of the machine where the requests are coming from.
From there you could use other network tools to dig further.
|
|
|
|
Re: Record deleted records to audit table
Posted: 06 Aug 09 12:51 AM
|
Depending on what tables/views you are talking about, what about using the WScript object to get the user details and write them to an audit table like the user audit bundle does you can get on this site?
Set WshNetwork = CreateObject("WScript.Network")
ComputerName = WshNetwork.ComputerName
LoginName = WshNetwork.UserName
|
|
|
|
Re: Record deleted records to audit table
Posted: 06 Aug 09 1:34 PM
|
Leon, the tricky bit isn't figuring out who deleted it, asuming you could intercept every delete statement on the way to the SQL server. Rauls's Idea of using the SQL profiler would work pretty well if I could overcome two issues: 1. Filter profiler to only show the deletes (no luck so far, still shows selects and updates) 2. Citrix users. 
I suppose I could just eliminate their ability to delete records from any table and call it a day.......now I starting to sound like the security guys doing my audit. 
Michael |
|
|
|
Re: Record deleted records to audit table
Posted: 06 Aug 09 2:55 PM
|
For #1, it is quite simple to filter it out:
Add a Column filter for the "TextData" column: Like %delete%
(I have done this many times in the past, and works wonderfully).
For #2, I would suggest you go ahead with #1 and if you detect that the Deletes are coming from #2, then go ahead and use SLX Profiler:
Run SLX Profiler on the Citrix server.
Select to Profile All Applications
Add a Filter for "%delete%" as well
|
|
|
|
Re: Record deleted records to audit table
Posted: 07 Aug 09 5:51 AM
|
| You might also consider the use of a DELETE trigger on the host database, for the tables in which you are particularly interested. But if deletes are coming in through synchronisations, I can't imagine that you would be a able to track down the source user very easily. |
|
|
|
Re: Record deleted records to audit table
Posted: 07 Aug 09 12:31 PM
|
Well step one is to limit the tables that users can delete records from. Severely.
Then on the forms where they are able to delete a record, you place your audit row inserting script.
Don't know what fields you want to record...
AuditTableID, Username, Userid, TableName, KeyFieldID, KeyfieldValue1, DateDeleted, etc.
But that's fairly easy to apply to many many forms....and you can sync in the deleted rows to the audit tables.
Be careful on Cascading deletes....where you have to track all of the children of children records.
Triggers unfortunately don't tell you who deleted the record......and don't know enough about monkeying with the SLX Provider extensions to play with it there.... |
|
|
|