Solved

Creating SCCM Query

Posted on 2014-03-07
12
871 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
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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