In my daily work routine,i used to work with some of the web report to present it to management as well for troubleshooting purpose.I come across with many reports that are required.some reports that have taken from the internet. Here i am listing all of the reports that are useful and will be posting more and more.
Report for 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
Report for 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
Report for 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
Report for How to monitor 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'
Report for Patch compliance Progress report:
select CS.Name0, CS.UserName0,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches'))else 'Good Client'
end as 'Status',
ws.lasthwscan as 'Last HW scan',
FCM.collectionID
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.ResourceID
join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
Where
UCS.Status = '2'
and FCM.collectionid = 'SMS00001'
Group by
CS.Name0,
CS.UserName0,
ws.lasthwscan,
FCM.collectionID
Order by
CS.Name0,
CS.UserName0,
ws.lasthwscan,
FCM.collectionID
Report for 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
Report for Software inventory that is not reported for X (7 days) days:
This report will give you list of machines where the clients are not reported their software inventory data for more than 7 days and also pulls the machine which doesn't report their inventory at all to server(blank)
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 (ResourceId in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) > 7) or ResourceId not in (select ResourceID from SMS_G_System_LastSoftwareScan))
Report for 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 ,not tested in SMS.
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 AdvStateWHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementIDGROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName
Prompt:
SELECT DISTINCT
a.MessageStateNameFROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus bWHERE a.MessageID = b.LastStatusMessageID
ORDER BY MessageStateName
Report for 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.Normaliz
edPublisher,
ISC.NormalizedName,
ISC.NormalizedVersion,
ISC.CategoryName
order by
ISC.NormalizedPublisher,
ISC.NormalizedName,
ISC.NormalizedVersion,
ISC.CategoryName
Report for 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
Report/collection for clients where 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 web report,software inventory has to be enabled 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
Web Report:
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
Report for list of computers with file size sum(like .jpeg or mp3 etc) :
To get this report,you've actually added *.jpg/mg/avi in software in software inventory client agent ,inventory collection tab.
select SYS.Netbios_Name0,SUM(SF.FileSize)
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID
Where SF.FileName like '%.jpeg%'
group by SF.Filesize,Netbios_Name0
ORDER BY SYS.Netbios_Name0
Report for 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
