0

Here is the example of CEF Data on a single column named raw

{
    "raw": "CEF:0|Check Point|New Anti Virus|Check Point|DNS reputation
  |Malware.TC.d8ccLeiq|Low|act=Detect cp_severity=Low 
  cs1Label=Threat Prevention Rule Name cs1=Autonomous Threat Prevention 
  cs2Label=Protection ID cs2=000DC8CC6 cs3Label=Protection Type 
  cs3=DNS reputation cs4Label=Protection Name cs4=Malware.TC.d8ccLeiq 
  cs4Label=Threat Prevention Rule ID cs4={678d033c-df1b-4a63-9ee0-11c6dd0dd028} cs6Label=Malware Family 
  cs6=Malware deviceDirection=1 flexNumber1Label=Confidence flexNumber1=1 flexString2Label=Malware 
  Action flexString2=DNS query for a site known to contain malware request=killz.pro 
  rt=1677624210000 sourceTranslatedAddress=30.20.0.5 spt=56476 dpt=53 
  cs4Label=Threat Prevention Rule ID cs4={678d033c-df1b-4a63-9ee0-11c6dd0dd028} 
  cs1Label=Threat Prevention Rule Name cs1=Autonomous Threat Prevention layer_name=Standard Threat Prevention 
  layer_uuid={D3A3D454-5CCB-4615-BA16-9CD7AFB4D084} smartdefense_profile=Strict Security 
  ifname=eth1 loguid={0x194377b3,0x558f5b34,0x692cf8a8,0x2f1636c7} 
  origin=30.20.0.10 originsicname=cn\\=cp_mgmt,o\\=checkpointfw..2nndov sequencenum=2 
  version=5 dst=1.1.1.1 log_id=2 policy=Standard 
  policy_time=1677624104 product=New Anti Virus proto=17 
  session_id={0x63fe8392,0x1,0x9381505b,0xddd7167c} smartdefense_profile=Strict Security 
  src=30.20.0.5 vendor_list=Check Point ThreatCloud"
}

I have been trying to filter the origin=30.20.0.10 and dst=1.1.1.1 and put them on a separate column. named origin_ip and dest_ip.

I have tried to nest that as separate table but it doesn't seem to work for me.(or not sure if I am doing it correctly) I am not sure how to do it with Regex and I cannot find any online reference in filtering CEF logs using SQL.

Appreciate any idea here.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Eps Geofs Diivs
  • 9
  • 4
  • That looks like JSON data… you will need to normalise it to tables-and-columns first - or try to get-by with whatever JSON-handling functions are supported by your DBMS. – Dai Mar 01 '23 at 17:25
  • this is plain text thing, as it consisst only of one big text. ue struing function to LOCATE originP= and dst= and the nexe space and you can extract it with substring – nbk Mar 01 '23 at 18:25
  • You won't be able to do this in an easy or well-performing way. If any of the fields in this data matter, you need to _build them into the table schema_, and then extract those values as part of the INSERT/UPDATE process. – Joel Coehoorn Apr 17 '23 at 18:53

1 Answers1

0

Let's consider this is your table structure:

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `text` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

And this is your data:

INSERT  INTO `test`(`id`,`text`) VALUES (1,'{\r\n    \"raw\": \"CEF:0|Check Point|New Anti Virus|Check Point|DNS reputation\r\n  |Malware.TC.d8ccLeiq|Low|act=Detect cp_severity=Low \r\n  cs1Label=Threat Prevention Rule Name cs1=Autonomous Threat Prevention \r\n  cs2Label=Protection ID cs2=000DC8CC6 cs3Label=Protection Type \r\n  cs3=DNS reputation cs4Label=Protection Name cs4=Malware.TC.d8ccLeiq \r\n  cs4Label=Threat Prevention Rule ID cs4={678d033c-df1b-4a63-9ee0-11c6dd0dd028} cs6Label=Malware Family \r\n  cs6=Malware deviceDirection=1 flexNumber1Label=Confidence flexNumber1=1 flexString2Label=Malware \r\n  Action flexString2=DNS query for a site known to contain malware request=killz.pro \r\n  rt=1677624210000 sourceTranslatedAddress=30.20.0.5 spt=56476 dpt=53 \r\n  cs4Label=Threat Prevention Rule ID cs4={678d033c-df1b-4a63-9ee0-11c6dd0dd028} \r\n  cs1Label=Threat Prevention Rule Name cs1=Autonomous Threat Prevention layer_name=Standard Threat Prevention \r\n  layer_uuid={D3A3D454-5CCB-4615-BA16-9CD7AFB4D084} smartdefense_profile=Strict Security \r\n  ifname=eth1 loguid={0x194377b3,0x558f5b34,0x692cf8a8,0x2f1636c7} \r\n  origin=30.20.0.10 originsicname=cn\\\\=cp_mgmt,o\\\\=checkpointfw..2nndov sequencenum=2 \r\n  version=5 dst=1.1.1.1 log_id=2 policy=Standard \r\n  policy_time=1677624104 product=New Anti Virus proto=17 \r\n  session_id={0x63fe8392,0x1,0x9381505b,0xddd7167c} smartdefense_profile=Strict Security \r\n  src=30.20.0.5 vendor_list=Check Point ThreatCloud\"\r\n}');

So use this query to find all the required values:

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(`text`, 'origin=', -1), ' ', 1) AS origin,
  SUBSTRING_INDEX(SUBSTRING_INDEX(`text`, 'dst=', -1), ' ', 1) AS dst
FROM test;

Here is your result:

origin dst
30.20.0.10 1.1.1.1

Here is a simple explanation: We used SUBSTRING_INDEX function to extract the values between the ("origin=" and " ") and ("dst=" and " ") substrings.

Renuka Jatoth
  • 46
  • 2