Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

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 | 761LBN

SOLUTION