------------------------------------------------------------------
DECLARE @RadiaTimeWindow int SET @RadiaTimeWindow = 61
------------------------------------------------------------------

DECLARE @EncryptedLaptopCount Decimal(6) SET @EncryptedLaptopCount = 
(  select count(rdc.device_id) 
   from radia.safewayconfig as rsc, radia.deviceconfig as rdc, radia.DeviceAservice as rda
   where rsc.device_id = rdc.device_id and rdc.device_id = rda.device_id
      and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
      and rsc.machine_type like '%BACKSTAGE%'
      and rdc.computer_type = 'LAPTOP'
      and rda.zobjname = 'XP_CREDANT_GUARDIAN'   )

DECLARE @LaptopPassingAvg Decimal(6,2) SET @LaptopPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
       and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('LAPTOP')
   and rsc.machine_type like 'BACKSTAGE%')

DECLARE @LaptopWithCredantPassingAvg Decimal(6,2) SET @LaptopWithCredantPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
       and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('LAPTOP')
   and rsc.machine_type like 'BACKSTAGE%'
   and rdc.device_id in ( select device_id from radia.DeviceAservice where zobjname =  'XP_CREDANT_GUARDIAN' ))

DECLARE @LaptopWithOutCredantPassingAvg Decimal(6,2) SET @LaptopWithOutCredantPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
       and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('LAPTOP')
   and rsc.machine_type like 'BACKSTAGE%'
   and rdc.device_id NOT in ( select device_id from radia.DeviceAservice where zobjname =  'XP_CREDANT_GUARDIAN' ))

DECLARE @EncryptedDesktopCount Decimal(6) SET @EncryptedDesktopCount = 
(  select count(rdc.device_id) 
   from radia.safewayconfig as rsc, radia.deviceconfig as rdc, radia.DeviceAservice as rda
   where rsc.device_id = rdc.device_id and rdc.device_id = rda.device_id
      and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
      and rsc.machine_type like '%BACKSTAGE%'
      and rdc.computer_type = 'DESKTOP'
      and rda.zobjname = 'XP_CREDANT_GUARDIAN'   )

DECLARE @DesktopPassingAvg Decimal(6,2) SET @DesktopPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
      and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('DESKTOP')
   and rsc.machine_type like 'BACKSTAGE%')

DECLARE @DesktopWithCredantPassingAvg Decimal(6,2) SET @DesktopWithCredantPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
      and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('DESKTOP')
   and rsc.machine_type like 'BACKSTAGE%'
   and rdc.device_id in ( select device_id from radia.DeviceAservice where zobjname =  'XP_CREDANT_GUARDIAN' ))

DECLARE @DesktopWithOutCredantPassingAvg Decimal(6,2) SET @DesktopWithOutCredantPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
      and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('DESKTOP')
   and rsc.machine_type like 'BACKSTAGE%'
   and rdc.device_id NOT in ( select device_id from radia.DeviceAservice where zobjname =  'XP_CREDANT_GUARDIAN' ))

DECLARE @TotalLaptopCount Decimal(6) SET @TotalLaptopCount = 
(  select count(rdc.device_id) 
   from radia.safewayconfig as rsc, radia.deviceconfig as rdc
   where rsc.device_id = rdc.device_id
      and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
      and rsc.machine_type like '%BACKSTAGE%'
      and rdc.computer_type = 'LAPTOP'    )

DECLARE @TotalDesktopCount Decimal(6) SET @TotalDesktopCount = 
(  select count(rdc.device_id) 
   from radia.safewayconfig as rsc, radia.deviceconfig as rdc
   where rsc.device_id = rdc.device_id
      and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
      and rsc.machine_type like '%BACKSTAGE%'
      and rdc.computer_type = 'DESKTOP'   )

DECLARE @LaptopDesktopPassingAvg Decimal(6,2) SET @LaptopDesktopPassingAvg =   
(  select avg(CASE WHEN 
   CAST(rdc.memory AS INT) > 995 
      and ( CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1850  
        or rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */ )
      and rsc.os_ver = 'XP' 
   THEN CAST(1 as decimal(1,0)) ELSE CAST(0 as decimal(1,0)) END)  * 100  as [OK All %]
   from radia.deviceconfig as rdc, radia.safewayconfig as rsc, radia.rWin32_ComputerSystem as rcs
   where rdc.device_id = rsc.device_id and rsc.device_id = rcs.wName
   and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
   and rdc.computer_type in ('DESKTOP', 'LAPTOP')
   and rsc.machine_type like 'BACKSTAGE%')


DECLARE @NotEncryptedLaptopCount Decimal(6,0) SET @NotEncryptedLaptopCount = @TotalLaptopCount - @EncryptedLaptopCount
DECLARE @NotEncryptedDesktopCount Decimal(6,0) SET @NotEncryptedDesktopCount = @TotalDesktopCount - @EncryptedDesktopCount
DECLARE @NotEncryptedLtDtCount Decimal(6,0) SET @NotEncryptedLtDtCount = @NotEncryptedLaptopCount + @NotEncryptedDesktopCount
DECLARE @EncryptedLtRatio Decimal(6,2) SET @EncryptedLtRatio = (@EncryptedLaptopCount / @TotalLaptopCount) * 100
DECLARE @EncryptedDtRatio Decimal(6,2) SET @EncryptedDtRatio = (@EncryptedDesktopCount / @TotalDesktopCount) * 100
DECLARE @EncryptedLtDtRatio Decimal(6,2) SET @EncryptedLtDtRatio = ((@EncryptedDesktopCount + @EncryptedLaptopCount) / (@TotalDesktopCount + @TotalLaptopCount)) * 100

select '% Qualified' [Min HW&SW Test], @LaptopWithCredantPassingAvg [% LT + Credant], @LaptopWithOutCredantPassingAvg [%LT - Credant], @DesktopWithCredantPassingAvg [%DT + Credant], @DesktopWithOutCredantPassingAvg [%DT - Credant]
select 'LAPTOP'  Chassis, @EncryptedLaptopCount Encrypted,  @NotEncryptedLaptopCount  [Not Encrypted], @TotalLaptopCount Total,  @EncryptedLtRatio [% Encrypted], @LaptopPassingAvg  [% Qualified]
select 'DESKTOP' Chassis, @EncryptedDesktopCount Encrypted, @NotEncryptedDesktopCount [Not Encrypted], @TotalDesktopCount Total, @EncryptedDtRatio [% Encrypted], @DesktopPassingAvg [% Qualified]
select 'BOTH'    Chassis, (@EncryptedDesktopCount + @EncryptedLaptopCount) Encrypted, @NotEncryptedLtDtCount [Not Encrypted],
                          (@TotalDesktopCount + @TotalLaptopCount) Total, @EncryptedLtDtRatio [% Encrypted], @LaptopDesktopPassingAvg [% Qualified]




-- Count all Credant Backstage Computers, by LOCATION
select 'Device Count' [Location],
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('PLE', 'PBO', 'PLB', 'WAL')) as PLE_WAL,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('PHX', 'NSC', 'KYR')) as PHX_DV,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('CAL','WIN','EDM','VAN')) as Canada,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('SEA')) as Seattle,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('VON')) as Vons,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('DEN')) as Denver,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('POR')) as Portland,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('WDC')) as Eastern,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('HOU', 'DAL')) as Texas,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('CTG')) as Manilla,
( select count(distinct rdc.device_id) from radia.deviceconfig AS rdc, radia.safewayconfig AS rsc, radia.DeviceAservice as rda
  where rdc.device_id = rsc.device_id and rdc.device_id = rda.device_id and datediff(dd, rsc.mtime, getDate()) < @RadiaTimeWindow
  and rsc.machine_type like '%BACKSTAGE%' and rda.zobjname = 'XP_CREDANT_GUARDIAN' and rsc.location in ('CHI')) as [Dominick's]


-- Count Active Credant Installs and Attrition
select 'Device Count' as [Days Since Last Credant Sync], 
   (select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10) 
         > dateadd([day], -31, getdate())) as [0.. 30], 
   (select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10) 
      BETWEEN dateadd([day], -61, getdate()) and 
         dateadd([day], -32, getdate())) as [31.. 60],
   (select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10) 
      BETWEEN dateadd([day], -91, getdate()) and 
         dateadd([day], -62, getdate())) as [61.. 90],
   (select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10) 
      BETWEEN dateadd([day], -121, getdate()) and 
         dateadd([day], -92, getdate())) as [91.. 120],
   (select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10) 
      BETWEEN dateadd([day], -151, getdate()) and 
         dateadd([day], -122, getdate())) as [121.. 150],
   (select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10)
      BETWEEN dateadd([day], -10000, getdate()) and 
         dateadd([day], -32, getdate())) as [31+]

select 'Device Count' [Active Device Count By Database], (select count(*) from radia.deviceconfig where device_id in
    ( select device_id from radia.DeviceAservice as rda 
      where rda.zobjname = 'XP_CREDANT_GUARDIAN' and datediff(dd, mtime, getDate()) < 61
          UNION
      select device_id from radia.fileaudit as rfa 
      where rfa.name  =  'CMGShieldSvc.exe' and datediff(dd, mtime, getDate()) < 61 )) as [Radia Database], 
    ( select count(*) from SQL5049.CMG.dbo.shieldentity where left(LastSyncTime,10) > dateadd([day], -61, getdate())) as [Credant Database]