name: aws-rds-mysql
fieldsToBeRemovedBeforeParsing: []
testCases:
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '1142'
          Vendor.object: UPDATE employees SET salary = salary * 1.2 WHERE emp_id = 18236
          '#event.kind': event
          process.name: mysqld
          user.name: bob
          client.ip: localhost
          event.type[0]: access
          Vendor.connectionid: '15'
          '#event.outcome': failure
          event.id: '46'
          Vendor.operation: QUERY
          event.category[0]: database
          Vendor.database: company
          client.user.name: bob
          Vendor.serverhost: ip-172-30-0-38
          host.hostname: ip-172-30-0-38
          event.action: query
    event:
      rawString: 20170817 11:07:18,ip-172-30-0-38,bob,localhost,15,46,QUERY,company,'UPDATE
        employees SET salary = salary * 1.2 WHERE emp_id = 18236',1142,
  - assertions:
      - fieldsHaveValues:
          event.type[2]: deletion
          Vendor.retcode: '0'
          Vendor.object: drop user test
          event.category[1]: iam
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '9'
          '#event.outcome': success
          event.id: '1365'
          Vendor.operation: QUERY
          event.category[0]: database
          event.type[1]: user
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-104
          host.hostname: ip-10-22-2-104
          event.action: query
    event:
      rawString: 20240913 09:26:22,ip-10-22-2-104,admin,172.31.12.144,9,1365,QUERY,,'drop
        user test',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: create table users (\nid int,\nname varchar(100))
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '9'
          '#event.outcome': success
          event.id: '365'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-104
          host.hostname: ip-10-22-2-104
          event.action: query
    event:
      rawString: 20240913 09:11:31,ip-10-22-2-104,admin,172.31.12.144,9,365,QUERY,,'create
        table users (\nid int,\nname varchar(100))',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: insert into users values (1,"josh"),(2,"john")
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '9'
          '#event.outcome': success
          event.id: '411'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-104
          host.hostname: ip-10-22-2-104
          event.action: query
    event:
      rawString: 20240913 09:12:12,ip-10-22-2-104,admin,172.31.12.144,9,411,QUERY,,'insert
        into users values (1,"josh"),(2,"john")',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1726052185184)
            ON DUPLICATE KEY UPDATE value = 1726052185184
          '#event.kind': event
          process.name: mysqld
          user.name: rdsadmin
          client.ip: localhost
          event.type[0]: access
          Vendor.connectionid: '7'
          '#event.outcome': success
          event.id: '45'
          Vendor.operation: QUERY
          event.category[0]: database
          Vendor.database: mysql
          client.user.name: rdsadmin
          Vendor.serverhost: ip-10-22-2-5
          host.hostname: ip-10-22-2-5
          event.action: query
    event:
      rawString: 20240911 10:56:25,ip-10-22-2-5,rdsadmin,localhost,7,45,QUERY,mysql,'INSERT
        INTO mysql.rds_heartbeat2(id, value) values (1,1726052185184) ON DUPLICATE KEY
        UPDATE value = 1726052185184',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: GRANT SELECT ON `test`.`t1` TO \'test\'@\'localhost\'
          event.category[1]: iam
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '9'
          '#event.outcome': success
          event.id: '412'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-5
          host.hostname: ip-10-22-2-5
          event.action: query
    event:
      rawString: 20240911 11:01:06,ip-10-22-2-5,admin,172.31.12.144,9,412,QUERY,,'GRANT
        SELECT ON `test`.`t1` TO \'test\'@\'localhost\'',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '1064'
          Vendor.object: grant read on t1 to test@localhost
          event.category[1]: iam
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '9'
          '#event.outcome': failure
          event.id: '360'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-5
          host.hostname: ip-10-22-2-5
          event.action: query
    event:
      rawString: 20240911 11:00:17,ip-10-22-2-5,admin,172.31.12.144,9,360,QUERY,,'grant
        read on t1 to test@localhost',1064,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: insert into table_1 values (2),(3)
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '9'
          '#event.outcome': success
          event.id: '238'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-5
          host.hostname: ip-10-22-2-5
          event.action: query
    event:
      rawString: 20240911 10:58:30,ip-10-22-2-5,admin,172.31.12.144,9,238,QUERY,,'insert
        into table_1 values (2),(3)',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: alter table t1 rename to table_1
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': success
          event.id: '127706'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: query
    event:
      rawString: 20240911 10:40:28,ip-10-22-2-28,admin,172.31.12.144,1631,127706,QUERY,,'alter
        table t1 rename to table_1',0,,
  - assertions:
      - fieldsHaveValues:
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': success
          event.id: '127446'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: query
    event:
      rawString: 20240911 10:36:36,ip-10-22-2-28,admin,172.31.12.144,1631,127446,QUERY,,'GRANT
        ALL PRIVILEGES ON `test`.* TO \'test\'@\'localhost\'',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: insert into t1 values (1)
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': success
          event.id: '127316'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: query
    event:
      rawString: 20240911 10:34:50,ip-10-22-2-28,admin,172.31.12.144,1631,127316,QUERY,,'insert
        into t1 values (1)',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: drop table t2
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': success
          event.id: '127290'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: query
    event:
      rawString: 20240911 10:34:24,ip-10-22-2-28,admin,172.31.12.144,1631,127290,QUERY,,'drop
        table t2',0,,
  - assertions:
      - fieldsHaveValues:
          event.type[2]: creation
          Vendor.retcode: '1064'
          Vendor.object: create user testusder with
          event.category[1]: iam
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': failure
          event.id: '127186'
          Vendor.operation: QUERY
          event.category[0]: database
          event.type[1]: user
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: query
    event:
      rawString: 20240911 10:33:31,ip-10-22-2-28,admin,172.31.12.144,1631,127186,QUERY,,'create
        user testusder with',1064,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          Vendor.object: select @@character_set_client, @@character_set_connection, @@character_set_server,
            @@character_set_database limit 1
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': success
          event.id: '127155'
          Vendor.operation: QUERY
          event.category[0]: database
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: query
    event:
      rawString: 20240911 10:33:01,ip-10-22-2-28,admin,172.31.12.144,1631,127155,QUERY,,'select
        @@character_set_client, @@character_set_connection, @@character_set_server,
        @@character_set_database limit 1',0,,
  - assertions:
      - fieldsHaveValues:
          '#event.kind': event
          process.name: mysqld
          user.name: rdsadmin
          client.ip: localhost
          event.type[0]: access
          Vendor.connectionid: '7'
          '#event.outcome': success
          event.id: '448'
          Vendor.operation: QUERY
          event.category[0]: database
          Vendor.database: mysql
          client.user.name: rdsadmin
          Vendor.serverhost: ip-10-22-2-5
          host.hostname: ip-10-22-2-5
          event.action: query
    event:
      rawString: 20240911 11:01:40,ip-10-22-2-5,rdsadmin,localhost,7,448,QUERY,mysql,'SELECT
        count(*) from information_schema.TABLES WHERE TABLE_SCHEMA = \'mysql\' AND TABLE_NAME
        = \'rds_heartbeat2\'',0,,
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          '#event.kind': event
          process.name: mysqld
          user.name: admin
          client.ip: 172.31.12.144
          event.type[0]: access
          Vendor.connectionid: '1631'
          '#event.outcome': success
          Vendor.connection_type: TCP/IP
          event.id: '0'
          Vendor.operation: CONNECT
          event.category[0]: database
          event.type[1]: start
          client.user.name: admin
          Vendor.serverhost: ip-10-22-2-28
          host.hostname: ip-10-22-2-28
          event.action: connect
    event:
      rawString: 20240911 10:31:39,ip-10-22-2-28,admin,172.31.12.144,1631,0,CONNECT,,,0,TCP/IP
  - assertions:
      - fieldsHaveValues:
          Vendor.retcode: '0'
          '#event.kind': event
          process.name: mysqld
          user.name: rdsadmin
          client.ip: localhost
          event.type[0]: access
          Vendor.connectionid: '821'
          '#event.outcome': success
          Vendor.connection_type: SOCKET
          event.id: '0'
          Vendor.operation: DISCONNECT
          event.category[0]: database
          event.type[1]: end
          client.user.name: rdsadmin
          Vendor.serverhost: ip-10-22-1-51
          host.hostname: ip-10-22-1-51
          event.action: disconnect
    event:
      rawString: 20221201 14:07:05,ip-10-22-1-51,rdsadmin,localhost,821,0,DISCONNECT,,,0,SOCKET
$schema: https://schemas.humio.com/parser/v0.3.0
script: |
  // #region PREPARSE
  /************************************************************
  ****** Parse timestamp and log headers
  ****** Extract message field for parsing
  ****** Parse structured data
  ************************************************************/
  /*
      This parser supports MySQL audit logs generated by mariadb audit plugin. More here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html

      MariaDB Audit Plugin is only supported for the following RDS for MySQL versions: MySQL 8.0.28 and higher 8.0 versions and All MySQL 5.7 versions

      Log format: comma separated fields: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html#Appendix.MySQL.Options.AuditPlugin.LogFormat

      [timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode],[connection_type]
  */
  //

  // preparing rawstring as some fields are in single quotes, example: 'GRANT SELECT ON `test`.`t1` TO \'test\'@\'localhost\''
  __rawdata := replace(field=@rawstring, "\"", with="\"\"")
  | __rawdata := replace(field=__rawdata, "\\\\'", with="myUniqueString")
  | __rawdata := replace(field=__rawdata, "'", with="\"")
  | __rawdata := replace(field=__rawdata, "myUniqueString", with="\\\\'")

  | parseCSV(__rawdata, columns=[@timestamp,Vendor.serverhost,Vendor.username,Vendor.host,Vendor.connectionid,Vendor.queryid,Vendor.operation,Vendor.database,Vendor.object,Vendor.retcode,Vendor.connection_type], delimiter=",", excludeEmpty=true)
  | parseTimestamp(field="@timestamp", format="yyyyMMdd HH:mm:ss", timezone="UTC")
  | drop(["__rawdata"])
  // #endregion

  // #region METADATA
  /************************************************************
  ****** Static Metadata Definitions
  ************************************************************/
  | ecs.version := "8.11.0"
  | Cps.version := "1.0.0"
  | Parser.version := "1.0.0"
  | Vendor := "aws"
  | event.module := "rds"
  | event.dataset := "rds.mysql-audit"

  // #endregion

  // #region NORMALIZATION
  /************************************************************
  ****** Parse unstructured data (i.e. message field)
  ****** Normalize fields to data model
  ************************************************************/

  | event.kind := "event"
  | event.category[0] := "database"
  | Vendor.object match {
      /create\s+user|grant|delete\s+user|drop\s+user|flush\s+privileges/i => event.category[1] := "iam";
      * => *;
  }
  | Vendor.operation match {
      "CONNECT" =>
          event.type[0] := "access" | event.type[1] := "start";
      "DISCONNECT" =>
          event.type[0] := "access" | event.type[1] := "end";
      * =>
          event.type[0] := "access";
  }
  | Vendor.object match {
      /create\s+user/i =>
          event.type[2] := "user" | event.type[3] := "creation";
      /drop\s+user/i =>
          event.type[2] := "user" | event.type[3] := "deletion";
      * => *
  }
  | default("", field=["event.type[0]", "event.type[1]", "event.type[2]"])
  | array:filter(array="event.type[]", function={event!=""}, var="event")

  // Vendor.retcode returns the error number for the query, 0 means no error
  | case {
      Vendor.retcode = "0"
          |  event.outcome := "success";
      Vendor.retcode != "0"
          |  event.outcome := "failure";
      *
          |  event.outcome := "unknown";
  }

  | event.action := lower(Vendor.operation)
  | event.id := rename(Vendor.queryid)

  | process.name := "mysqld"
  | user.name := rename(Vendor.username)
  | host.hostname := lower(Vendor.serverhost)
  // host --> client host /ip

  | client.ip := rename(Vendor.host)
  | client.user.name := user.name

  // #endregion

  // #region POST-NORMALIZATION
  /************************************************************
  ****** Post Normalization
  ****** Custom parser logic needed after normalization
  ************************************************************/


  // #endregion
tagFields:
  - Cps.version
  - Vendor
  - ecs.version
  - event.dataset
  - event.kind
  - event.module
  - event.outcome
  - observer.type
