Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers th
at
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client to
ol
> 3) Change events - original data, new data, who did it - stuff like that.
.
> Thanks in advance.sql

Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.
Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.
|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon
|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2
|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client tool
> 3) Change events - original data, new data, who did it - stuff like that...
> Thanks in advance.

Right Outer Join with Where clause

Using SQL Server 2005

I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped.
I need a to show the count of each events that have happened to it within a certain time period.
This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table.
What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count)
Any thoughts? I'm a complete newbie at this by the way.

Thanks

Code Snippet

SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit
FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
WHERE (DeviceEventDurationLog.TimeIn > @.StartDate) AND (DeviceEventDurationLog.TimeIn < @.EndDate)
GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit
ORDER BY DeviceStatusWords.DeviceName

Here it is,

Code Snippet

select

devicestatuswords.devicename,

count(deviceeventdurationlog.statusbit) as bitcount,

deviceeventdurationlog.statusbit as bit

from

deviceeventdurationlog

right outer join devicestatuswords

on deviceeventdurationlog.deviceid = devicestatuswords.deviceid

and (deviceeventdurationlog.timein > @.startdate)

and (deviceeventdurationlog.timein < @.enddate)

group by

devicestatuswords.devicename,

deviceeventdurationlog.statusbit

order by

devicestatuswords.devicename

|||

It looks to me like you need to join this stuff to a "DEVICE" table or something similar that lists all "DeviceID" entries. If you will take the "DEVICE" table (or similar) and left join to the "DEVICE" table the results of this query you should have what you are after.

|||

I believe the reason you're missing rows is because the engine is fulfilling the FROM clause first, and then filtering out rows based upon the WHERE clause. In the following example, the engine will do all the filtering at the same time and so the RIGHT join maintains the records in the DeviceStatusWords table.

Code Snippet

....

FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
AND (DeviceEventDurationLog.TimeIn > @.StartDate) AND (DeviceEventDurationLog.TimeIn < @.EndDate)

.....

HTH!

|||The DeviceStatusWords table is the table that contains the list of devices. That's why I needed the RIGHT OUTER JOIN to select all devices from the DeviceStatusWords table. I just couldn't fiugure out the exact syntax - the previous reply was just what I was looking for though
- thanks guys for once again educating me!sql