Tuesday, September 21, 2010

Dionaea sql logging for dce rpc calls

As the need to keep observe and get notify about the recent attacks to Dionaea's SMB stack, I spent some time to figure out few SQL queries that made the works easier, especially to view the statistic of supported and unsupported DCE RPC calls,

1. (From Dionaea site) Interested which dcerpc calls get attacked most?
SELECT
COUNT(*),
dcerpcrequest_uuid,
dcerpcrequest_opnum
FROM
dcerpcrequests
GROUP BY
dcerpcrequest_uuid,
dcerpcrequest_opnum
ORDER BY
COUNT(*)
DESC;

Result:
"COUNT(*)", "dcerpcrequest_uuid", "dcerpcrequest_opnum"
"17231", "99fcfec4-5260-101b-bbcb-00aa0021347a", "5"
"7853", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "31"
"1828", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "15"
"1475", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "32"
"962", "1ff70682-0a51-30e8-076d-740be8cee98b", "0"
"362", "000001a0-0000-0000-c000-000000000046", "4"
"350", "afa8bd80-7d8a-11c9-bef4-08002b102989", "0"
....
The returned result cant really provide much info to me, eg what is the UUID and opnum stand for. Lets move on.

2. To know which dce rpc attacks the most, with UUID and Opnum details?

SELECT 
COUNT(*),
dcerpcrequest_uuid,
dcerpcrequest_opnum,
parent.dcerpcservice_name,
ops.dcerpcserviceop_name
FROM
dcerpcrequests
JOIN dcerpcservices AS parent
JOIN dcerpcserviceops AS ops
ON (dcerpcrequests.dcerpcrequest_uuid == parent.dcerpcservice_uuid )
AND ((parent.dcerpcservice == ops.dcerpcservice)
AND (dcerpcrequests.dcerpcrequest_opnum == ops.dcerpcserviceop_opnum))
GROUP BY
dcerpcrequest_uuid,
dcerpcrequest_opnum
ORDER BY
COUNT(*)
DESC;

Result:
"COUNT(*)", "dcerpcrequest_uuid", "dcerpcrequest_opnum", "dcerpcservice_name", "dcerpcserviceop_name"
"17231", "99fcfec4-5260-101b-bbcb-00aa0021347a", "5", "IOXIDResolver", "ServerAlive2"
"7853", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "31", "SRVSVC", "NetPathCanonicalize"
"1828", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "15", "SRVSVC", "NetShareEnum"
"1475", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "32", "SRVSVC", "NetPathCompare"
"362", "000001a0-0000-0000-c000-000000000046", "4", "ISystemActivator", "RemoteCreateInstance"
"350", "afa8bd80-7d8a-11c9-bef4-08002b102989", "0", "MGMT", "inq_if_ids"
....
It works but it only shows those supported DCE RPC calls. If compared the result in 1 and 2, we found out there is 1 unsupported rpc call, which receive 962 times of attack.
"962", "1ff70682-0a51-30e8-076d-740be8cee98b", "0"

Which RPC services related with this unsupported call?

3. To find out which DCE RPC calls that unsupported with Dionaea?

SELECT 
COUNT(*),
dcerpcrequest_uuid,
dcerpcrequest_opnum,
parent.dcerpcservice_name,
ops.dcerpcserviceop_name
FROM
dcerpcrequests
JOIN dcerpcservices AS parent
LEFT OUTER JOIN dcerpcserviceops AS ops
ON (dcerpcrequests.dcerpcrequest_uuid == parent.dcerpcservice_uuid )
AND (parent.dcerpcservice == ops.dcerpcservice)
AND (dcerpcrequests.dcerpcrequest_opnum == ops.dcerpcserviceop_opnum)
WHERE
dcerpcserviceop_name IS NULL
GROUP BY
dcerpcrequest_uuid,
dcerpcrequest_opnum
ORDER BY
COUNT(*)
DESC;

Result:
"COUNT(*)", "dcerpcrequest_uuid", "dcerpcrequest_opnum", "dcerpcservice_name", "dcerpcserviceop_name"
"962", "1ff70682-0a51-30e8-076d-740be8cee98b", "0", "ATSVC", ""
"3", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "35", "SRVSVC", ""
"2", "3919286a-b10c-11d0-9ba8-00c04fd92ef5", "31", "DSSETUP", ""
"1", "12345778-1234-abcd-ef00-0123456789ac", "34", "samr", ""
"1", "4b324fc8-1670-01d3-1278-5a47bf6ee188", "21", "SRVSVC", ""
There are 5 calls not supported for the moment, with the obvious 962 attacks to ATSVC RPC services, Opnum 0.

According to MDSN http://msdn.microsoft.com/en-us/library/cc248423%28v=PROT.13%29.aspx, it is the NetrJobAdd (Opnum 0) call.

I got the answer :)

Reference:
Markus has sent me his query that it will return the exact result as the third query.
SELECT
COUNT(*),
dcerpcrequests.dcerpcrequest_uuid,
dcerpcservice_name,
dcerpcrequest_opnum
FROM
dcerpcrequests
JOIN dcerpcservices
ON(dcerpcrequests.dcerpcrequest_uuid ==
dcerpcservices.dcerpcservice_uuid)
LEFT OUTER JOIN dcerpcserviceops
ON(dcerpcserviceops.dcerpcserviceop_opnum = dcerpcrequest_opnum
AND dcerpcservices.dcerpcservice = dcerpcserviceops.dcerpcservice )
WHERE
dcerpcserviceop_name IS NULL
GROUP BY
dcerpcrequests.dcerpcrequest_uuid,
dcerpcservice_name,
dcerpcrequest_opnum
ORDER BY
COUNT(*)
DESC


Mistake:
Why the 2nd sql query can only returned the supported rpc calls,but it cant show the unsupported call?

Reason:
I messed up with the sql query JOIN, INNER JOIN, RIGHT OUTER JOIN and LEFT OUTER JOIN.
- JOIN/INNER JOIN will not include the NULL data in the row
- LEFT OUTER JOIN/RIGHT OUT JOIN will include the NULL data in each row
- LEFT OUTER JOIN : selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.
- RIGHT OUT JOIN : Opposite to LEFT OUTER JOIN, returns all rows from the second table

http://www.sql-tutorial.net/SQL-JOIN.asp

Clear!

No comments:

Post a Comment