---for a specific Advertisement status for last 3 Days… if we include the Set @AdvName = '%' line then and remove the above line in red color will show for all advertisements with last 3 days status
-- To include all we need to give % in SQL this is a tip
Declare @AdvName Varchar(256)
Set @AdvName = 'Lync_2010_Full_Install'
---Set @AdvName = '%'
Select adv.AdvertisementName
, adv.AdvertisementID
, s.Host
, LastAcceptanceMessageIDName
, LastAcceptanceStateName
, LastAcceptanceStatusTime
, LastStatusMessageIDName
, LastStateName
, LastStatusTime
, LastExecutionResult
From (
Select AdvertisementName
, AdvertisementID
From dbo.v_AdvertisementInfo
Where AdvertisementName Like @AdvName
) As adv
Join (
Select AdvertisementID
, ResourceID
, LastAcceptanceMessageIDName
, LastAcceptanceStateName
, LastAcceptanceStatusTime
, LastStatusMessageIDName
, LastStateName
, LastStatusTime
, LastExecutionResult
From dbo.v_ClientAdvertisementStatus
Where LastStatusTime >= DateAdd(d,-3,GetDate())
) As cas
On adv.AdvertisementID = cas.AdvertisementID
Join (
Select ResourceID
, Netbios_Name0 As Host
From dbo.v_R_System
Where Client0 = 1
And Active0 = 1
And Obsolete0 = 0
) As s
On cas.ResourceID = s.ResourceID
Order By
AdvertisementName
, Host
