Hey Guys,
I need to extract some data from SEP MS-SQL DB for a custom report. I am using below mentioned query to extract list of endpoints with relevant information which are infected with Conficker Worm. I need to add client Group information as well which is in another table. I am not that good with SQL queries to use Joint and other attributes to merge data from two different tables. Also if someone has simpler query for conficker query with mentioned info, please do share.
SELECT dateadd(s,convert(bigint,EVENT_TIME)/1000,'01-01-1970 00:00:00') EVENT_TIME1
,[SEVERITY]=
Case When SEVERITY in(0,1,2,3)then'Critical'
When SEVERITY in(4,5,6,7)then'Major'
When SEVERITY in(8,9,10,11)then'Minor'
When SEVERITY in(12,13,14,15)then'Info'
else'Unknown'end
,[HOST_NAME]
,CAST(LOCAL_HOST_IP / 256 / 256 / 256 % 256 AS VARCHAR)+'.'+CAST(LOCAL_HOST_IP / 256 / 256 % 256 AS VARCHAR)+'.'+CAST(LOCAL_HOST_IP/ 256 % 256 AS VARCHAR)+'.'+CAST(LOCAL_HOST_IP % 256 AS VARCHAR)AS LOCAL_HOSTIP
,CAST(REMOTE_HOST_IP / 256 / 256 / 256 % 256 AS VARCHAR)+'.'+CAST(REMOTE_HOST_IP / 256 / 256 % 256 AS VARCHAR)+'.'+CAST(REMOTE_HOST_IP/ 256 % 256 AS VARCHAR)+'.'+CAST(REMOTE_HOST_IP % 256 AS VARCHAR)AS REMOTE_HOST_IP
,[TRAFFIC_DIRECTION]=
case
when TRAFFIC_DIRECTION=1 then'Inbound'
when TRAFFIC_DIRECTION=2 then'Outbound'
else'Unknown'end
,[NETWORK_PROTOCOL]=case
When NETWORK_PROTOCOL=1 then'Others'
When NETWORK_PROTOCOL=2 then'TCP'
When NETWORK_PROTOCOL=3 then'UDP'
When NETWORK_PROTOCOL=4 then'ICMP'
else'Unknown'end
,dateadd(s,convert(bigint,BEGIN_TIME)/1000,'01-01-1970 00:00:00') [BEGIN_TIME]
,dateadd(s,convert(bigint,end_TIME)/1000,'01-01-1970 00:00:00') [END_TIME]
,[REPETITION]
,[APP_NAME]
,[EVENT_DESC]
,[LOCATION_NAME]
,[LOCAL_PORT]
,[REMOTE_PORT]
,[CIDS_SIGN_ID]
,[STR_CIDS_SIGN_ID]
,[CIDS_SIGN_SUB_ID]
,[INTRUSION_URL]
,[INTRUSION_PAYLOAD_URL]
FROM [sepm].[dbo].[AGENT_SECURITY_LOG_1]
where
--year(dateadd(s,convert(bigint,Time_Stamp)/1000,'01-01-1970 00:00:00'))=11
--And
EVENT_DESC like '%CVE-2008-4250%'