All PCs with particular application last used date 2

All Software applications required deployments status within 30 days 2

All Software applications available deployments status within 30 days 3

All Software applications simulate deployments status within 30 days 3

All Software applications required deployments status within 5 days 5

All Software applications deployments status within 30 days 5

All Deployments status for Specific Application 7

Deployment Detailed status for specific application with specific collection 8

Deployment Detailed status for specific application 10

All Applications Deployments Status for Specific Computers 12

Deployments status for specific applications 12

All Application Installed on Specific Collection 13

All Software applications deployments status 15

All PCs with particular application last used date

Declare @Monthold int

set @Monthold = 2

SELECT DISTINCT SYS.Netbios_Name0 as Name,

SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, max(apps.LastUsedTime0) as LastUsedTime,

SYS.User_Name0 as Login,

CSYS.Manufacturer0 as Manufacturer, CSYS.Model0 as Model, BIOS.SerialNumber0 as SN,

MAX(IPSub.IP_Subnets0) as ‘Subnet’, sys.AD_Site_Name0 as ADSite, MAX(SYSOU.System_OU_Name0) as ‘OU’

FROM v_GS_SoftwareFile SF

join v_R_System SYS on SYS.ResourceID = SF.ResourceID

LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID

LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID

LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID

LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID

LEFT JOIN v_RA_System_SystemOUName SYSOU on SYS.ResourceID=SYSOU.ResourceID

LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0

LEFT JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID

LEFT JOIN (select * from v_GS_CCM_RECENTLY_USED_APPS where ExplorerFileName0 = ‘notepad.exe’) APPS on SYS.ResourceID = APPS.ResourceID

Where SF.FileName LIKE ‘notepad.exe’

GROUP BY SYS.Netbios_Name0, –apps.LastUsedTime0,

SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath,

SYS.User_Name0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0,

sys.AD_Site_Name0

HAVING max(apps.LastUsedTime0) < dateadd(month, -(@Monthold), dateadd(day,0,datediff(day,0,getdate()))) OR max(apps.LastUsedTime0) IS NULL

ORDER BY SYS.Netbios_Name0

All Software applications required deployments status within 30 days

Declare @SoftwareAppDeploymentsReportNeededDays as integer

Set @SoftwareAppDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AssignmentName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Vaa.ApplicationName as ‘ApplicationName’,

CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,

Ds.CollectionName as ‘CollectionName’,

CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,

Ds.DeploymentTime as ‘AvailableTime’,

Ds.EnforcementDeadline as ‘RequiredTime’,

Ds.NumberTotal as ‘Target’,

Ds.NumberSuccess as ‘Success’,

Ds.NumberInProgress as ‘Progress’,

Ds.NumberErrors as ‘Errors’,

Ds.NumberOther as ‘ReqNotMet’,

Ds.NumberUnknown as ‘Unknown’,

case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then ‘100’ Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,

DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,

DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’

from v_DeploymentSummary Ds

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 1

and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Software applications available deployments status within 30 days

Declare @SoftwareAppDeploymentsReportNeededDays as integer

Set @SoftwareAppDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AssignmentName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Vaa.ApplicationName as ‘ApplicationName’,

CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,

Ds.CollectionName as ‘CollectionName’,

CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,

Ds.DeploymentTime as ‘AvailableTime’,

Ds.EnforcementDeadline as ‘RequiredTime’,

Ds.NumberTotal as ‘Target’,

Ds.NumberSuccess as ‘Success’,

Ds.NumberInProgress as ‘Progress’,

Ds.NumberErrors as ‘Errors’,

Ds.NumberOther as ‘ReqNotMet’,

Ds.NumberUnknown as ‘Unknown’,

case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then ‘100’ Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,

DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,

DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’

from v_DeploymentSummary Ds

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 2

and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Software applications simulate deployments status within 30 days

Declare @SoftwareAppDeploymentsReportNeededDays as integer

Set @SoftwareAppDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AssignmentName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Vaa.ApplicationName as ‘ApplicationName’,

CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,

Ds.CollectionName as ‘CollectionName’,

CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ when Ds.DeploymentIntent = 3 Then ‘Simulate’ End as ‘Purpose’,

Ds.DeploymentTime as ‘AvailableTime’,

Ds.EnforcementDeadline as ‘RequiredTime’,

Ds.NumberTotal as ‘Target’,

Ds.NumberSuccess as ‘Success’,

Ds.NumberInProgress as ‘Progress’,

Ds.NumberErrors as ‘Errors’,

Ds.NumberOther as ‘ReqNotMet’,

Ds.NumberUnknown as ‘Unknown’,

case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then ‘100’ Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,

DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,

DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’

from v_DeploymentSummary Ds

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 3

and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Software applications required deployments status within 5 days

Declare @CurrentDeploymentsReportNeededDays as integer

Set @CurrentDeploymentsReportNeededDays = 5 –Specify the Days

Select

CONVERT(VARCHAR(11),GETDATE(),106) as ‘Date’,

Right(Ds.CollectionName,3) as ‘Stage’,

Vaa.ApplicationName as ‘ApplicationName’,

CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,

Ds.CollectionName as ‘CollectionName’,

CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,

Ds.DeploymentTime as ‘AvailableTime’,

Ds.EnforcementDeadline as ‘RequiredTime’,

Ds.NumberTotal as ‘Target’,

Ds.NumberSuccess as ‘Success’,

Ds.NumberInProgress as ‘Progress’,

Ds.NumberErrors as ‘Errors’,

Ds.NumberOther as ‘ReqNotMet’,

Ds.NumberUnknown as ‘Unknown’,

case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then ‘100’ Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,

DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘ReqDays’

from v_DeploymentSummary Ds

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 1

and DateDiff(D,Ds.EnforcementDeadline, GetDate()) between 0 and @CurrentDeploymentsReportNeededDays

and Ds.NumberTotal > 0

order by Ds.EnforcementDeadline desc

All Software applications deployments status within 30 days

Declare @AppDeploymentsReportNeededDays as integer

Set @AppDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AssignmentName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Vaa.ApplicationName as ‘ApplicationName’,

CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,

Ds.CollectionName as ‘CollectionName’,

CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ when Ds.DeploymentIntent = 3 Then ‘Simulate’ End as ‘Purpose’,

Ds.DeploymentTime as ‘AvailableTime’,

Ds.EnforcementDeadline as ‘RequiredTime’,

Ds.NumberTotal as ‘Target’,

Ds.NumberSuccess as ‘Success’,

Ds.NumberInProgress as ‘Progress’,

Ds.NumberErrors as ‘Errors’,

Ds.NumberOther as ‘ReqNotMet’,

Ds.NumberUnknown as ‘Unknown’,

case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then ‘100’ Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,

DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,

DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’,

DateDiff(D,Ds.CreationTime, GetDate()) as ‘CreatedDays’,

Vaa.CreationTime as ‘CreationTime’,

Vaa.LastModificationTime as ‘LastModifiedTime’,

Vaa.LastModifiedBy as ‘LastModifiedBy’

from v_DeploymentSummary Ds

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

Where Ds.FeatureType = 1

and Ds.CreationTime > GETDATE()-@AppDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Deployments status for Specific Application

Declare @ApplicationName as Varchar(255)

Set @ApplicationName = ‘Adobe DIO Illustrator Extension 3.0.13.2’ –Specify Application Name

Select

Vaa.AssignmentName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Vaa.ApplicationName as ‘ApplicationName’,

CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,

Ds.CollectionName as ‘CollectionName’,

CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ when Ds.DeploymentIntent = 3 Then ‘Simulate’ End as ‘Purpose’,

Ds.DeploymentTime as ‘AvailableTime’,

Ds.EnforcementDeadline as ‘RequiredTime’,

Ds.NumberTotal as ‘Target’,

Ds.NumberSuccess as ‘Success’,

Ds.NumberInProgress as ‘Progress’,

Ds.NumberErrors as ‘Errors’,

Ds.NumberOther as ‘ReqNotMet’,

Ds.NumberUnknown as ‘Unknown’,

case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then ‘100’ Else (round( (Ds.NumberSuccess + Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,

DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,

DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’,

DateDiff(D,Ds.CreationTime, GetDate()) as ‘CreatedDays’,

Vaa.CreationTime as ‘CreationTime’,

Vaa.LastModificationTime as ‘LastModifiedTime’,

Vaa.LastModifiedBy as ‘LastModifiedBy’

from v_DeploymentSummary Ds

left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

Where Ds.FeatureType = 1

and Vaa.ApplicationName = @ApplicationName

order by Ds.DeploymentTime desc

Deployment Detailed status for specific application with specific collection

Declare @ApplicationName as Varchar(255)

Declare @CollectionName as Varchar(255)

Set @ApplicationName = ‘Adobe Creative-Cloud Design-Standard 2015 (64-bit)’ — Specify Application name

Set @CollectionName = ‘%Adobe Creative-Cloud Design-Standard 2015%’ — Specify Application name

select

aa.ApplicationName as ‘Application Name’,

aa.CollectionName as ‘Target Collection’,

ae.descript as ‘DeploymentTypeName’,

s1.netbios_name0 as ‘ComputerName’,

s1.AD_Site_Name0 as ‘ADSiteName’,

case when ae.AppEnforcementState = 1000 then ‘Success’

when ae.AppEnforcementState = 1001 then ‘Already Compliant’

when ae.AppEnforcementState = 1002 then ‘Simulate Success’

when ae.AppEnforcementState = 2000 then ‘In Progress’

when ae.AppEnforcementState = 2001 then ‘Waiting for Content’

when ae.AppEnforcementState = 2002 then ‘Installing’

when ae.AppEnforcementState = 2003 then ‘Restart to Continue’

when ae.AppEnforcementState = 2004 then ‘Waiting for maintenance window’

when ae.AppEnforcementState = 2005 then ‘Waiting for schedule’

when ae.AppEnforcementState = 2006 then ‘Downloading dependent content’

when ae.AppEnforcementState = 2007 then ‘Installing dependent content’

when ae.AppEnforcementState = 2008 then ‘Restart to complete’

when ae.AppEnforcementState = 2009 then ‘Content downloaded’

when ae.AppEnforcementState = 2010 then ‘Waiting for update’

when ae.AppEnforcementState = 2011 then ‘Waiting for user session reconnect’

when ae.AppEnforcementState = 2012 then ‘Waiting for user logoff’

when ae.AppEnforcementState = 2013 then ‘Waiting for user logon’

when ae.AppEnforcementState = 2014 then ‘Waiting to install’

when ae.AppEnforcementState = 2015 then ‘Waiting retry’

when ae.AppEnforcementState = 2016 then ‘Waiting for presentation mode’

when ae.AppEnforcementState = 2017 then ‘Waiting for Orchestration’

when ae.AppEnforcementState = 2018 then ‘Waiting for network’

when ae.AppEnforcementState = 2019 then ‘Pending App-V Virtual Environment’

when ae.AppEnforcementState = 2020 then ‘Updating App-V Virtual Environment’

when ae.AppEnforcementState = 3000 then ‘Requirements not met’

when ae.AppEnforcementState = 3001 then ‘Host platform not applicable’

when ae.AppEnforcementState = 4000 then ‘Unknown’

when ae.AppEnforcementState = 5000 then ‘Deployment failed’

when ae.AppEnforcementState = 5001 then ‘Evaluation failed’

when ae.AppEnforcementState = 5002 then ‘Deployment failed’

when ae.AppEnforcementState = 5003 then ‘Failed to locate content’

when ae.AppEnforcementState = 5004 then ‘Dependency installation failed’

when ae.AppEnforcementState = 5005 then ‘Failed to download dependent content’

when ae.AppEnforcementState = 5006 then ‘Conflicts with another application deployment’

when ae.AppEnforcementState = 5007 then ‘Waiting retry’

when ae.AppEnforcementState = 5008 then ‘Failed to uninstall superseded deployment type’

when ae.AppEnforcementState = 5009 then ‘Failed to download superseded deployment type’

when ae.AppEnforcementState = 5010 then ‘Failed to updating App-V Virtual Environment’

End as ‘State Message’,

case when ae.AppEnforcementState like ‘10%’ then ‘Success’

when ae.AppEnforcementState like ‘20%’ then ‘Progress’

when ae.AppEnforcementState like ‘30%’ then ‘ReqNotMet’

when ae.AppEnforcementState like ‘40%’ then ‘Unknown’

when ae.AppEnforcementState like ‘50%’ then ‘Failed’

End as ‘Status’,

LastComplianceMessageTime as ‘LastMessageTime’

from v_R_System_Valid s1

join vAppDTDeploymentResultsPerClient ae on ae.ResourceID = s1.ResourceID

join v_CICurrentComplianceStatus ci2 on ci2.CI_ID = ae.CI_ID AND ci2.ResourceID=s1.ResourceID

join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID

where ae.AppEnforcementState is not null

and (aa.ApplicationName = @ApplicationName

and aa.CollectionName like @CollectionName)

order by ae.AppEnforcementState, LastComplianceMessageTime Desc

Deployment Detailed status for specific application