Where are the Audit Level settings stored?
-
Every server has 1 of four possible audit settings: (None, Failure, Success, All). I can see that thru the EM...but considering the number of servers i have here, i want a programmattic way of determining that setting. Where is that data stored that i might retrieve it?
Thanks!
-
I just did the same thing, I used a combination of xp_loginconfig extended stored procedure and osql. I wrote the results of osql to a text file and then queried that, the server list is select through a while loop lookup table. Could have done it via DTS but this was a little quicker to put together, you jsut need to change a little of the code to meet your needs.
set
nocount on
create
table #temp
(
servername nvarchar(200),
Name
nvarchar(200))
create
table #ServerName
(
[ID] [int] IDENTITY (1, 1) NOT NULL ,
ServerName varchar
(128),
Processed [bit]
NOT NULL CONSTRAINT [ServerName_Processed] DEFAULT (0))
insert
into #ServerName(ServerName)
select
ServerName from SQLMonitorEmailList (nolock)
declare
@servername nvarchar(200)
declare
@status nvarchar(200)
declare
@cmd nvarchar(200)
while
(select Count(*)
from
#ServerName where Processed = 0) > 0
Begin
select
@servername= (select ServerName from #ServerName
where
id in (select top 1 id from #ServerName
where
Processed = 0))
Set
@cmd = 'osql -S' + rtrim(@ServerName) + ' -UYourUser -PYourPassword -n -h-1 -I40 -t40 -Q"xp_loginconfig ''audit level''" -oc:\output.txt'
--print
@cmd
Exec
master..xp_cmdShell @cmd, no_output
insert
into #temp(servername, name)
select
top 1 @servername, F1
FROM
(
SELECT
*
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\";Extended properties="Text;HDR=NO"')...output#txt
)
TextInfo
update
#ServerName set Processed = 1
where
id in (select top 1 id
from
#ServerName where Processed = 0)
end
select
servername,
'AUDIT LEVEL'
as Name,
upper
(ltrim(rtrim(replace(name, 'audit level', '')))) as ConfiValue from #temp
where
upper(ltrim(rtrim(replace(name, 'audit level', '')))) in ('none')
order
by servername
drop
table #ServerName
drop
table #temp
Hope this helps
-
That's perfect! Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply