Friday, October 8, 2010

Query to find the workstations thrown access denied against a client installation attempt

select InsStrValue, [time], [Site Code], [Site Server], Win32Error
row_number() over (partition by ins.InsStrValue order by stat.[time] desc) as rownum,
stat.Sitecode as [Site Code],
stat.MachineName as [Site Server],
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.


Blogger gabriel said...


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 ' '.

March 15, 2011 at 4:41 AM  
Blogger Jaison Jose said...

It is perfectly working for a copy paste once again...

March 15, 2011 at 10:46 AM  
Blogger John said...

How would you add the operating system and query only workstation for the script?

October 1, 2011 at 11:37 AM  
Blogger Jaison Jose said...

Join v_gs_operating_system table and add a filter in the where condition to limit the result only for workstations.

October 4, 2011 at 9:34 AM  
Blogger joy said...

I accidentally viewed your blog and I was so amazed with your work that it touched the deepness of my heart. It made me sentimental. Thanks for posting.

January 8, 2015 at 12:28 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home