Video Screencast Help

I need patch vulnarable query

Created: 18 Mar 2013 • Updated: 20 Mar 2013 | 1 comment
This issue has been solved. See solution.

Hi,

 

I have patch vulnarable query in NS6 below,I need sam equery in SMP 7 .

 

Can you please any one help on this?

 

 

 set rowcount 0  

set nocount on   
  
select distinct  
 Computer = isu._ResourceGuid,  
 SoftwareBulletin = swb_swu.ParentResourceGuid,  
 SoftwareUpdate = ia._ResourceGuid,  
 Severity = isnull(customSevName.SeverityName, vendorSevName.SeverityName),  
 ReleaseDate = swb.FirstReleaseDate,  
 Installed = case when iisu._ResourceGuid is null then 0 else 1 end  
into #Vulnerable  
from Inv_Software_Update ia  
join vItem i on i.Guid = ia._ResourceGuid  
join ResourceAssociation ii on ii.ResourceAssociationTypeGuid = '6CCB60F8-E88D-4BA2-959F-4B531C8C5FCD' --Is Installed  
  and ii.ParentResourceGuid = ia._ResourceGuid  
join ResourceAssociation isa on isa.ResourceAssociationTypeGuid = 'D528BCE5-8911-4762-90D9-72CA0AB87D86' --Is Applicable  
  and isa.ParentResourceGuid = ia._ResourceGuid  
join ResourceAssociation swb_swu on swb_swu.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293' --SWB to SWU  
  and ia._ResourceGuid = swb_swu.ChildResourceGuid  
join ItemActive activ on swb_swu.ParentResourceGuid = activ.Guid  
join Inv_Software_Bulletin swb on swb_swu.ParentResourceGuid = swb._ResourceGuid  
join Inv_Applicable_Microsoft_Software_Update isu on isu.InventoryRuleGuid = isa.ChildResourceGuid  
join Inv_AeX_AC_Identification cid on isu._ResourceGuid = cid._ResourceGuid  
left join Inv_Installed_Microsoft_Software_Update iisu on iisu.InventoryRuleGuid = ii.ChildResourceGuid  and isu._ResourceGuid = iisu._ResourceGuid   
left join CollectionMembership cm on cm.ResourceGuid = isu._ResourceGuid  
left join ResourceAssociation sup on sup.ResourceAssociationTypeGuid = '6B8742D9-4C3C-4CF1-B466-45DBEBCC281B' --SWU Supersedes SWU  
  and ia._ResourceGuid = sup.ChildResourceGuid  
left join Inv_PM_Severity_Rating customSev on ia._ResourceGuid = customSev._ResourceGuid  
  and customSev.ProviderGuid = 'E2FEA34C-ADBB-47BD-9D7A-1092C5078245'  
join Inv_PM_Severity_Rating vendorSev on ia._ResourceGuid = vendorSev._ResourceGuid  
  and vendorSev.SeverityRatingSystemGuid = 'F1BEB524-9694-4E8E-BF78-0F04736556E2'  
left join Inv_Severity_Rating_Level customSevName on customSev.SeverityRatingSystemGuid = customSevName._ResourceGuid  
  and customSev.SeverityLevel = customSevName.SeverityLevel  
join Inv_Severity_Rating_Level vendorSevName on vendorSev.SeverityRatingSystemGuid = vendorSevName._ResourceGuid  
  and vendorSev.SeverityLevel = vendorSevName.SeverityLevel  
where 1 = 1  
  and isnull(convert(varchar(36), sup.ParentResourceGuid), 'non-superseded') like '%_SupersedenceStatus%' --FILTER: Supersedence Status  
  and cid.[OS Name] like '%_OperatingSystem%' --FILTER: OS Type  
  and case activ.Enabled  
        when 1 then 'active'  
        else 'inactive'  
      end like '%_DistributionStatus%' --FILTER: Distribution Status  
  and iisu._ResourceGuid is null  
  and cm.CollectionGuid = '%_Collection%' --FILTER: Collection  
  and isnull(customSevName.SeverityName, vendorSevName.SeverityName) like '%_Severity%' --FILTER: Severity  
  and swb.FirstReleaseDate between '%_StartDate%' and '%_EndDate%' --FILTER: StartDate and EndDate  
  and upper(swb._ResourceGuid) like upper('%_SWBGuid%') --FILTER: SWBGuid  
  and upper(cid._ResourceGuid) like upper('%_CompGuid%') --FILTER: CompGuid  
  and upper(ia._ResourceGuid) like upper('%_SWUGuid%') --FILTER: SWUGuid  
  
  DECLARE @DateFormat int
  set @DateFormat = [dbo].fnPMCore_GetCultureCode('%_culture%')
 
select  
 v.Computer _ResourceGuid,  
 str1.String AS [Computer Name],  
 str2.String AS Bulletin,  
 str3.String AS [Update],  
 v.Severity,  
 rtrim(convert(varchar(11), v.ReleaseDate, @DateFormat)) AS Released  
from #Vulnerable v  
 join String str1 on v.Computer = str1.BaseGuid  
  and str1.StringRef = 'item.name'  
 join String str2 on v.SoftwareBulletin = str2.BaseGuid  
  and str2.StringRef = 'item.name'  
 join String str3 on v.SoftwareUpdate = str3.BaseGuid  
  and str3.StringRef = 'item.name'  
order by  
  [Computer Name],  
  v.ReleaseDate desc,  
  Bulletin desc,  
  [Update]  
  
drop table #Vulnerable
 
Thanks In Advance :)
 
Operating Systems:

Comments 1 CommentJump to latest comment

CRZ's picture

Moved to forum: Symantec Management Platform (Notification Server)

Good luck!


bit.ly/76LBN | APPLBN | 75LBN

SOLUTION