SoloDig

SELECT GS.Name0 as ‘PC Name’, GSS.DisplayName0 as ‘Service’, GSS.StartMode0 as ‘Startup’, GSS.State0 as ‘State’ FROM v_GS_SERVICE GSS, v_GS_COMPUTER_SYSTEM GS Where GSS.StartMode0 = ‘Auto’ and GSS.State0 != ‘Running’ and GSS.ResourceID = GS.ResourceID ORDER BY GS.Name0, GSS.DisplayName0

Thanks to jones for original post.

All advertisements with specific status :

This report gives you all percentage count with specific status for all advertisement in your environment. In order to get the list of machines where it is advertised with specific status,this report has to be linked to default report i.e 106.It works with SCCM , if you want to run the report in SMS 2003 ,see below after this code.
SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS ‘Number of clients with this Status’,
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS ‘Percent with this Status’,
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS ‘Total # Clients with Accepted Status’

FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as ‘number’
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState

WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID

GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

Prompt:

SELECT DISTINCT
a.MessageStateName

FROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus b

WHERE a.MessageID = b.LastStatusMessageID

ORDER BY MessageStateName

SMS 2003 report:

This is similar to the above report but few things gets changed.

SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS ‘Number of clients with this Status’,
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS ‘Percent with this Status’,
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS ‘Total # Clients with Accepted Status’

FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as ‘number’
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState

WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID

GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

The prompt condition for status will be same as above ,use above query and once you do this ,go the report properties and link it to the default report ID i.e 110 inorder to get the list of systems where the status is failed.

Gives list of machines where static IP exists:

select 
    CS.Name0,
    NAC.IPAddress0
from
    dbo.v_GS_COMPUTER_SYSTEM cs
    JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceId = NAC.ResourceId
Where
    NAC.IPAddress0 !=”
    and DHCPEnabled0 = 0

Gives list of machines where and When RAM changed?

select
CS.Name0,
CS.UserName0,
RAM.TotalPhysicalMemory0,
RAM.TimeStamp,
HRAM.TotalPhysicalMemory0,
Max(HRAM.TimeStamp)
from
dbo.v_GS_COMPUTER_SYSTEM CS,
dbo.v_GS_X86_PC_MEMORY RAM,
dbo.v_HS_X86_PC_MEMORY HRAM
Where CS.ResourceID = RAM.ResourceID
and CS.ResourceID = HRAM.ResourceID
and RAM.TotalPhysicalMemory0 != HRAM.TotalPhysicalMemory0
Group by
CS.Name0,
CS.UserName0,
RAM.TotalPhysicalMemory0,
RAM.TimeStamp,
HRAM.TotalPhysicalMemory0

How to find Netbios name if you have GUID:

Select  Sys.Name0, Sys.SMSID0, Sys.Domain0, Sys.SystemRole0 from
    dbo.v_GS_SYSTEM Sys Where   Sys.SMSID0 = @GUID

Prompt for GUID:

select SMS_Unique_Identifier0 from v_R_System

How to moitor Windows Services:

select Distinct
CS.Name0, 
SER.Displayname0,
SER.Started0,
SER.StartMode0,
SER.State0
from
dbo.v_GS_COMPUTER_SYSTEM CS,
dbo.v_GS_SERVICE SER,
dbo.v_FullCollectionMembership FCM
where
CS.ResourceId = SER.ResourceID
and CS.ResourceId = FCM.ResourceID
and SER.displayname0 like ‘%firewall%’
and (SER.State0 != ‘Running’ or Isnull(SER.State0,”)=”)
and FCM.CollectionID = ‘SMS00001′

List the machines that belongs to which collection:

select a.CollectionId, b.Name from dbo.v_R_System r
join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
join dbo.v_Collection b on b.CollectionID = a.CollectionID
Where R.Name0 =@machine

Prompt:

select Name0 from v_R_System

Count of unidentified and uncategorized software’s:

SELECT
    ISC.NormalizedPublisher,
    ISC.NormalizedName,
    ISC.NormalizedVersion,
    ISC.CategoryName,
    count(*) as ‘count’
FROM
    dbo.v_GS_Installed_Software_Categorized ISC
WHERE
    ISC.FamilyName In (‘Unidentified’,'Uncategorized’)
group by
    ISC.NormalizedPublisher,
    ISC.NormalizedName,
    ISC.NormalizedVersion,
    ISC.CategoryName
order by
    ISC.NormalizedPublisher,
    ISC.NormalizedName,
    ISC.NormalizedVersion,
    ISC.CategoryName

Recently installed programs:

Gives list of machines where Programs installed on the machines past 2 weeks :

SELECT
    CS.Name0,
    CS.UserName0,
    ISW.ProductName0,
    ISW.VersionMajor0,
    ISW.VersionMinor0,
    ISW.Publisher0,
    ISW.RegisteredUser0,
    ISW.InstallDate0,
    ISW.InstallSource0
FROM
    dbo.v_GS_COMPUTER_SYSTEM CS,
    dbo.v_GS_INSTALLED_SOFTWARE ISW
WHERE
    ISW.ResourceID = CS.ResourceID
ORDER BY
    ISW.InstallDate0 DESC,
    CS.Name0,
    CS.UserName0,
    ISW.ProductName0

Group policy not updated for past 1 week:

This gives list of machines where the group policy database file not updated recently.Before creating the webreport,software inventory has to be enbaled for secedit.sdb file which will be present in %windir%\security\database.

collection:

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 inner join SMS_G_System_SoftwareFile  

on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId  

where

SMS_G_System_SoftwareFile.FileName = “secedit.sdb”  

and DATEDIFF(dd,SMS_G_System_SoftwareFile.ModifiedDate,GetDate()) > 7

Now you have to figure out why it is not updating

WebReport:

select a.Name0 ,a.User_Name0 ,a.Operating_System_Name_and0
, CONVERT(VARCHAR(12),b.ModifiedDate,107)As “GPO Date Last Applied”
from v_R_System a join v_GS_SoftwareFile b on b.ResourceID=a.ResourceID
where b.FileName=’secedit.sdb’
and DATEDIFF(dd,b.ModifiedDate,GetDate()) > 7
order by b.ModifiedDate

list all collections and their parent collection:

SELECT
    COL.CollectionID,
    COL.Name,
    COL.Comment,
    CTSC.parentCollectionID
FROM
    dbo.v_Collection COL,
    dbo.v_CollectToSubCollect CTSC
WHERE
    CTSC.subCollectionID = COL.CollectionID