myITforum.com Community Forum myITforum.com Community Forum

Home  Forums  Blogs  Live Support chat  Search Articles  Wiki  FAQ  Email Lists  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Photo Gallery  Member List  Search  Calendars  FAQ  Ticket List  Log Out

All Forums RSS Feed Subscription:


  


Converting "Hardware 06A..." Report to Collection Query

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
  Printable Version
All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Converting "Hardware 06A..." Report to Collection Query Page: [1]
Login
Message << Older Topic   Newer Topic >>
Converting "Hardware 06A..." Report to Collec... - 11/22/2008 11:56:37 PM   
jkuta

 

Posts: 14
Score: 0
Joined: 9/13/2007
Status: offline 		I am trying to create a collection based on the following:

Select v_R_System_Valid.Netbios_Name0
from v_R_System_Valid LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID
Where (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TotalSecurityLogTime0 < 5 or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID IS NULL)
 
The syntax above is a shortened version of the full SQL statement that makes up report number 473, "Hardware 06A - Computers for which console users could not be determined" in SCCM"  I removed lines I didn't think were necessary for getting the collection query to work but no matter what I try I keep getting an error message about incorrect syntax when I click "OK" to save the collection query.

Any ideas how I can covert this to the proper collection query syntax?


Post #: 1
RE: Converting "Hardware 06A..." Report to Co... - 11/24/2008 9:26:43 AM   
jnelson993


Posts: 959
Score: 132
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		That's SQL code you're using.  Collections use WQL (WMI Query Language) which is a subset of SQL.  Instead of using SQL views like V_R_System_Valid, it uses WMI classes like SMS_R_System_Valid.  I don't have a CM console handy right this minute, but if you open the collection query builder in CM, and you select the same classes, you can look at the query language and it will show you what they are.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jkuta)
Post #: 2
RE: Converting "Hardware 06A..." Report to Co... - 12/5/2008 7:00:50 PM   
jkuta

 

Posts: 14
Score: 0
Joined: 9/13/2007
Status: offline 		Easier said than done.  I've been trying to craft this collection query off and on for awhile now and still no joy. I don't understand why is it so difficult to translate a CM report into a collection query or why there isn't a wizard provided to accomplish this.

All I want to do is create a collection that shows me all workstations that are not providing console usage statistics via the local security event log so I can target them for remediation.  I can always fall back on the manual method of running the report, exporting the names of the machines to a file and using that to create a direct membership collection but this an extremely time-intensive and otherwise cluegy way of doing it.



_____________________________

JK

(in reply to jnelson993)
Post #: 3
RE: Converting "Hardware 06A..." Report to Co... - 12/5/2008 11:05:32 PM   
gjones


Posts: 903
Score: 60
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline 		I did a quick look and you will not be able to convert this to WQL as you are indirectly use the Max function which does not exist within WQL.

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx


(in reply to jkuta)
Post #: 4
RE: Converting "Hardware 06A..." Report to Co... - 12/6/2008 1:46:20 PM   
jkuta

 

Posts: 14
Score: 0
Joined: 9/13/2007
Status: offline 		Thanks gjones, prior to your post and through alot of trial and error I had discovered what you stated above as being true so I ended up going with the following WQL collection query:

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 left join SMS_G_System_SYSTEM_CONSOLE_USAGE on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID is null 
and SMS_R_System.Decommissioned = 0
and SMS_R_System.Client = 1
and SMS_R_System.Obsolete = 0

This appears to be working as my collection is showing me all my workstations whose security event logs are not registering events properly. 
On a side note and in case anyone else is experiencing the same issue whereby security events are not registering in the Security Event log on Windows XP workstations, the fix I am going to deploy involves running the following commands via batch file to recreate the local group policy database.

md %windir%\security\oldsec
move /y %windir%\security\*.log %windir%\security\oldsec
move /y %windir%\security\database\*.sdb %windir%\security\oldsec
secedit /configure /db %windir%\security\database\repaired.sdb /cfg "%windir%\security\templates\setup security.inf" /log %windir%\security\repaired.log

Thanks again jnelson993 and gjones for your insight.

< Message edited by jkuta -- 12/6/2008 1:47:42 PM >

_____________________________

JK

(in reply to gjones)
Post #: 5
Page:   [1]
All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Converting "Hardware 06A..." Report to Collection Query Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.469