Reports on Patch Levels

[mikehday]

Does anyone know where i can get some SMS custom reports on what patches are not installed on certain collections only or is this something better obtained using MBSA 2.0?.

 

[GSC]

Here is what I used to query the database for Servers that didn't have an "approved" patch installed:

select distinct SMS_R_System.ADSiteName, SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_G_System_PATCHSTATE.ID, SMS_G_System_PATCHSTATE.Product, SMS_G_System_PATCHSTATE.Title, SMS_G_System_PATCHSTATE.Status from SMS_R_System inner join SMS_G_System_PATCHSTATE on SMS_G_System_PATCHSTATE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PATCHSTATE.Status = "Applicable" and SMS_G_System_SYSTEM.SystemRole = "Server" and (SMS_G_System_PATCHSTATE.ID = "MS03-042" or SMS_G_System_PATCHSTATE.ID = "MS03-043" or SMS_G_System_PATCHSTATE.ID = "MS03-044" or SMS_G_System_PATCHSTATE.ID = "MS03-045" or SMS_G_System_PATCHSTATE.ID = "MS03-049" or SMS_G_System_PATCHSTATE.ID = "MS04-011" or SMS_G_System_PATCHSTATE.ID = "MS04-012" or SMS_G_System_PATCHSTATE.ID = "MS04-014" or SMS_G_System_PATCHSTATE.ID = "MS04-015" or SMS_G_System_PATCHSTATE.ID = "MS04-016" or SMS_G_System_PATCHSTATE.ID = "MS04-018" or SMS_G_System_PATCHSTATE.ID = "MS04-019" or SMS_G_System_PATCHSTATE.ID = "MS04-020" or SMS_G_System_PATCHSTATE.ID = "MS04-021" or SMS_G_System_PATCHSTATE.ID = "MS04-022" or SMS_G_System_PATCHSTATE.ID = "MS04-023" or SMS_G_System_PATCHSTATE.ID = "MS04-024" or SMS_G_System_PATCHSTATE.ID = "MS04-025" or SMS_G_System_PATCHSTATE.ID = "MS04-028" or SMS_G_System_PATCHSTATE.ID = "MS04-029" or SMS_G_System_PATCHSTATE.ID = "MS04-030" or SMS_G_System_PATCHSTATE.ID = "MS04-031" or SMS_G_System_PATCHSTATE.ID = "MS04-032" or SMS_G_System_PATCHSTATE.ID = "MS04-034" or SMS_G_System_PATCHSTATE.ID = "MS04-037" or SMS_G_System_PATCHSTATE.ID = "MS04-041" or SMS_G_System_PATCHSTATE.ID = "MS04-042" or SMS_G_System_PATCHSTATE.ID = "MS04-043" or SMS_G_System_PATCHSTATE.ID = "MS04-044" or SMS_G_System_PATCHSTATE.ID = "MS04-045" or SMS_G_System_PATCHSTATE.ID = "MS05-001" or SMS_G_System_PATCHSTATE.ID = "MS05-002" or SMS_G_System_PATCHSTATE.ID = "MS05-003" or SMS_G_System_PATCHSTATE.ID = "MS05-004" or SMS_G_System_PATCHSTATE.ID = "MS05-007" or SMS_G_System_PATCHSTATE.ID = "MS05-009" or SMS_G_System_PATCHSTATE.ID = "MS05-010" or SMS_G_System_PATCHSTATE.ID = "MS05-011" or SMS_G_System_PATCHSTATE.ID = "MS05-012" or SMS_G_System_PATCHSTATE.ID = "MS05-013" or SMS_G_System_PATCHSTATE.ID = "MS05-015" or SMS_G_System_PATCHSTATE.ID = "MS05-016" or SMS_G_System_PATCHSTATE.ID = "MS05-017" or SMS_G_System_PATCHSTATE.ID = "MS05-018" or SMS_G_System_PATCHSTATE.ID = "MS05-019" or SMS_G_System_PATCHSTATE.ID = "MS05-023" or SMS_G_System_PATCHSTATE.ID = "MS05-024" or SMS_G_System_PATCHSTATE.ID = "MS05-025" or SMS_G_System_PATCHSTATE.ID = "MS05-026" or SMS_G_System_PATCHSTATE.ID = "MS05-027" or SMS_G_System_PATCHSTATE.ID = "MS05-028" or SMS_G_System_PATCHSTATE.ID = "MS05-030" or SMS_G_System_PATCHSTATE.ID = "MS05-031" or SMS_G_System_PATCHSTATE.ID = "MS05-032" or SMS_G_System_PATCHSTATE.ID = "MS05-033" or SMS_G_System_PATCHSTATE.ID = "MS05-035" or SMS_G_System_PATCHSTATE.ID = "MS05-036" or SMS_G_System_PATCHSTATE.ID = "MS05-037" or SMS_G_System_PATCHSTATE.ID = "MS05-038" or SMS_G_System_PATCHSTATE.ID = "MS05-039" or SMS_G_System_PATCHSTATE.ID = "MS05-040" or SMS_G_System_PATCHSTATE.ID = "MS05-041" or SMS_G_System_PATCHSTATE.ID = "MS05-042" or SMS_G_System_PATCHSTATE.ID = "MS05-043") order by SMS_R_System.ADSiteName, SMS_R_System.Name

You can always modify it to suit your purposes a bit more.
Hope that it helps!

Regards,
GSC

Common sense is not so common anymore

 

[mikehday]

Thanks ... I will give it a shot. But I have no experience with queries yet.

 

[tbrennans]

3 mofs I got from Rick Houchins
________________________________________________
// *********************************************************************************
//
// Created by SMS Export object wizard
//
// Thursday, January 27, 2005 created
//
// File Name: Security Patch Compliance.MOF
//
// Comments : security patch compliance mof
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Security Update Reports";
Comment = "This report displays the compliance percentage for a particular security update but for all sites";
DrillThroughColumns = {};


GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Compliance Percentage for a Particular Security Update for all Sites";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Security Update";
SampleValueSQL = "Select Distinct Title0 as 'Name'
\n\tFrom v_GS_PATCHSTATE
\nwhere type0 <> 'Microsoft Office'
\n\torder by Title0 asc";
VariableName = "Update";
}};
SecurityKey = "";
SQLQuery = "select QNumber, Site as 'Site Code', Installed as 'Total Installed', Applicable as 'Total Needed',
\n
\nInstalled + Applicable as 'Total Clients', (100 * Installed / (Applicable + Installed))
\nas '% Compliant'
\n
\nfrom (select v_RA_System_SMSInstalledSites.sms_installed_sites0 as Site,QNumbers0 as QNumber, Title0 as Issue,
\n
\nsum (case when v_gs_patchstate.Status0 = 'Installed' then 1 else 0 end) as Installed,
\nsum (case when v_gs_patchstate.Status0 = 'Applicable' then 1 else 0 end) as Applicable
\n
\nfrom v_gs_patchstate INNER JOIN
\n
\nV_GS_WORKSTATION_STATUS ON v_gs_patchstate.ResourceID = V_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\nv_RA_System_SMSInstalledSites ON v_gS_PATCHSTATE.ResourceID = v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\nv_Site ON v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = v_Site.SiteCode
\n
\nwhere (v_GS_PATCHSTATE.Title0 = @Update)
\n
\nGroup By v_RA_System_SMSInstalledSites.sms_installed_sites0,QNumbers0, Title0) as ps
\n
\nOrder BY '% Compliant' desc";
StatusMessageDetailSource = FALSE;
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Drill Down Reports";
Comment = "";
DrillThroughColumns = {};
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "DD For Compliance Percentage for all Software Updates";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Security Update";
SampleValueSQL = "Select Distinct Title0 as 'Name'
\n\tFrom v_GS_PATCHSTATE
\n\torder by Title0 asc";
VariableName = "Update";
}};
SecurityKey = "";
SQLQuery = "SELECT DISTINCT
\n
\nTOP 100 PERCENT dbo.v_R_System.Netbios_Name0
\nas 'Name', dbo.v_Site.SiteCode
\nas 'Site Code', dbo.v_GS_PATCHSTATE.Status0 AS Status, dbo.v_GS_PATCHSTATE.Title0
\nas 'Security Update', dbo.v_GS_WORKSTATION_STATUS.lasthwscan as ' Last HW Scan'
\n
\nFROM dbo.v_GS_PATCHSTATE INNER JOIN
\n dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\n dbo.v_RA_System_SMSInstalledSites ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\n dbo.v_Site ON dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = dbo.v_Site.SiteCode INNER JOIN
\n dbo.v_R_System ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
\ndbo.v_GS_SYSTEM ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
\n
\nWHERE (dbo.v_GS_PATCHSTATE.Title0 like @update)
\nAND(dbo.v_GS_PATCHSTATE.Status0 = 'Applicable')
\n
\nORDER BY dbo.v_R_System.Netbios_Name0";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Drill Down Reports";
Comment = "";
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "DD For Compliance Percentage for a Particular Security Update for all Sites";
NumPrompts = 2;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Security Update";
SampleValueSQL = "SELECT distinct v_GS_Patchstate.QNumbers0 as 'Update'
\nFROM v_GS_Patchstate
\nOrder by v_GS_Patchstate.QNumbers0";
VariableName = "Update";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Site Code";
SampleValueSQL = "SELECT distinct v_Site.SiteCode as 'Site Code'
\nFROM v_Site v_Site
\nOrder by v_Site.SiteCode";
VariableName = "Site";
}};
SecurityKey = "";
SQLQuery = "select
\nTOP 100 PERCENT dbo.v_R_System.Netbios_Name0
\nas 'Name', dbo.v_Site.SiteCode
\nas 'Site Code', dbo.v_GS_PATCHSTATE.Status0 AS Status, dbo.v_GS_PATCHSTATE.Title0
\nas 'Security Update', dbo.v_GS_WORKSTATION_STATUS.lasthwscan as ' Last HW Scan'
\n
\nFROM dbo.v_GS_PATCHSTATE INNER JOIN
\n dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\n dbo.v_RA_System_SMSInstalledSites ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\n dbo.v_Site ON dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = dbo.v_Site.SiteCode INNER JOIN
\n dbo.v_R_System ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
\ndbo.v_GS_SYSTEM ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
\n
\nWHERE (dbo.v_GS_PATCHSTATE.Qnumbers0 like @update)
\nAND (dbo.v_Site.SiteCode like @site)
\nAND(dbo.v_GS_PATCHSTATE.Status0 = 'Applicable')
\n
\nORDER BY dbo.v_R_System.Netbios_Name0";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Drill Down Reports";
Comment = "";
DrillThroughColumns = {};
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "DD For Compliance for all Security Software Updates for a Site";
NumPrompts = 2;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Security Update";
SampleValueSQL = "Select Distinct Title0 as 'Name'
\n\tFrom v_GS_PATCHSTATE
\n\torder by Title0 asc";
VariableName = "Update";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Site Code";
SampleValueSQL = "Select Distinct SMSITE.SiteCode as 'Site Code'
\nFrom V_Site SMSITE
\nOrder by SMSITE.SiteCode";
VariableName = "Site";
}};
SecurityKey = "";
SQLQuery = "SELECT DISTINCT
\n
\nTOP 100 PERCENT dbo.v_R_System.Netbios_Name0
\nas 'Name', dbo.v_Site.SiteCode
\nas 'Site Code', dbo.v_GS_PATCHSTATE.Status0 AS Status, dbo.v_GS_PATCHSTATE.Title0
\nas 'Security Update', dbo.v_GS_WORKSTATION_STATUS.lasthwscan as ' Last HW Scan'
\n
\nFROM dbo.v_GS_PATCHSTATE INNER JOIN
\n dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\n dbo.v_RA_System_SMSInstalledSites ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\n dbo.v_Site ON dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = dbo.v_Site.SiteCode INNER JOIN
\n dbo.v_R_System ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
\ndbo.v_GS_SYSTEM ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
\n
\nWHERE (dbo.v_GS_PATCHSTATE.Title0 like @update)
\nAND (dbo.v_Site.SiteCode like @site)
\nAND(dbo.v_GS_PATCHSTATE.Status0 = 'Applicable')
\n
\nORDER BY dbo.v_R_System.Netbios_Name0";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Security Update Reports";
Comment = "This report gives a percentage summary of every security update available in your site. Each patch shows the number of clients who have reported the patch, who needs the patch, and who has the patch installed.";
DrillThroughColumns = {};


GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Compliance Percentage for all Software Updates";
NumPrompts = 0;
RefreshInterval = 0;

SecurityKey = "";
SQLQuery = "select Issue as 'Name',QNumber, Installed as 'Total Installed',
\nApplicable as 'Total Needed',
\n
\nInstalled + Applicable as 'Total Clients', (100 * Installed / (Applicable + Installed))
\nas '% Compliant'
\n
\nfrom (select QNumbers0 as QNumber, Title0 as Issue,
\n
\nsum (case when patch.Status0 = 'Installed' then 1 else 0 end) as Installed,
\nsum (case when patch.Status0 = 'Applicable' then 1 else 0 end) as Applicable
\n
\nfrom v_GS_PatchState as patch join v_GS_WORKSTATION_STATUS WS
\non patch.ResourceID=WS.ResourceID
\n
\nWHERE (PATCH.Type0 = 'mbsa') or (PATCH.Type0 = 'ms04-028')
\n
\nGroup By QNumbers0, Title0) as ps
\n
\nOrder BY '% Compliant' desc";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Security Update Reports";
Comment = "This report gives a percentage summary of every security update available in your site. Each patch shows the number of clients who have reported the patch, who needs the patch, and who has the patch installed.
\n
\nYou will be prompted to enter a site code for this report.";
DrillThroughColumns = {};


GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Compliance Percentage for all Security Software Updates for a Site";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Site Code";
SampleValueSQL = "Select Distinct SMSITE.SiteCode as 'Site Code'
\nFrom V_Site SMSITE
\nOrder by SMSITE.SiteCode";
VariableName = "Site";
}};
SecurityKey = "";
SQLQuery = "select Issue as 'Name', Site, QNumber, Installed as 'Total Installed',Applicable as 'Total Needed',
\n
\n
\nInstalled + Applicable as 'Total Clients', (100 * Installed / (Applicable + Installed))
\nas '% Compliant'
\n
\nfrom (select QNumbers0 as QNumber, Title0 as Issue, siteCode as 'site',
\n
\nsum (case when v_gs_patchstate.Status0 = 'Installed' then 1 else 0 end) as Installed,
\nsum (case when v_gs_patchstate.Status0 = 'Applicable' then 1 else 0 end) as Applicable
\n
\nfrom v_gs_patchstate INNER JOIN
\n
\nV_GS_WORKSTATION_STATUS ON v_gs_patchstate.ResourceID = V_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\nv_RA_System_SMSInstalledSites ON v_gS_PATCHSTATE.ResourceID = v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\nv_Site ON v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = v_Site.SiteCode
\n
\nwhere v_Site.SiteCode like @site and type0 <> 'Microsoft Office'
\n
\nGroup By QNumbers0, Title0, sitecode) as PS
\n
\nOrder BY QNumber DESC";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****



_________________________________________________-

// *********************************************************************************
//
// Created by SMS Export object wizard
//
// Thursday, January 27, 2005 created
//
// File Name: Office Patch Compliance.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Office Update Reports";
Comment = "This report displays the compliance percentage for a particular office update but for all sites";
DrillThroughColumns = {};


GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Compliance Percentage for a Particular Office Update for all Sites";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Office Update";
SampleValueSQL = "Select Distinct Title0 as 'Name'
\n\tFrom v_GS_PATCHSTATE
\nWHERE (v_GS_PATCHSTATE.Type0 = 'Microsoft Office')
\n\torder by Title0 asc";
VariableName = "Update";
}};
SecurityKey = "";
SQLQuery = "select Patch, Site as 'Site Code', Installed as 'Total Installed', Applicable as 'Total Needed',
\n
\nInstalled + Applicable as 'Total Clients', (100 * Installed / (Applicable + Installed))
\nas '% Compliant'
\n
\nfrom (select v_RA_System_SMSInstalledSites.sms_installed_sites0 as Site, Product0 as Patch, Title0 as Issue,
\n
\nsum (case when v_gs_patchstate.Status0 = 'Installed' then 1 else 0 end) as Installed,
\nsum (case when v_gs_patchstate.Status0 = 'Applicable' then 1 else 0 end) as Applicable
\n
\nfrom v_gs_patchstate INNER JOIN
\n
\nV_GS_WORKSTATION_STATUS ON v_gs_patchstate.ResourceID = V_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\nv_RA_System_SMSInstalledSites ON v_gS_PATCHSTATE.ResourceID = v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\nv_Site ON v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = v_Site.SiteCode
\n
\nwhere (v_GS_PATCHSTATE.Title0 = @update)
\n
\nGroup By v_RA_System_SMSInstalledSites.sms_installed_sites0,Product0, Title0) as ps
\n
\nOrder BY '% Compliant' desc";
StatusMessageDetailSource = FALSE;
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Drill Down Reports";
Comment = "";
DrillThroughColumns = {};
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "DD For Compliance Percentage for all Office Updates for a Site";
NumPrompts = 2;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Office Update";
SampleValueSQL = "Select Distinct Title0 as 'Name'
\n\tFrom v_GS_PATCHSTATE
\nWhere type0 = 'Microsoft Office'
\n\torder by Title0 asc";
VariableName = "Update";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Site Code";
SampleValueSQL = "Select Distinct SMSITE.SiteCode as 'Site Code'
\nFrom V_Site SMSITE
\nOrder by SMSITE.SiteCode";
VariableName = "Site";
}};
SecurityKey = "";
SQLQuery = "SELECT DISTINCT
\n
\nTOP 100 PERCENT dbo.v_R_System.Netbios_Name0
\nas 'Name', dbo.v_Site.SiteCode
\nas 'Site Code', dbo.v_GS_PATCHSTATE.Status0 AS Status, dbo.v_GS_PATCHSTATE.Title0
\nas 'Security Update', dbo.v_GS_WORKSTATION_STATUS.lasthwscan as ' Last HW Scan'
\n
\nFROM dbo.v_GS_PATCHSTATE INNER JOIN
\n dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\n dbo.v_RA_System_SMSInstalledSites ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\n dbo.v_Site ON dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = dbo.v_Site.SiteCode INNER JOIN
\n dbo.v_R_System ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
\ndbo.v_GS_SYSTEM ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
\n
\nWHERE (dbo.v_GS_PATCHSTATE.Title0 like @update)
\nAND (dbo.v_Site.SiteCode like @site)
\nAND(dbo.v_GS_PATCHSTATE.Status0 = 'Applicable')
\n
\nORDER BY dbo.v_R_System.Netbios_Name0";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Drill Down Reports";
Comment = "";
DrillThroughColumns = {};
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "DD For Compliance Percentage for all Office Updates";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Office Update";
SampleValueSQL = "Select Distinct Title0 as 'Name'
\n\tFrom v_GS_PATCHSTATE
\nwhere type0 = 'Microsoft Office'
\n\torder by Title0 asc";
VariableName = "Update";
}};
SecurityKey = "";
SQLQuery = "SELECT DISTINCT
\n
\nTOP 100 PERCENT dbo.v_R_System.Netbios_Name0
\nas 'Name', dbo.v_Site.SiteCode
\nas 'Site Code', dbo.v_GS_PATCHSTATE.Status0 AS Status, dbo.v_GS_PATCHSTATE.Title0
\nas 'Security Update', dbo.v_GS_WORKSTATION_STATUS.lasthwscan as ' Last HW Scan'
\n
\nFROM dbo.v_GS_PATCHSTATE INNER JOIN
\n dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\n dbo.v_RA_System_SMSInstalledSites ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\n dbo.v_Site ON dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = dbo.v_Site.SiteCode INNER JOIN
\n dbo.v_R_System ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
\ndbo.v_GS_SYSTEM ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
\n
\nWHERE (dbo.v_GS_PATCHSTATE.Title0 like @update)
\nAND(dbo.v_GS_PATCHSTATE.Status0 = 'Applicable')
\n
\nORDER BY dbo.v_R_System.Netbios_Name0";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Drill Down Reports";
Comment = "";
DrillThroughColumns = {};
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "DD For Compliance Percentage for a Particular Office Update for all Sites";
NumPrompts = 2;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Office Update";
SampleValueSQL = "SELECT distinct v_GS_Patchstate.Product0 as 'Update'
\nFROM v_GS_Patchstate
\nOrder by v_GS_Patchstate.Product0";
VariableName = "Update";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Site Code";
SampleValueSQL = "SELECT distinct v_Site.SiteCode as 'Site Code'
\nFROM v_Site v_Site
\nOrder by v_Site.SiteCode";
VariableName = "Site";
}};
SecurityKey = "";
SQLQuery = "select
\nTOP 100 PERCENT dbo.v_R_System.Netbios_Name0
\nas 'Name', dbo.v_Site.SiteCode
\nas 'Site Code', dbo.v_GS_PATCHSTATE.Status0 AS Status, dbo.v_GS_PATCHSTATE.Title0
\nas 'Security Update', dbo.v_GS_WORKSTATION_STATUS.lasthwscan as ' Last HW Scan'
\n
\nFROM dbo.v_GS_PATCHSTATE INNER JOIN
\n dbo.v_GS_WORKSTATION_STATUS ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\n dbo.v_RA_System_SMSInstalledSites ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\n dbo.v_Site ON dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = dbo.v_Site.SiteCode INNER JOIN
\n dbo.v_R_System ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
\ndbo.v_GS_SYSTEM ON dbo.v_GS_PATCHSTATE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
\n
\nWHERE (dbo.v_GS_PATCHSTATE.Product0 like @update)
\nAND (dbo.v_Site.SiteCode like @site)
\nAND(dbo.v_GS_PATCHSTATE.Status0 = 'Applicable')
\n
\nORDER BY dbo.v_R_System.Netbios_Name0";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Office Update Reports";
Comment = "This report gives a percentage summary of every office update available in your site. Each patch shows the number of clients who have reported the patch, who needs the patch, and who has the patch installed.
\n
\nYou will be prompted to enter a site code for this report.";
DrillThroughColumns = {};


GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Compliance Percentage for all Office Updates for a Site";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Site Code";
SampleValueSQL = "Select Distinct SMSITE.SiteCode as 'Site Code'
\nFrom V_Site SMSITE
\nOrder by SMSITE.SiteCode";
VariableName = "Site";
}};
SecurityKey = "";
SQLQuery = "select Issue as 'Name', Site, QNumber, Installed as 'Total Installed',Applicable as 'Total Needed',
\n
\n
\nInstalled + Applicable as 'Total Clients', (100 * Installed / (Applicable + Installed))
\nas '% Compliant'
\n
\nfrom (select QNumbers0 as QNumber, Title0 as Issue, siteCode as 'site',
\n
\nsum (case when v_gs_patchstate.Status0 = 'Installed' then 1 else 0 end) as Installed,
\nsum (case when v_gs_patchstate.Status0 = 'Applicable' then 1 else 0 end) as Applicable
\n
\nfrom v_gs_patchstate INNER JOIN
\n
\nV_GS_WORKSTATION_STATUS ON v_gs_patchstate.ResourceID = V_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
\nv_RA_System_SMSInstalledSites ON v_gS_PATCHSTATE.ResourceID = v_RA_System_SMSInstalledSites.ResourceID INNER JOIN
\nv_Site ON v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 = v_Site.SiteCode
\n
\nwhere v_Site.SiteCode like @site and type0 = 'Microsoft Office'
\n
\nGroup By QNumbers0, Title0, sitecode) as PS
\n
\nOrder BY QNumber DESC";
StatusMessageDetailSource = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Office Update Reports";
Comment = "This report gives a percentage summary of every office update available in your site. Each patch shows the number of clients who have reported the patch, who needs the patch, and who has the patch installed.";
DrillThroughColumns = {};


GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Compliance Percentage for all Office Updates";
NumPrompts = 0;
RefreshInterval = 0;

SecurityKey = "";
SQLQuery = "select Issue as 'Name',QNumber, Installed as 'Total Installed',
\nApplicable as 'Total Needed',
\n
\nInstalled + Applicable as 'Total Clients', (100 * Installed / (Applicable + Installed))
\nas '% Compliant'
\n
\nfrom (select QNumbers0 as QNumber, Title0 as Issue,
\n
\nsum (case when patch.Status0 = 'Installed' then 1 else 0 end) as Installed,
\nsum (case when patch.Status0 = 'Applicable' then 1 else 0 end) as Applicable
\n
\nfrom v_GS_PatchState as patch join v_GS_WORKSTATION_STATUS WS
\non patch.ResourceID=WS.ResourceID
\n
\nWHERE (PATCH.Type0 = 'Microsoft Office')
\n
\nGroup By QNumbers0, Title0) as ps
\n
\nOrder BY '% Compliant' desc";
StatusMessageDetailSource = FALSE;
};
// ***** End *****


________________________________________________


// *********************************************************************************
//
// Created by SMS Export object wizard
//
// Thursday, January 27, 2005 created
//
// File Name: Additional Compliance Reports.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Machine Compliance Reports";
Comment = "This report will display all updates that are still applicable for a certain machine.";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Applicable Updates Reports";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Machine Name";
SampleValueSQL = "Select Distinct Name0 as 'Machine'
\nFrom v_GS_SYSTEM";
VariableName = "Variable";
}};
SecurityKey = "";
SQLQuery = "SELECT
\n\t\tPatch.Type0 as 'Category',
\n\t\tPatch.Title0 as 'Name',
\n\t\tPatch.ID0 as 'Update ID',
\n\t\tPatch.QNumbers0 as 'QNumber',\t
\n\t\tPatch.Product0 as 'Product'
\n\t\t
\n\t
\n\t\tfrom v_GS_PATCHSTATE Patch full outer join v_GS_SYSTEM on v_GS_SYSTEM.ResourceID = Patch.ResourceID
\n\t\twhere v_GS_SYSTEM.Name0 like @variable and Patch.Status0 = 'Applicable'
\n\tOrder by
\n\t\tPatch.Type0 ASC,
\n\t\tPatch.Title0 ASC";
StatusMessageDetailSource = FALSE;
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Compliance Reports";
Comment = "This report, broken down to show each site, will list the sum of all applicable updates and all installed updates and display a resulting percentage for all clients within your site.";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Overall Compliance Percentage for All Software Updates by Site";
NumPrompts = 0;
RefreshInterval = 0;

SecurityKey = "";
SQLQuery = "SELECT DISTINCT
\n app.sms_installed_sites0 AS [Site Code], installed.Expr1 AS [All Installed Updates], app.Expr1 + installed.Expr1 AS [All Applicable Updates],
\n LEFT(ROUND((installed.Expr1 + 0.1) / (app.Expr1 + installed.Expr1 + 0.1) * 100, 2), 5) AS [% Compliant]
\nFROM (SELECT s.sms_installed_sites0, COUNT(0) AS Expr1
\n FROM v_GS_PATCHSTATE p, v_ra_system_smsinstalledsites s
\n WHERE (p.Status0 = 'installed' AND p.resourceid = s.resourceid)
\n GROUP BY s.sms_installed_sites0) installed INNER JOIN
\n (SELECT s.sms_installed_sites0, COUNT(0) AS Expr1
\n FROM v_GS_PATCHSTATE p, v_ra_system_smsinstalledsites s
\n WHERE (p.Status0 = 'applicable' AND p.resourceid = s.resourceid)
\n GROUP BY s.sms_installed_sites0) app ON installed.sms_installed_sites0 = app.sms_installed_sites0";
StatusMessageDetailSource = FALSE;
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Compliance Reports";
Comment = "This report will list the sum of all applicable updates and all installed updates and display a resulting percentage for all clients within your site.
\n
\nThis report displays both Office and Security Updates.";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Overall Compliance Percentage for All Software Updates";
NumPrompts = 0;
RefreshInterval = 0;

SecurityKey = "";
SQLQuery = "SELECT installed.Expr1 AS [All Installed Updates],
\n app.Expr1 + installed.Expr1 AS [All Applicable Updates],
\n LEFT(ROUND(((installed.Expr1 + 0.1) / (app.Expr1 + installed.Expr1 + 0.1) * 100),2),5) AS [% Compliant]
\nFROM (SELECT COUNT(0) AS Expr1
\n FROM v_GS_PATCHSTATE
\n WHERE (Status0 = 'installed')) installed CROSS JOIN
\n (SELECT COUNT(0) AS Expr1
\n FROM v_GS_PATCHSTATE
\n WHERE (Status0 = 'applicable'))
\n app";
StatusMessageDetailSource = FALSE;
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Addtional Machine Compliance Reports";
Comment = "This report will show all updates that are installed on a particular machine.";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Installed Updates Reports";
NumPrompts = 1;
RefreshInterval = 0;

ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Machine Name";
SampleValueSQL = "Select Distinct Name0 as 'Machine'
\nFrom v_GS_SYSTEM";
VariableName = "Variable";
}};
SecurityKey = "";
SQLQuery = "SELECT
\n\t\tPatch.Type0 as 'Category',
\n\t\tPatch.Title0 as 'Name',
\n\t\tPatch.ID0 as 'Update ID',
\n\t\tPatch.QNumbers0 as 'QNumber',\t
\n\t\tPatch.TimeApplied0 as 'Installation Date'
\n\t\t
\n\t
\n\t\tfrom v_GS_PATCHSTATE Patch full outer join v_GS_SYSTEM on v_GS_SYSTEM.ResourceID = Patch.ResourceID
\n\t\twhere v_GS_SYSTEM.Name0 like @variable and Patch.Status0 = 'Installed'
\n\tOrder by
\n\t\tPatch.Type0 ASC,
\n\t\tPatch.Title0 ASC";
StatusMessageDetailSource = FALSE;
};
// ***** End *****