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
