Solved

Creating SCCM Query

Posted on 2014-03-07
12
849 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Learn about cloud computing and its benefits for small business owners.
OfficeMate Freezes on login or does not load after login credentials are input.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

12 Experts available now in Live!

Get 1:1 Help Now