Solved

Creating SCCM Query

Posted on 2014-03-07
12
853 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
  • 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 500 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now