I wanted to put in WQL but i could not figure it out and i had it posted for a week or 2 and got no responses other then to do what i did so i went with this:
{the information that i need is in this table but i couldn't figure out how to make it wql dbo.Update_ScanStatus.LastErrorCode <> 0 }
******************************************
SELECT cm.MachineID
, cm.ArchitectureKey
, cm.Name
, cm.SMSID
, cm.SiteCode
, cm.Domain
, cm.ClientType
, cm.IsDirect
, cm.IsAssigned
, cm.IsClient
, cm.IsObsolete
, cm.IsActive
, cm.IsDecommissioned
, cm.AMTStatus
, cm.AMTFullVersion
, cm.SuppressAutoProvision
, ck.ApprovalStatus AS IsApproved
, ck.IsRevoked AS IsBlocked
, ck.AlwaysInternet AS IsAlwaysInternet
, ck.InternetEnabled AS IsInternetEnabled
FROM dbo.CollectionMembers AS cm INNER JOIN
dbo.Update_ScanStatus ON cm.MachineID = dbo.Update_ScanStatus.MachineID LEFT OUTER JOIN
dbo.ClientKeyData AS ck ON cm.SMSID = ck.SMSID
WHERE (cm.SiteID = 'ABC00123') AND (dbo.Update_ScanStatus.LastErrorCode <> 0)
************************************************************************************************
I was trying to do something like this but i couldn't get the WQL correct
Original post:
I think it needs to be something like this but my wql is weak and i have something wrong.
**********************
select SMS_R_SYSTEM.ResourceID
,SMS_R_SYSTEM.ResourceType
,SMS_R_SYSTEM.Name
,SMS_R_SYSTEM.SMSUniqueIdentifier
,SMS_R_SYSTEM.ResourceDomainORWorkgroup
,SMS_R_SYSTEM.Client
from
sms_r_system inner join SMS_Update_ScanStatus on SMS_Update_ScanStatus.machineid=sms_r_system.resourceid where SMS_Update_ScanStatus.LastErrorCode != 0
*************************************************************
The table that has the info i need is dbo.Update_ScanStatus.LastErrorCode.
Anything other then 0 is a failure.
Thanks,
Christopher Stauffer <><
Enterprise SMS Admin
MCTS ConfigMgr 2007
MCP SMS 2003
Email: CStauffer@myitforum.com
Blog: http://myitforum.com/cs2/blogs/cstauffer/
From: "Garth Jones" <garth@enhansoft.com>
Sent: Monday, June 22, 2009 9:40 AM
To: mssms@lists.myitforum.com
Subject: RE: [mssms] Creating Collections using SQL Central site replication?
But what does you SQL query look like. i.e. why can't you use WQL for this?
From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Chris Stauffer
Sent: Monday, June 22, 2009 9:29 AM
To: mssms@lists.myitforum.com
Subject: Re: [mssms] Creating Collections using SQL Central site replication?
369
PA100165
PA1 - Machines that failed to scan
18
6/5/2009 13:32
6/18/2009 13:29
6/21/2009 13:31
6/21/2009 13:31
6/21/2009 13:31
_RES_COLL_PA100165
1
6/21/2009 13:31
7DA569C000100008
0
6/19/2009 15:01
1033
<Binary data>
371
PA100167
PA1 - Test 2
18
6/19/2009 9:27
6/19/2009 9:27
6/22/2009 8:27
6/22/2009 8:27
6/22/2009 8:27
_RES_COLL_PA100167
1
6/22/2009 8:27
Testing query replication
6D3369C000100100
0
6/19/2009 9:31
1033
<Binary data>
Looks like the Value is 18.
I created a second collection using the same setup and have the save values and results. Central site is correct but child sites dont get the new query.
Thanks,
Christopher Stauffer <><
Enterprise SMS Admin
MCTS ConfigMgr 2007
MCP SMS 2003
Email: CStauffer@myitforum.com
Blog: http://myitforum.com/cs2/blogs/cstauffer/
From: "Chris Stauffer" <cstauffer@myitforum.com>
Sent: Saturday, June 20, 2009 10:17 AM
To: mssms@lists.myitforum.com
Subject: Re: [mssms] Creating Collections using SQL Central site replication?
Dont know i will look on monday :-)
Thanks,
Christopher Stauffer <><
Enterprise SMS Admin
MCTS ConfigMgr 2007
MCP SMS 2003
Email: CStauffer@myitforum.com
Blog: http://myitforum.com/cs2/blogs/cstauffer/
From: "Mark A Mears" <mark.mears@macys.com>
Sent: Friday, June 19, 2009 1:28 PM
To: mssms@lists.myitforum.com
Subject: Re: [mssms] Creating Collections using SQL Central site replication?
In the Collections table on the central site, what is the value of the
Flags column for that collection?
Thanks,
Mark
From:
"Chris Stauffer"
To:
Date:
06/19/2009 01:09 PM
Subject:
[mssms] Creating Collections using SQL Central site replication?
Sent by:
admin@lists.myITforum.com
I followed the directions to create the collection for machines that have
failed the WSUS scan as per these directions:
http://www.myitforum.com/articles/1/view.asp?id=5578
but the changes i made to the view query statement are not replicating to
child sites.
The query works on the central site but all the the child sites show all
of the clients for the site.
After reviewing the views code in SQL it is clear that the changes where
not replicated.
Any suggestions. This does not look like it is a solution that can be done
from the central site.
I would hate to have to replicate these changes manualy to 40 + sites.
Thanks,
Christopher Stauffer <><
Enterprise SMS Admin
MCTS ConfigMgr 2007
MCP SMS 2003
Email: CStauffer@myitforum.com
Blog: http://myitforum.com/cs2/blogs/cstauffer/
==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/
==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/
==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/
==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/
==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/
