Shane
asked on
Creating SCCM Query
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.SMSUniqueIden tifier,
SMS_R_SYSTEM.ResourceDomai nORWorkgro up,
SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameAndVers ion 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
select
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIden
SMS_R_SYSTEM.ResourceDomai
SMS_R_SYSTEM.Client from SMS_R_System where OperatingSystemNameAndVers
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
ASKER
I'm getting a syntax error. This is how I have it copied:
select SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType, SMS_R_SYST EM.Name,
SMS_R_SYSTEM.SMSUniqueIden tifier,
SMS_R_SYSTEM.ResourceDomai nORWorkgro up,
SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSyst emNameandV ersion like "%server%" and SMS_R_System.Client =1 1
select SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.SMSUniqueIden
SMS_R_SYSTEM.ResourceDomai
SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSyst
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thank you
Post the query here. We will check the syntax.
ASKER
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_STATU S 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
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_STATU
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
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.ResourceI D,
v_R_System_Valid.Netbios_N ame0 AS [Computer Name],
V_GS_OPERATING_SYSTEM.Last BootUpTime 0 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.Res ourceID = v_R_System_Valid.ResourceI D)
inner join v_FullCollectionMembership on (v_FullCollectionMembershi p.Resource ID = v_R_System_Valid.ResourceI D)
left join v_Site on (v_FullCollectionMembershi p.SiteCode = v_Site.SiteCode)
Where v_FullCollectionMembership .Collectio nID = @CollectionID
Order by v_R_System_Valid.Netbios_N ame0
Dont forgot to mark as answer, if it is helpful to you
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.ResourceI
v_R_System_Valid.Netbios_N
V_GS_OPERATING_SYSTEM.Last
v_R_System_Valid.Resource_
v_Site.SiteCode as [SMS Site Code]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.Res
inner join v_FullCollectionMembership
left join v_Site on (v_FullCollectionMembershi
Where v_FullCollectionMembership
Order by v_R_System_Valid.Netbios_N
Dont forgot to mark as answer, if it is helpful to you
ASKER
I get syntax errors on both of them.
Try this query
SELECT v.Name0, v.Active0, v.Obsolete0, v.Client_Version0,
Convert(VarChar(10), OS.LastBootUpTime0, 101) 'Last Boot Date',
os.lastBootUpTime0,DATEDIF F(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_an d0 like '%server%'
SELECT v.Name0, v.Active0, v.Obsolete0, v.Client_Version0,
Convert(VarChar(10), OS.LastBootUpTime0, 101) 'Last Boot Date',
os.lastBootUpTime0,DATEDIF
FROM v_R_System v
LEFT JOIN v_Gs_Operating_System OS on v.ResourceID = OS.ResourceID
where v.Operating_System_Name_an
ASKER
I will give that a shot on Monday and let you know.
Thanks
Thanks
ASKER
I'm still getting a syntax error.
ASKER
Any idea on the reboot query?
Thanks
Thanks
select SMS_R_SYSTEM.ResourceID,SM