SELECT
     fd.FoundDeviceID
    ,fd.DeviceID    
    ,fd.SuspectedOS 
    ,fd.Reason      
    ,prd.Name         [product_type]
    ,fdP.Port       
    ,fdP.Banner     
    ,fdP.IPAddress  
    ,fdL.FoundHostnameOrIP
    ,fdL.DetectedPorts
    ,fdL.ScanCount
    ,fdL.LastScanDate
    ,fdL.LatestOutcome
    ,fdL.ScannedDeviceName
    ,ch.AttemptDate
    ,ch.Port
    ,ch.[Message]
    ,ch.[RawMessage]
    ,ch.Connection
    ,oc.Name        [Outcome]
    ,pro.[Name]     [Protocol]
 
FROM                model.t_FoundDevice             fd
    INNER JOIN      model.t_product                 prd ON fd.ProductId = prd.ProductID
    LEFT OUTER JOIN model.v_FoundDeviceTCPPort      fdP ON fd.FoundDeviceID = fdP.FoundDeviceID
    LEFT OUTER JOIN model.v_ProjectFoundDeviceList  fdL ON fd.foundDeviceID = fdL.foundDeviceID
    LEFT OUTER JOIN
        (
        SELECT
             ah.ObjectId
            ,ah.JobID
        FROM history.t_ArtifactHistory ah
        WHERE
            ah.objectType = N'FoundDevice'
        )
    [hst]                                               ON fd.FoundDeviceID = [hst].ObjectID
    LEFT OUTER JOIN history.t_ConnectionHistory     ch  ON [hst].JobId = ch.JobId
    LEFT OUTER JOIN config.t_Outcome                oc  ON ch.OutcomeID = oc.OutcomeID
    LEFT OUTER JOIN config.t_Protocol               pro ON ch.ProtocolId = pro.protocolId
-- ---- If required, put a ProjectID selection HERE ----
-- WHERE fdL.ProjectID = N
-- -----------------------------------------------  ----
ORDER BY
     fd.FoundDeviceID
    ,ch.AttemptDate

WITH unscanned AS (
	SELECT DISTINCT [HostnameOrIP]
	FROM [history].[v_DiagnosticsDeviceList]
	WHERE Status = 'Unscanned'
	  AND ProjectID = 1
)
SELECT jobProj.ProjectID
      , p.Name AS Protocol
      , o.Name AS Outcome
      , ch.AttemptDate
      , ch.IPAddress
      , ch.Port
      , c.label as Credential
FROM history.t_connectionhistory ch
  INNER JOIN jobs.t_JobLocationProjectIPRange AS jobProj ON jobProj.JobID = ch.JobID
  INNER JOIN config.t_Protocol p ON ch.ProtocolID = p.ProtocolID
  INNER JOIN config.t_Outcome o ON o.OutcomeID = ch.OutcomeID
  INNER JOIN config.t_Credential c ON c.CredentialID = CH.CredentialID
WHERE ch.OutcomeID = 3
  AND ch.IPAddress IN (SELECT HostnameOrIP FROM unscanned)
-- === Change this line ==== --
   AND jobProj.ProjectID = 1
-- ========================= --
ORDER BY AttemptDate DESC