Systems with Inventories < 14 Days

This is a little SQL web report I created that identifies systems which have not reported hardware or software inventories within the last 14 days.

SELECT DISTINCT
    SYS.Netbios_Name0,
    HWSCAN.LastHWScan,
    SWSCAN.LastScanDate
FROM v_R_System SYS
…

Full LessThan14Days SQL Code Here

Note: you’ll want to change the collection ID to be the one you need.  Or modify the code to prompt for a collection would probably be even better still!

Web Report for Software Updates Compliance

SCCM doesn’t have a precreated report (that I can find) that quickly shows the patch compliance for a group of systems.  So I created one for myself, that I thought I’d share with every one.  To implement this, create a web report as you normally would; use the following SQL statement and remember to create a prompt with the information below that!

SQL Statement

select distinct
   sys.Name0,
   ui.BulletinID as BulletinID,
   ui.ArticleID as ArticleID,
   ui.Title as Title
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_R_System sys on css.ResourceID=sys.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=sys.ResourceID
where  css.Status=2 and ccm.CollectionID=@CollID
order by sys.Name0, ui.ArticleID

Prompts

Name: CollID
Prompt text: Collection ID
Provide a SQL statement:
begin
 if (@__filterwildcard = ”)
  select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
 else
  select CollectionID as CollectionID, Name as CollectionName from v_Collection
  WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
  order by Name
end