?
Solved

Creating SCCM Query

Posted on 2014-03-07
12
Medium Priority
?
900 Views
Last Modified: 2014-03-19
I would like to create a query in SCCM 2007 that list all the servers that have the SCCM client installed. Here is what I found online but I'm not sure how to impliment it.

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 where OperatingSystemNameAndVersion like 'Microsoft Windows NT %Server%' and client=1

I right click on Queries and select New - Query
I give the query it's name
I change Object Type to Unspecified (Not sure if this is correct)
I choose Edit Query Statement and I paste the above information.
When I choose Apply and OK the new query says, "The ConfigMgr Provider reported an error."

Any idea what I'm doing wrong?

Thank you
0
Comment
Question by:BuRinger7a
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 9

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 39913430
Try this 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 where SMS_R_System.OperatingSystemNameandVersion like "%server%" and SMS_R_System.Client =1 1
0
 

Author Comment

by:BuRinger7a
ID: 39913437
I'm getting a syntax error. This is how I have it copied:

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 where SMS_R_System.OperatingSystemNameandVersion like "%server%" and SMS_R_System.Client =1 1
0
 
LVL 9

Accepted Solution

by:
Vijaya Reddy Pinnapa Reddy earned 2000 total points
ID: 39913442
Remove 1 from 11. Try with this 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 where SMS_R_System.OperatingSystemNameandVersion like "%server%" and SMS_R_System.Client =1
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:BuRinger7a
ID: 39913452
Removing the 1 did the trick! Also, I would like to create a query that shows when a server with the SCCM client was last rebooted. I found some online but I'm getting syntax errors.

Thank you
0
 
LVL 9

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 39913523
Post the query here. We will check the syntax.
0
 

Author Comment

by:BuRinger7a
ID: 39913527
SELECT
cs.Name0 AS 'ComputerName',
DATEDIFF(HOUR, os.LastBootUpTime0, ws.LastHWScan)
AS 'Uptime (in Hours)',
CONVERT(VARCHAR(26), os.LastBootUpTime0, 100)
AS 'Last Reboot Date/Time',
CONVERT(VARCHAR(26), ws.LastHWScan, 101)
AS 'Last Hardware Inventory'
FROM
dbo.v_GS_WORKSTATION_STATUS ws
LEFT OUTER JOIN dbo.v_GS_Operating_System os
ON ws.ResourceID = os.ResourceID
LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM cs
ON cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
ORDER BY os.LastBootUpTime0 ASC
0
 
LVL 9

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 39913546
Try this query
SELECT
      CS.Name0 as 'PC Name',
      CS.UserName0 as 'User ID',
      OS.lastbootuptime0 as 'Boot Time'
 FROM
      v_GS_COMPUTER_SYSTEM CS
      join v_GS_OPERATING_SYSTEM OS on CS.ResourceID = OS.ResourceID
      join v_GS_WORKSTATION_STATUS WS on CS.ResourceID = WS.ResourceID


Or


select  distinct
v_R_System_Valid.ResourceID,
v_R_System_Valid.Netbios_Name0 AS [Computer Name],
V_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Boot Time],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site.SiteCode as [SMS Site Code]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
Where v_FullCollectionMembership.CollectionID = @CollectionID
Order by v_R_System_Valid.Netbios_Name0



Dont forgot to mark as answer, if it is helpful to you
0
 

Author Comment

by:BuRinger7a
ID: 39913553
I get syntax errors on both of them.
0
 
LVL 9

Expert Comment

by:Vijaya Reddy Pinnapa Reddy
ID: 39913580
Try this query

SELECT v.Name0, v.Active0, v.Obsolete0, v.Client_Version0,
Convert(VarChar(10), OS.LastBootUpTime0, 101) 'Last Boot Date',
os.lastBootUpTime0,DATEDIFF(dd, LastBootUpTime0, GETDATE())AS 'Number of Days since last bootup'
FROM v_R_System v
LEFT JOIN v_Gs_Operating_System OS on v.ResourceID = OS.ResourceID
where v.Operating_System_Name_and0 like '%server%'
0
 

Author Comment

by:BuRinger7a
ID: 39914261
I will give that a shot on Monday and let you know.

Thanks
0
 

Author Comment

by:BuRinger7a
ID: 39917413
I'm still getting a syntax error.
0
 

Author Comment

by:BuRinger7a
ID: 39924357
Any idea on the reboot query?

Thanks
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A procedure for exporting installed hotfix details of remote computers using powershell
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question