patching complaince for list of KB

 use CM_CAS



declare @CollID char(50)

select @CollID = CollectionID

from v_Collection

where CollectionID='CAS00846'


select distinct

m.Name0 as ServerName,

ui.BulletinID as Bulletin, 

ui.ArticleID as Article, 

ui.Title as Title,

--ucs.Status,


case vstate.StateName

when 'Update is installed' then '1'

Else '0'

End as 'Installed',

case vstate.StateName

when 'Update is required' then '1'

Else '0'

End as 'Required',

case vstate.StateName

when 'Detection state unknown' then '1'

Else '0'

End as 'Unknown',

case vstate.StateName

when 'Update is not required' then '1'

Else '0'

End as 'Not Required'

--m.User_Domain0+'\'+User_Name0 as LastLoggedOnUser,

--ucs.Status as UpdateStatus

--asi.SMS_Assigned_Sites0 as AssignedSite,

--m.Client_Version0 as ClientVersion,

--LastStatusCheckTime as LastStateReceived,

--LastStatusChangeTime as LastStateChange

from v_Update_ComplianceStatusAll  ucs

join v_UpdateInfo  ui on ucs.CI_ID=ui.CI_ID

join v_ClientCollectionMembers  cm on cm.ResourceID=ucs.ResourceID

join v_R_System m on m.ResourceType=5 and m.ResourceID=ucs.ResourceID and isnull(m.Obsolete0,0)<>1

join v_CICategories_All CATALL on CATALL.CI_ID=UCS.CI_ID

join v_CategoryInfo CATInfo on CATALL.CategoryInstance_UniqueID = CatInfo.CategoryInstance_UniqueID and CatInfo.CategoryTypeName = 'Product' 

AND CATInfo.CategoryInstanceName in ('Active Directory Rights Management Services Client 2.0','Adobe Acrobat','Adobe Flash Player','Adobe Reader','Antigen for Exchange/SMTP','ASP.NET Web Frameworks','Assistant de connexion Microsoft Online Services','Azure File Sync agent updates for Windows Server 2012 R2','Azure File Sync agent updates for Windows Server 2016','Azure Information Protection Client Dogfood Tool','Bing Bar','BizTalk Server 2002','BizTalk Server 2006R2','BizTalk Server 2009','BizTalk Server 2013','CAPICOM','Category for System Center Online Client','Complément Office Live','Compute Cluster Pack','Data Protection Manager 2006','Device Health','Dictionary Updates for Microsoft IMEs','Exchange 2000 Server','Exchange Server 2003','Exchange Server 2007','Exchange Server 2007 and Above Anti-spam','Exchange Server 2010','Exchange Server 2013','Exchange Server 2016','Expression Design 1','Expression Design 2','Expression Design 3','Expression Design 4','Expression Media 2','Expression Media V1','Expression Web 3','Expression Web 4','Firewall Client for ISA Server','Forefront Client Security','Forefront Endpoint Protection 2010','Forefront Identity Manager 2010','Forefront Identity Manager 2010 R2','Forefront Protection Category','Forefront Server Security Category','Forefront Threat Management Gateway, Definition Updates for HTTP Malware Inspection','Forefront TMG','Forefront TMG MBE','HealthVault Connection Center','HealthVault Connection Center Upgrades','Host Integration Server 2000','Host Integration Server 2004','Host Integration Server 2006','Host Integration Server 2009','Host Integration Server 2010','HPC Pack 2008','Installation de Windows Live OneCare Contrôle parental','Installation et mises à niveau de la Galerie de photos Windows Live','Installation et mises à niveau de Windows Live Writer','Internet Security and Acceleration Server 2004','Internet Security and Acceleration Server 2006','Locally published packages','Lokal veröffentlichte Pakete','Microsoft Advanced Threat Analytics','Microsoft Application Virtualization 4.5','Microsoft Application Virtualization 4.6','Microsoft Application Virtualization 5.0','Microsoft Azure Information Protection Client','Microsoft Azure Site Recovery Provider','Microsoft BitLocker Administration and Monitoring v1','Microsoft Dynamics CRM 2011','Microsoft Dynamics CRM 2011 SHS','Microsoft Dynamics CRM 2013','Microsoft Dynamics CRM 2015','Microsoft Dynamics CRM 2016','Microsoft Dynamics CRM 2016 SHS','Microsoft Lync 2010','Microsoft Lync Server 2010','Microsoft Lync Server 2013','Microsoft Monitoring Agent','Microsoft Online Services Sign-In Assistant','Microsoft Research AutoCollage 2008','Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010','Microsoft SQL Server 2012','Microsoft SQL Server 2014','Microsoft SQL Server 2016','Microsoft SQL Server 2017','Microsoft SQL Server Management Studio v17','Microsoft StreamInsight V1.0','Microsoft System Center DPM 2010','Microsoft System Center Virtual Machine Manager 2007','Microsoft System Center Virtual Machine Manager 2008','Microsoft Works 8','Microsoft Works 9','MS Security Essentials','Network Monitor 3','New Dictionaries for Microsoft IMEs','Office 2002/XP','Office 2003','Office 2007','Office 2010','Office 2013','Office 2016','Office 365 Client','Office Communications Server 2007','Office Communications Server 2007 R2','Office Communicator 2007 R2','Office Live Add-in','OneCare Family Safety Installation','OOBE ZDP','Packages publiés localement','Pakker som er publisert lokalt','Paquetes publicados localmente','Photo Gallery Installation and Upgrades','Report Viewer 2005','Report Viewer 2008','Report Viewer 2010','Search Enhancement Pack','Security Essentials','Service Bus for Windows Server 1.1','Services AD RMS (Active Directory Rights Management Services) Client 2.0','Silverlight','Skype for Business Server 2015','Skype for Business Server 2015, SmartSetup','Skype for Windows','SQL Server 2000','SQL Server 2005','SQL Server 2008','SQL Server 2008 R2','SQL Server 2012 Product Updates for Setup','SQL Server 2014-2016 Product Updates for Setup','SQL Server Feature Pack','System Center 2012 - App Controller','System Center 2012 - Data Protection Manager','System Center 2012 - Operations Manager','System Center 2012 - Orchestrator','System Center 2012 - Virtual Machine Manager','System Center 2012 R2 - Data Protection Manager','System Center 2012 R2 - Operations Manager','System Center 2012 R2 - Orchestrator','System Center 2012 R2 - Virtual Machine Manager','System Center 2012 SP1 - App Controller','System Center 2012 SP1 - Data Protection Manager','System Center 2012 SP1 - Operation Manager','System Center 2012 SP1 - Virtual Machine Manager','System Center 2016 - Data Protection Manager','System Center 2016 - Operations Manager','System Center 2016 - Orchestrator','System Center 2016 - Virtual Machine Manager','System Center Advisor','System Center Configuration Manager 2007','Systems Management Server 2003','Threat Management Gateway Definition Updates for Network Inspection System','TMG Firewall Client','Virtual PC','Virtual Server','Visual Studio 2005','Visual Studio 2008','Visual Studio 2010','Visual Studio 2010 Tools for Office Runtime','Visual Studio 2010 Tools for Office Runtime','Visual Studio 2012','Visual Studio 2013','Windows 10 and later upgrade & servicing drivers','Windows 10 Feature On Demand','Windows 10 GDR-DU','Windows 2000','Windows 8 Embedded','Windows Admin Center','Windows Azure Pack: Admin API','Windows Azure Pack: Admin Authentication Site','Windows Azure Pack: Admin Site','Windows Azure Pack: Configuration Site','Windows Azure Pack: Microsoft Best Practice Analyzer','Windows Azure Pack: Monitoring Extension','Windows Azure Pack: MySQL Extension','Windows Azure Pack: PowerShell API','Windows Azure Pack: SQL Server Extension','Windows Azure Pack: Tenant API','Windows Azure Pack: Tenant Authentication Site','Windows Azure Pack: Tenant Public API','Windows Azure Pack: Tenant Site','Windows Azure Pack: Usage Extension','Windows Azure Pack: Web App Gallery Extension','Windows Azure Pack: Web Sites','Windows Defender','Windows Dictionary Updates','Windows Essential Business Server 2008','Windows Essential Business Server 2008 Setup Updates','Windows Essential Business Server Preinstallation Tools','Windows GDR-Dynamic Update','Windows Internet Explorer 7 Dynamic Installer','Windows Internet Explorer 8 Dynamic Installer','Windows Live','Windows Live','Windows Live Toolbar','Windows Media Dynamic Installer','Windows Next Graphics Driver Dynamic update','Windows RT','Windows RT Drivers','Windows Server 2003','Windows Server 2003, Datacenter Edition','Windows Server 2008','Windows Server 2008 R2','Windows Server 2008 Server Manager Dynamic Installer','Windows Server 2012','Windows Server 2012 Language Packs','Windows Server 2012 R2','Windows Server 2012 R2  and later drivers','Windows Server 2012 R2 Drivers','Windows Server 2012 R2 Language Packs','Windows Server 2016','Windows Server 2016 and Later Servicing Drivers','Windows Server Drivers','Windows Server Manager – Windows Server Update Services (WSUS) Dynamic Installer','Windows Server Solutions Best Practices Analyzer 1.0','Windows Server Technical Preview Language Packs','Windows Server 2008 R2','Windows Small Business Server 2003','Windows Small Business Server 2008','Windows Small Business Server 2008 Migration Preparation Tool','Windows Small Business Server 2011 Standard','Windows Ultimate Extras','Windows XP Embedded','Works 6-9 Converter','Writer Installation and Upgrades')

left join v_RA_System_SMSAssignedSites asi on m.ResourceID=asi.ResourceID

inner join v_StateNames as vstate on vstate.StateID = ucs.Status and TopicType = 500

where --cm.CollectionID=@CollID and 

m.Name0 in ('AXAPRDCGY001','AXAPRDCGY002','AXAPRDCGY003','AXAPRDCGY004','AXAPRDCGY005','AXAPRDCGY006','AXAPRDCGY007','AXAPRDCGY008','AXAPRDCGY009','AXAPRDCGY010','AXAPRDCGY021','AXAPRDCGY022','AXAPRDCGY023','AXAPRDCGY024','AXAPRDCGY025','AXAPRDCGY026','AXAPRDCGY027','AXAPRDCGY028','AXAPRDCGY029','AXAPRDCGY030','AXAPRDCGY031','AXAPRDCGY032','AXDPRDCGY001B','AXDPRDCGY001A','AXDPRDCGY002B','AXDPRDCGY002A','AXMPRDCGY001','AXWPRDCGY001','AXWPRDCGY002','AXWPRDCGY003','AXWPRDCGY004','AXWPRDCGY005','AXWPRDCGY006','AXWPRDCGY007','AXWPRDCGY008','AXWPRDCGY009','AXWPRDCGY010','AXWTRNCGY010','AXATRNCGY010','AXDTRNCGY010','AXWDEVCGY040','AXWDEVCGY041','AXWDEVCGY042','AXWDEVCGY043','AXWDEVCGY044','AXWDEVCGY045','AXWDEVCGY046','AXWDEVCGY047','AXWDEVCGY048','AXWDEVCGY049','AXWDEVCGY050','AXWDEVCGY011','AXADEVCGY011','AXADEVCGY001','AXDDEVCGY001 ','TFSDEVCGY001','AXWQUTCGY001','AXWQUTCGY002','AXWQUTCGY003','AXWQUTCGY004','AXWQUTCGY005','AXAQUTCGY001','AXAQUTCGY002','AXAQUTCGY003','AXAQUTCGY004','AXAQUTCGY005','AXWQUTCGY021','AXWQUTCGY022','AXWQUTCGY023','AXWQUTCGY024','AXWQUTCGY025','AXDQUTCGY001a','AXDQUTCGY001b','AXDQUTCGY002a','AXDQUTCGY002b','AXWDEVCGY056','AXWDEVCGY055','AXWDEVCGY054','AXWDEVCGY051','AXWDEVCGY052','AXADEVCGY110','AXWDEVCGY110','AXADEVCGY111','AXDDEVCGY101','AXADEVCGY100','AXADEVCGY201','AXADEVCGY202','AXADEVCGY203','AXADEVCGY204','AXWDEVCGY201','AXDDEVCGY100')

and ucs.CI_ID in (select CI_ID

from v_UpdateInfo ui

WHERE ( ui.ArticleID in ('4480965','4480978','4480966','4480961','4481486','4481484','4481482','4481480','4480968','4480970','4480975','4480963','4480957','4480960','4480972','4480964','4481487','4481485','4481483','4481481','4480979','APSB19-01','4461624','4471389','2553332','4461614','4461617','3172522','4461537','4022162','4461535','2596760','4461623','4461595','4461601','4461589','4461591','4461596','4461612','4461620','4461625','4461594','4461543','4476698','4476755','4461635','4462112') ) 

and CIType_ID in (1,8))  







use CM_CAS



declare @CollID char(50)

select @CollID = CollectionID

from v_Collection

where CollectionID='CAS00846'


select

--ucs.ResourceID,

m.Name0 as ServerName,

ui.BulletinID as Bulletin, 

ui.ArticleID as Article, 

ui.Title as Title,

--ucs.Status,


case vstate.StateName

when 'Update is installed' then '1'

Else '0'

End as 'Installed',

case vstate.StateName

when 'Update is required' then '1'

Else '0'

End as 'Required',

case vstate.StateName

when 'Detection state unknown' then '1'

Else '0'

End as 'Unknown',

case vstate.StateName

when 'Update is not required' then '1'

Else '0'

End as 'Not Required'

--m.User_Domain0+'\'+User_Name0 as LastLoggedOnUser,

--ucs.Status as UpdateStatus

--asi.SMS_Assigned_Sites0 as AssignedSite,

--m.Client_Version0 as ClientVersion,

--LastStatusCheckTime as LastStateReceived,

--LastStatusChangeTime as LastStateChange

from v_Update_ComplianceStatusAll  ucs

join v_UpdateInfo  ui on ucs.CI_ID=ui.CI_ID

join v_ClientCollectionMembers  cm on cm.ResourceID=ucs.ResourceID

join v_R_System m on m.ResourceType=5 and m.ResourceID=ucs.ResourceID and isnull(m.Obsolete0,0)<>1

join v_CICategories_All CATALL on CATALL.CI_ID=UCS.CI_ID

join v_CategoryInfo CATInfo on CATALL.CategoryInstance_UniqueID = CatInfo.CategoryInstance_UniqueID and CatInfo.CategoryTypeName = 'Product' 

AND CATInfo.CategoryInstanceName in ('Active Directory Rights Management Services Client 2.0','Adobe Acrobat','Adobe Flash Player','Adobe Reader','Antigen for Exchange/SMTP','ASP.NET Web Frameworks','Assistant de connexion Microsoft Online Services','Azure File Sync agent updates for Windows Server 2012 R2','Azure File Sync agent updates for Windows Server 2016','Azure Information Protection Client Dogfood Tool','Bing Bar','BizTalk Server 2002','BizTalk Server 2006R2','BizTalk Server 2009','BizTalk Server 2013','CAPICOM','Category for System Center Online Client','Complément Office Live','Compute Cluster Pack','Data Protection Manager 2006','Device Health','Dictionary Updates for Microsoft IMEs','Exchange 2000 Server','Exchange Server 2003','Exchange Server 2007','Exchange Server 2007 and Above Anti-spam','Exchange Server 2010','Exchange Server 2013','Exchange Server 2016','Expression Design 1','Expression Design 2','Expression Design 3','Expression Design 4','Expression Media 2','Expression Media V1','Expression Web 3','Expression Web 4','Firewall Client for ISA Server','Forefront Client Security','Forefront Endpoint Protection 2010','Forefront Identity Manager 2010','Forefront Identity Manager 2010 R2','Forefront Protection Category','Forefront Server Security Category','Forefront Threat Management Gateway, Definition Updates for HTTP Malware Inspection','Forefront TMG','Forefront TMG MBE','HealthVault Connection Center','HealthVault Connection Center Upgrades','Host Integration Server 2000','Host Integration Server 2004','Host Integration Server 2006','Host Integration Server 2009','Host Integration Server 2010','HPC Pack 2008','Installation de Windows Live OneCare Contrôle parental','Installation et mises à niveau de la Galerie de photos Windows Live','Installation et mises à niveau de Windows Live Writer','Internet Security and Acceleration Server 2004','Internet Security and Acceleration Server 2006','Locally published packages','Lokal veröffentlichte Pakete','Microsoft Advanced Threat Analytics','Microsoft Application Virtualization 4.5','Microsoft Application Virtualization 4.6','Microsoft Application Virtualization 5.0','Microsoft Azure Information Protection Client','Microsoft Azure Site Recovery Provider','Microsoft BitLocker Administration and Monitoring v1','Microsoft Dynamics CRM 2011','Microsoft Dynamics CRM 2011 SHS','Microsoft Dynamics CRM 2013','Microsoft Dynamics CRM 2015','Microsoft Dynamics CRM 2016','Microsoft Dynamics CRM 2016 SHS','Microsoft Lync 2010','Microsoft Lync Server 2010','Microsoft Lync Server 2013','Microsoft Monitoring Agent','Microsoft Online Services Sign-In Assistant','Microsoft Research AutoCollage 2008','Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010','Microsoft SQL Server 2012','Microsoft SQL Server 2014','Microsoft SQL Server 2016','Microsoft SQL Server 2017','Microsoft SQL Server Management Studio v17','Microsoft StreamInsight V1.0','Microsoft System Center DPM 2010','Microsoft System Center Virtual Machine Manager 2007','Microsoft System Center Virtual Machine Manager 2008','Microsoft Works 8','Microsoft Works 9','MS Security Essentials','Network Monitor 3','New Dictionaries for Microsoft IMEs','Office 2002/XP','Office 2003','Office 2007','Office 2010','Office 2013','Office 2016','Office 365 Client','Office Communications Server 2007','Office Communications Server 2007 R2','Office Communicator 2007 R2','Office Live Add-in','OneCare Family Safety Installation','OOBE ZDP','Packages publiés localement','Pakker som er publisert lokalt','Paquetes publicados localmente','Photo Gallery Installation and Upgrades','Report Viewer 2005','Report Viewer 2008','Report Viewer 2010','Search Enhancement Pack','Security Essentials','Service Bus for Windows Server 1.1','Services AD RMS (Active Directory Rights Management Services) Client 2.0','Silverlight','Skype for Business Server 2015','Skype for Business Server 2015, SmartSetup','Skype for Windows','SQL Server 2000','SQL Server 2005','SQL Server 2008','SQL Server 2008 R2','SQL Server 2012 Product Updates for Setup','SQL Server 2014-2016 Product Updates for Setup','SQL Server Feature Pack','System Center 2012 - App Controller','System Center 2012 - Data Protection Manager','System Center 2012 - Operations Manager','System Center 2012 - Orchestrator','System Center 2012 - Virtual Machine Manager','System Center 2012 R2 - Data Protection Manager','System Center 2012 R2 - Operations Manager','System Center 2012 R2 - Orchestrator','System Center 2012 R2 - Virtual Machine Manager','System Center 2012 SP1 - App Controller','System Center 2012 SP1 - Data Protection Manager','System Center 2012 SP1 - Operation Manager','System Center 2012 SP1 - Virtual Machine Manager','System Center 2016 - Data Protection Manager','System Center 2016 - Operations Manager','System Center 2016 - Orchestrator','System Center 2016 - Virtual Machine Manager','System Center Advisor','System Center Configuration Manager 2007','Systems Management Server 2003','Threat Management Gateway Definition Updates for Network Inspection System','TMG Firewall Client','Virtual PC','Virtual Server','Visual Studio 2005','Visual Studio 2008','Visual Studio 2010','Visual Studio 2010 Tools for Office Runtime','Visual Studio 2010 Tools for Office Runtime','Visual Studio 2012','Visual Studio 2013','Windows 10 and later upgrade & servicing drivers','Windows 10 Feature On Demand','Windows 10 GDR-DU','Windows 2000','Windows 8 Embedded','Windows Admin Center','Windows Azure Pack: Admin API','Windows Azure Pack: Admin Authentication Site','Windows Azure Pack: Admin Site','Windows Azure Pack: Configuration Site','Windows Azure Pack: Microsoft Best Practice Analyzer','Windows Azure Pack: Monitoring Extension','Windows Azure Pack: MySQL Extension','Windows Azure Pack: PowerShell API','Windows Azure Pack: SQL Server Extension','Windows Azure Pack: Tenant API','Windows Azure Pack: Tenant Authentication Site','Windows Azure Pack: Tenant Public API','Windows Azure Pack: Tenant Site','Windows Azure Pack: Usage Extension','Windows Azure Pack: Web App Gallery Extension','Windows Azure Pack: Web Sites','Windows Defender','Windows Dictionary Updates','Windows Essential Business Server 2008','Windows Essential Business Server 2008 Setup Updates','Windows Essential Business Server Preinstallation Tools','Windows GDR-Dynamic Update','Windows Internet Explorer 7 Dynamic Installer','Windows Internet Explorer 8 Dynamic Installer','Windows Live','Windows Live','Windows Live Toolbar','Windows Media Dynamic Installer','Windows Next Graphics Driver Dynamic update','Windows RT','Windows RT Drivers','Windows Server 2003','Windows Server 2003, Datacenter Edition','Windows Server 2008','Windows Server 2008 R2','Windows Server 2008 Server Manager Dynamic Installer','Windows Server 2012','Windows Server 2012 Language Packs','Windows Server 2012 R2','Windows Server 2012 R2  and later drivers','Windows Server 2012 R2 Drivers','Windows Server 2012 R2 Language Packs','Windows Server 2016','Windows Server 2016 and Later Servicing Drivers','Windows Server Drivers','Windows Server Manager – Windows Server Update Services (WSUS) Dynamic Installer','Windows Server Solutions Best Practices Analyzer 1.0','Windows Server Technical Preview Language Packs','Windows Server 2008 R2','Windows Small Business Server 2003','Windows Small Business Server 2008','Windows Small Business Server 2008 Migration Preparation Tool','Windows Small Business Server 2011 Standard','Windows Ultimate Extras','Windows XP Embedded','Works 6-9 Converter','Writer Installation and Upgrades')

left join v_RA_System_SMSAssignedSites asi on m.ResourceID=asi.ResourceID

inner join v_StateNames as vstate on vstate.StateID = ucs.Status and TopicType = 500

where cm.CollectionID=@CollID 

and ucs.CI_ID in (select CI_ID

from v_UpdateInfo ui

WHERE ( ui.ArticleID in ('4073065','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4467694','4466536','4467701','4467701','4467680','4467680','4465659','4467691','4465659','4467691','4465660','4467696','4465660','4467696','4465661','4467686','4465661','4467686','4465661','4467686','4465663','4467702','4465663','4467702','4465663','4467702','4465664','4467708','4465664','4467708','4465664','4467708','4466536','4467107','4466536','4467107','4466536','4467697','4466536','4467697','4467703','4467697','4467703','4466536','4467107','4466536','4467697','4467703','4465659','4467691','4465664','4467708','4466536','4467706','4466536','4467706','4467680','4467680','4465659','4465659','4467691','4465659','4467691','4465660','4467696','4465660','4467696','4465661','4467686','4465661','4467686','4465661','4467686','4465663','4467702','4465663','4467702','4465663','4467702','4465664','4467708','4465664','4467708','4465664','4467708','4465659','4467691','4465664','4467708','4467675','4011190','4461530','4461530','4461488','4461488','4461488','4461503','4461503','4461519','4461487','4461487','4461487','4461487','3114565','4032218','4461524','3114565','4032218','4461524','4022237','4022237','4022237','4022232','4022232','4461518','4461527','4092473','4461529','4461529','4461486','4461486','4461486','4461506','4461506','4022147','4022147','4461478','4461478','4461489','4461489','4461483','4461501','4461511','4461520','4461513','4461526','4461526','4461485','4461485','4461485','4461504','4461504','4461473','4461473','4461473','4461473','4467680','4467680','4465659','4467691','4465659','4467691','4465660','4467696','4465660','4467696','4465661','4467686','4465661','4467686','4465661','4467686','4465663','4467702','4465663','4467702','4467702','4465663','4467702','4465664','4467708','4465664','4467708','4465664','4467708','3177467','4467106','4467107','3177467','4467106','4467107','4467697','4467703','4467697','4467703','4467697','4467703','4467700','4467706','4467700','4467706','4467700','4467706','4467700','4467706','4467700','4467706','3177467','4467106','4467107','3177467','4467106','4467107','3177467','4467106','4467107','4467678','4467701','4467678','4467701','4467697','4467703','4467697','4467703','4465659','4467691','4465659','4467691','4465664','4467708','4465664','4467708','4465661','4467686','4465663','4467702') ) 

and CIType_ID in (1,8))  


Comments

Post a Comment

Popular posts from this blog

powershell script to export applications and its requirement in weird way

 $SiteCode = 'DEV' $sccmserver = 'covid-100' $Applications = Get-WmiObject -Namespace "root\SMS\Site_$SiteCode" -Class SMS_Application -ComputerName $SCCMServer foreach ($apps in $Applications){ $DTS = Get-CMDeploymentType -ApplicationName $apps.LocalizedDisplayName foreach ($DT in $DTS){ $req = Get-CMDeploymentTypeRequirement -InputObject $DT "$($apps.LocalizedDisplayName) ; $($req.name)" | Out-File -FilePath "C:\Users\username\Desktop\dtsssss.csv" -Append } }
Read more

get-allrecent udpated logs

 # winrm service should be running on the machine where script is running for ciminstance query else change it to get-wmiobject # Get-Allrecentlogs -computername 'localhost' -startdateandtime '01/01/2005 00:00' -enddateandtime '07/07/2020 00:00' -logfilextension 'flv' # Get-Allrecentlogs -computername 'localhost' -startdateandtime '01/01/2005 00:00' -enddateandtime '07/07/2020 00:00' -logfilextension 'mp4' Function Get-Allrecentlogs{ param([string]$computername = 'localhost', [datetime]$startdateandtime, [datetime]$enddateandtime, [string]$logfilextension = 'log' ) if ($computername -eq 'localhost')     {     $location = (Get-CimInstance -ClassName Win32_logicaldisk -Filter "DriveTYpe = '3'").DeviceId     } else { $shares = Get-CimInstance -ComputerName $computername -ClassName Win32_share | Where-Object {$_.Path -match '^\w{1}:\\$'} [System.Collections.ArrayList]$location ...
Read more

TASK sequence duration report

 SELECT   sys.Netbios_Name0 AS [Machine Name],   sys.User_Name0,   os.Caption0[Operating System],  os.InstallDate0 [OS installed date],  os.BuildNumber0[OS build number],  tsstart.Time as [TS StartTime],   tslast.Time as [TS EndTime],   CASE     WHEN DATEDIFF(dd, tsstart.Time, tslast.Time) > 0 THEN     CAST(DATEDIFF(dd, tsstart.Time, tslast.Time) AS VARCHAR(2)) + 'd '    ELSE ''   END +     convert(char(8),DATEDIFF(MINUTE, tsstart.Time , tslast.Time)) AS [Duration in Minutes] FROM  (   SELECT    MAX(ExecutionTime) AS Time,    AdvertisementID,    ResourceID   FROM v_TaskExecutionStatus  WHERE LastStatusMessageID = 11140 AND Step = 0   GROUP BY AdvertisementID,ResourceID  ) tsstart    INNER JOIN  (   SELECT    AdvertisementID,    Res...
Read more