SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 filter out column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smith2487
Starting Member

Canada
9 Posts

Posted - 06/11/2013 :  15:24:48  Show Profile  Reply with Quote
I want to isolate the query below to just members of the Local Administrators group from column lgm.name0 and filter out the expected 'Administrator' and 'Domain Admins', but have been unable to.
I realize I need to put in a (where name0 = 'Administrators') but can't figure out where to put it.
Any help is appreciated.
Thanks.

declare @olddcm datetime
declare @oldhinv datetime
set @oldDCM=DATEADD(DAY,-3, getdate())
set @oldHinv=DATEADD(DAY,-3, getdate())
select sys1.netbios_name0
,lgm.name0 [Name of the local Group]
,lgm.account0 as [Account Contained within the Group]
, lgm.category0 [Account Type]
, lgm.domain0 [Domain for Account]
, lgm.type0 [Type of Account]
, case when ws.lasthwscan < @oldhinv then 'Last Hinv might be out of date'
when cs.lastcompliancemessagetime < @olddcm then 'CI evaluation might be out of date'
when ws.lasthwscan < cs.lastcompliancemessagetime then 'CI evaluated since hinv, not necessarily unreliable'
else 'Recent CI Eval, Hinv since CI Eval = Fairly Reliable'
end as [Reliability of Information]
from
v_gs_localgroupmembers0 lgm
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid
join v_r_system_valid sys1 on sys1.resourceid=lgm.resourceid
left join v_CICurrentComplianceStatus cs on cs.resourceid=lgm.resourceid
left join v_LocalizedCIProperties_SiteLoc loc on loc.ci_id=cs.ci_id
where loc.displayname = 'local group members into WMI'
and lgm.type0 = 'local'
and lgm.category0 = 'userAccount'
and lgm.account0 not in ('Administrator','Guest')
order by sys1.netbios_name0, lgm.name0, lgm.account0

I would like to show you a screen shot of what the query result looks like, but I'm unable to paste a screen shot in this forum.

http://myitforum.com/cs2/blogs/skissinger/archive/2010/04/25/report-on-all-members-of-all-local-groups.aspx

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 06/11/2013 :  15:46:48  Show Profile  Reply with Quote
Have you tried adding the criteria just above the order by as "AND lgm.name0 = 'Administrators'"?

djj
Go to Top of Page

smith2487
Starting Member

Canada
9 Posts

Posted - 06/11/2013 :  18:04:48  Show Profile  Reply with Quote
thanks djj55!
your suggestion worked perfectly!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/12/2013 :  00:56:59  Show Profile  Reply with Quote
also one more thing


..
from 
v_gs_localgroupmembers0 lgm 
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid 
join v_r_system_valid sys1 on sys1.resourceid=lgm.resourceid 
left join v_CICurrentComplianceStatus cs on cs.resourceid=lgm.resourceid 
left join v_LocalizedCIProperties_SiteLoc loc on loc.ci_id=cs.ci_id 
where loc.displayname = 'local group members into WMI' 
....


the way this is written the last left join will reduce to an inner join as you've added a filter from right table in where which will only return you records with matches in right table (v_LocalizedCIProperties_SiteLoc).
If you intention is to treat it as a left join and get unmatched ones too then it should be like


..
from 
v_gs_localgroupmembers0 lgm 
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid 
join v_r_system_valid sys1 on sys1.resourceid=lgm.resourceid 
left join v_CICurrentComplianceStatus cs on cs.resourceid=lgm.resourceid 
left join v_LocalizedCIProperties_SiteLoc loc on loc.ci_id=cs.ci_id 
and loc.displayname = 'local group members into WMI' 
where lgm.type0 = 'local' 
and lgm.category0 = 'userAccount' 
and lgm.account0 not in ('Administrator','Guest') 
order by sys1.netbios_name0, lgm.name0, lgm.account0 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/12/2013 :  00:58:40  Show Profile  Reply with Quote
Here's an article explaining the same

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 06/12/2013 :  08:29:25  Show Profile  Reply with Quote
quote:
Originally posted by smith2487

thanks djj55!
your suggestion worked perfectly!


You are welcome.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000