declare @days integer
set @days = 30
SELECT
G.NAME AS [SEP GROUP]
,CASE WHEN SA.STATUS = '0' THEN 'Offline' ELSE 'Online' END AS [STATUS]
,C.COMPUTER_NAME AS [Computer Name]
,C.OPERATION_SYSTEM AS [Operating System]
,C.SERVICE_PACK as [Service Pack]
,C.COMPUTER_DOMAIN_NAME as [DOMAIN]
,C.CURRENT_LOGIN_USER as [Logon User]
,SA.AGENT_VERSION AS [SEP Client Version]
,p.VERSION as [Virus Definitions]
,CONVERT(varCHAR(20), dateadd(s,convert(bigint,SA.[LAST_UPDATE_TIME])/1000,'01-01-1970 0:00:00'), 100) as [SEPM Last Online Time]
,CASE WHEN dateadd(s,convert(bigint,SA.LAST_DOWNLOAD_TIME)/1000,'01-01-1970 0:00:00') = '01-01-1970 0:00:00' THEN ''
ELSE CONVERT(varCHAR(20), dateadd(s,convert(bigint,SA.LAST_DOWNLOAD_TIME)/1000,'01-01-1970 0:00:00'), 100) END as [Last DownLoad Time]
,SA.LAST_DOWNLOAD_TIME
,CASE WHEN SA.AP_ONOFF = 1 THEN 'On'
WHEN SA.AP_ONOFF = 2 THEN 'Not Installed'
WHEN SA.AP_ONOFF = 4 THEN 'Component is Malfunctioning'
WHEN SA.AP_ONOFF = 0 THEN 'Off'
when SA.AP_ONOFF = 127 THEN 'Not Reporting'
END AS [Auto-Protect Staus]
,SA.INFECTED
,CASE WHEN SA.INFECTED = 1 THEN 'Infected' ELSE '' END AS [Infected]
FROM SEM_AGENT as SA
join SEM_COMPUTER C on SA.COMPUTER_ID=C.COMPUTER_ID
JOIN IDENTITY_MAP G ON G.ID = SA.GROUP_ID
JOIN V_SEM_COMPUTER VC ON VC.COMPUTER_ID = SA.COMPUTER_ID
JOIN PATTERN P ON SA.PATTERN_IDX=P.PATTERN_IDX
WHERE DATEDIFF(DD,P.PATTERNDATE,GETDATE()) > @days
ORDER BY G.NAME, c.COMPUTER_NAME