Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Use this ip for input: 10.124.208.208. Declare @DD_INPUT as varchar(128) Set @DD_INPUT = '10.124.208.208'--@INPUT Declare @DD_SCANDATE as Date set @DD_SCANDATE = (Select distinct top 1 sd.ScanDate as [ScanDate] from dflt.scandates as sd Where sd.id in (Select distinct top 1 scandateid from dflt.device order by scandateid desc) order by [ScanDate] desc) ;WITH CTE_SCANNED as ( SELECT count(Case when [Source type] = 'Nessus Scan' then 1 end) as [TotalScanned], count(case when [Source type] = 'Informational' then 1 end) as [Totalbad], ScanDate, [Source type],[Branch Region], [Branch Network], Branch From ( SELECT Distinct dsfs.[DeviceID] as [DeviceID], sd.ScanDate, case when dsfs.pluginsource = 19506 then 'Nessus Scan' else 'Informational' end as [Source type], sos.OSName as [OS] ,FIP.[Branch Region], FIP.[Branch Network], FIP.Branch FROM [FODW_PVT].[Dflt].[Device_ScanInfo_Facts] as dsfs INNER JOIN (Select top 4 ID as ScandateID, Scandate from dflt.scandates order by [ScanDate] desc) as sd on sd.ScandateID = dsfs.ScandateID INNER JOIN [FODW_PVT].[Dflt].[Device] as d on d.deviceid = dsfs.deviceid inner JOIN [Dflt].[SystemsOS] as sos on d.OSID = sos.OSID INNER JOIN dflt.Device_IPLong_FacilityIPRange sip on d.IPlong = sip.IPLong INNER JOIN dflt.FacilityIPRange as FIP on (sip.FacilityIPRangeID = FIP.ID) WHERE (d.[IPAddress] = @DD_INPUT OR d.[Hostname]=@DD_INPUT) AND dsfs.FactName in ( 'SMB Cannot Access Windows Registry', 'Authentication Failure', 'Nessus Windows Scan Performed Non Admin', 'SMB Registry Not Fully Accessible', 'Scan Start Date') AND dsfs.pluginsource in (26917, 21745, 24786, 10428, 19506) ) as X group by Scandate, [Branch Region], [Branch Network], Branch, [Source type] ) SELECT Sum(TotalScanned) as [TotalScanned], Sum(Totalbad) as [Totalbad], cs.ScanDate, [Branch Region], [Branch Network], Branch FROM CTE_SCANNED as cs GROUP BY cs.Scandate, [Branch Region], [Branch Network], Branch UNION ALL SELECT '0' as [TotalScanned] --In VS, 2 = No , '0' as [Totalbad] --In VS, 2 = N/A , sd.ScanDate,'Null' as [Branch Region], 'Null' as [Branch Network], 'Null' as Branch FROM (Select Top 4 ScanDate from dflt.ScanDates where scandate <= @DD_SCANDATE order by ScanDate Desc) as sd LEFT JOIN (SELECT distinct top 4 Scandate from CTE_SCANNED where scandate <= @DD_SCANDATE ) as cs on sd.ScanDate = cs.ScanDate WHERE cs.ScanDate is NULL