Query to find the workstations thrown access denied against a client installation attempt
select InsStrValue, [time], [Site Code], [Site Server], Win32Error
from
(
select
row_number() over (partition by ins.InsStrValue order by stat.[time] desc) as rownum,
ins.InsStrValue,
stat.[time],
stat.Sitecode as [Site Code],
stat.MachineName as [Site Server],
stat.Win32Error
from v_StatusMessage as stat
left join v_StatMsgInsStrings as ins
on stat.RecordID = ins.RecordID
where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER'
and stat.Win32Error = '5'
) as dt
where rownum=1
order by [time] desc
Description: This query will list you all the machines which have thrown an access denied error against client installation attempts etc.. This query will work only if you have SQL 2005 or above as your SMS DB.
from
(
select
row_number() over (partition by ins.InsStrValue order by stat.[time] desc) as rownum,
ins.InsStrValue,
stat.[time],
stat.Sitecode as [Site Code],
stat.MachineName as [Site Server],
stat.Win32Error
from v_StatusMessage as stat
left join v_StatMsgInsStrings as ins
on stat.RecordID = ins.RecordID
where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER'
and stat.Win32Error = '5'
) as dt
where rownum=1
order by [time] desc
Description: This query will list you all the machines which have thrown an access denied error against client installation attempts etc.. This query will work only if you have SQL 2005 or above as your SMS DB.
posted by Unknown at
6:18 AM
4 Comments:
Hi,
Very nice queries. Great help. I have a problem getting this one to work. It returns:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
It is perfectly working for me..do a copy paste once again...
How would you add the operating system and query only workstation for the script?
Join v_gs_operating_system table and add a filter in the where condition to limit the result only for workstations.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home