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
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
13 Experts available now in Live!