DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10392
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[N_NotAssociated] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; /*
 10/26/21, OVK: Survey query to find unassociated sites with All Webs category enabled
 11/3/21, OVK: Minimal row presence will suffice to identify CS & count to prioritize
 11/18/21, OVK: Dropping App_Client table per feedback
*/

-- All Webs, all pseudo-clients
select COUNT(D.userGUID) as N_NotAssociated
from 
	APP_Application SC WITH (NOLOCK)
	JOIN APP_CloudAppUserDetails D0 WITH (NOLOCK) ON D0.subclientid=SC.id AND D0.modified=0 AND D0.discoverByType=9 AND D0.status=0
	JOIN APP_CloudAppUserDetails D WITH (NOLOCK) ON D.subClientId=SC.id AND D.modified=0 AND D.discoverByType=6 AND D.flags&2=2 AND d.status=0
where SC.appTypeId=78	
having COUNT(D.userGUID)>0 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [N_NotAssociated] AS '@N_NotAssociated' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10392'), type
)
  FOR XML PATH ('SurveyResults') 
)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
SET @outputXML = (SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA'))