filter out column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

filter out column

Author

Topic

smith2487
Starting Member

9 Posts

Posted - 2013-06-11 : 15:24:48

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

352 Posts

Posted - 2013-06-11 : 15:46:48

Have you tried adding the criteria just above the order by as "AND lgm.name0 = 'Administrators'"?

djj

smith2487
Starting Member

9 Posts

Posted - 2013-06-11 : 18:04:48

thanks djj55!
your suggestion worked perfectly!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 00:56:59

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 00:58:40

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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-06-12 : 08:29:25

quote:
Originally posted by smith2487

thanks djj55!
your suggestion worked perfectly!

You are welcome.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources