SCCM 2007 Patch Report Per Collection

Here is a slightly modified Patch Compliance report that allows for quick analysis for either a Security Bulletin or a KB Article number.  The default report in SCCM only allows you to focus on 1 OS at a time whereas this allows you to have a nice little report for a particular Security Bulletin or KB number.  (Because of the OR statement, you can also have both parameters filled to view both)

declare @collid varchar(20); set @collid = 'SMS0001'
declare @BulletinID varchar(20); set @BulletinID = 'MS12-048'
declare @ArticleID varchar(20); set @ArticleID = ''

declare @CI table(CI_ID int primary key)
if @ArticleID = '' set @ArticleID = NULL;
if @BulletinID = '' set @BulletinID = NULL;


insert @CI(CI_ID)
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 
and (ci.ArticleID = @ArticleID or ci.BulletinID = @BulletinID)

select
Vendor=ven.CategoryInstanceName,
UpdateClassification=cls.CategoryInstanceName,
ArticleID,
BulletinID,
Title,
Approved=case when exists(select 1 from v_CITargetedCollections where CI_ID=ci.CI_ID and CollectionID=@CollID) then '*' else '' end,
Present=NumPresent,
Missing=NumMissing,
NotApplicable=NumNotApplicable,
Unknown=NumUnknown,
Total=NumTotal,
PCompliant=convert(numeric(5,2), isnull((NumPresent+NumNotApplicable)*100.0/nullif(NumTotal, 0), 100)),
PNotCompliant=convert(numeric(5,2), isnull((NumMissing)*100.0/nullif(NumTotal, 0), 0)),
PUnknown=convert(numeric(5,2), isnull((NumUnknown)*100.0/nullif(NumTotal, 0), 0)),
CollectionID=@CollID,
UniqueUpdateID=CI_UniqueID,
InformationURL=InfoURL
from @CI ci
left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID=@CollID
left join v_UpdateInfo ui on ui.CI_ID=ci.CI_ID
left join v_CICategoryInfo_All ven on ven.CI_ID=ci.CI_ID and ven.CategoryTypeName='Company'
left join v_CICategoryInfo_All cls on cls.CI_ID=ci.CI_ID and cls.CategoryTypeName='UpdateClassification'
order by 1, 2, 3

Track Down Malicious Executables Using SCCM 2007

I have been chasing down some malicious code and found a very useful SCCM 2007 table.

declare @filename varchar(20)
set @filename = 'wiupdat.exe'

select 
sys.Name0 [Resource Name],
sys.AD_Site_Name0 [Resource Site Name],
sys.Operating_System_Name_and0 [Resource OS],
sys.Resource_Domain_OR_Workgr0 [Resource Domain],
sys.User_Name0 as [Resource Username],
app.LastUserName0 [App Username],
app.LastUsedTime0 [App Last Used Time],
app.TimeStamp [App Database Timestamp],
app.ExplorerFileName0 [App FileName],
app.OriginalFileName0 [App Original FileName],
app.CompanyName0 [App Company Name],
app.FileDescription0 [App FileDescription],
app.FileVersion0 [App File Version],
app.FolderPath0 [App Execution Path],
app.FilePropertiesHash0 [App Hash],
app.SoftwarePropertiesHash0 [App Software Properties Hash],
app.FileSize0 [App FileSize],
app.LaunchCount0 [App Launch Count],
app.ProductLanguage0 [App Product Language Code]
from v_GS_CCM_RECENTLY_USED_APPS app
left join v_R_System sys on sys.ResourceID = app.ResourceID
where ExplorerFileName0 = @filename
or OriginalFileName0 = @filename
order by TimeStamp

I've been using SCCM report "Software 07C - Recently used executables on a specific computer" to pull a list of recently run executables on a known infected workstation.  Make sure to display all executables by choosing 'N'.  Then look for the offending executable.  Plug in that executable name into the SQL query or build a report with a Prompt name of 'filename' and remove the top two lines in the SQL shown above.

A problem I see is that the Hash function used in SCCM 2007 does not seem to be MD5 or SHA1.  It is still useful assuming it creates a unique signature but we cannot use that information to find out information from https://www.virustotal.com