A community call: share your mappings

Plugins SQL
,
1

Hi!

My team is working on SQL plugin for OpenSearch, and we found that our sample data used for testing is pretty simple. I afraid that we can miss something while working on new features.

I’m kindly asking you to share your indices mapping and maybe sample data for those mapping (just few docs). Please, remove/replace/rename all sensitive data before sharing.

The most interesting things are data of types nested, object, text with fields, and date with different formats. And all combinations of listed things.

I’d like to catch all possible bugs and issues before you faced them.

Thank you in advance!

2

I can get you tons of docs if you’re still working on this lol. Right now I’m using the SQL plugin like this:

select @timestamp, event.code, winlog.event_data.TargetUserSid, winlog.event_data.SubjectUserName,winlog.event_data.SubjectUserSid,winlog.event_data.SubjectLogonId,winlog.event_data.TargetLogonId,winlog.event_data.SubjectDomainName,winlog.event_data.TargetDomainName,winlog.event_data.TokenElevationType,winlog.event_data.TargetUserName,winlog.event_data.ParentProcessName,winlog.event_data.NewProcessName,winlog.event_data.CommandLine,winlog.event_data.MandatoryLabel,winlog.event_data.ProcessId,winlog.event_data.NewProcessId 
from winlogbeat-*
where event.code="4688" and @timestamp>"2023-02-23 18:53:55.000" and @timestamp < "2023-02-23 18:55:00.000"
order by @timestamp asc

This doesn’t return anything for me. If I remove the event.code clause, it returns all events in that time range correctly. If I remove the time range, it returns 4688 events correctly. I’m not really a SQL pro or anything so it could just be me. event.code is mapped as a number. The full mapping is pretty big but I can provide that as well.

doc samples:

{
  "_index": "winlogbeat-2023.02.23",
  "_id": "HnGgf4YBVtBqFsU3oHbq",
  "_version": 1,
  "_score": null,
  "_source": {
    "host": {
      "architecture": "x86_64",
      "id": "e2fc0cf5-2212-4089-9184-8e2a827610ed",
      "hostname": "station1",
      "os": {
        "kernel": "10.0.18362.418 (WinBuild.160101.0800)",
        "version": "10.0",
        "platform": "windows",
        "family": "windows",
        "build": "18363.418",
        "type": "windows",
        "name": "Windows 10 Enterprise Evaluation"
      },
      "mac": [
        "00:0c:29:4c:0b:bd",
        "00:0c:29:4c:0b:b3"
      ],
      "name": "station1.windomain.local",
      "ip": [
        "192.168.56.110",
        "192.168.58.164"
      ]
    },
    "message": "A new process has been created.\n\nCreator Subject:\n\tSecurity ID:\t\tS-1-5-21-1620670665-1735723833-706467303-1002\n\tAccount Name:\t\toperator\n\tAccount Domain:\t\tSTATION1\n\tLogon ID:\t\t0x1E5AAE6\n\nTarget Subject:\n\tSecurity ID:\t\tS-1-0-0\n\tAccount Name:\t\t-\n\tAccount Domain:\t\t-\n\tLogon ID:\t\t0x0\n\nProcess Information:\n\tNew Process ID:\t\t0x11a0\n\tNew Process Name:\tC:\\Windows\\SysWOW64\\mshta.exe\n\tToken Elevation Type:\t%%1936\n\tMandatory Label:\t\tS-1-16-12288\n\tCreator Process ID:\t0x1508\n\tCreator Process Name:\tC:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\ONENOTE.EXE\n\tProcess Command Line:\t\"C:\\Windows\\SysWOW64\\mshta.exe\" \"C:\\Users\\operator\\AppData\\Local\\Temp\\OneNote\\16.0\\Exported\\{68907BFF-1CB4-4D6A-9645-8ADE4D859615}\\NT\\0\\Open.hta\" {1E460BD7-F1C3-4B2E-88BF-4E770A288AF5}{1E460BD7-F1C3-4B2E-88BF-4E770A288AF5} \n\nToken Elevation Type indicates the type of token that was assigned to the new process in accordance with User Account Control policy.\n\nType 1 is a full token with no privileges removed or groups disabled.  A full token is only used if User Account Control is disabled or if the user is the built-in Administrator account or a service account.\n\nType 2 is an elevated token with no privileges removed or groups disabled.  An elevated token is used when User Account Control is enabled and the user chooses to start the program using Run as administrator.  An elevated token is also used when an application is configured to always require administrative privilege or to always require maximum privilege, and the user is a member of the Administrators group.\n\nType 3 is a limited token with administrative privileges removed and administrative groups disabled.  The limited token is used when User Account Control is enabled, the application does not require administrative privilege, and the user does not choose to start the program using Run as administrator.",
    "ecs": {
      "version": "1.8.0"
    },
    "log": {
      "level": "information"
    },
    "@timestamp": "2023-02-23T18:54:36.086Z",
    "winlog": {
      "provider_name": "Microsoft-Windows-Security-Auditing",
      "task": "Process Creation",
      "keywords": [
        "Audit Success"
      ],
      "process": {
        "pid": 4,
        "thread": {
          "id": 5880
        }
      },
      "event_data": {
        "TokenElevationType": "%%1936",
        "NewProcessName": "C:\\Windows\\SysWOW64\\mshta.exe",
        "CommandLine": "\"C:\\Windows\\SysWOW64\\mshta.exe\" \"C:\\Users\\operator\\AppData\\Local\\Temp\\OneNote\\16.0\\Exported\\{68907BFF-1CB4-4D6A-9645-8ADE4D859615}\\NT\\0\\Open.hta\" {1E460BD7-F1C3-4B2E-88BF-4E770A288AF5}{1E460BD7-F1C3-4B2E-88BF-4E770A288AF5} ",
        "TargetUserName": "-",
        "ParentProcessName": "C:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\ONENOTE.EXE",
        "SubjectDomainName": "STATION1",
        "NewProcessId": "0x11a0",
        "TargetUserSid": "S-1-0-0",
        "MandatoryLabel": "S-1-16-12288",
        "SubjectUserName": "operator",
        "SubjectLogonId": "0x1e5aae6",
        "TargetDomainName": "-",
        "TargetLogonId": "0x0",
        "ProcessId": "0x1508",
        "SubjectUserSid": "S-1-5-21-1620670665-1735723833-706467303-1002"
      },
      "provider_guid": "{54849625-5478-4994-a5ba-3e3b0328c30d}",
      "version": 2,
      "channel": "Security",
      "computer_name": "station1.windomain.local",
      "event_id": 4688,
      "api": "wineventlog",
      "record_id": 98819,
      "opcode": "Info"
    },
    "event": {
      "code": 4688,
      "provider": "Microsoft-Windows-Security-Auditing",
      "action": "Process Creation",
      "kind": "event",
      "created": "2023-02-23T18:54:37.945Z",
      "outcome": "success"
    },
    "@version": "1",
    "agent": {
      "version": "7.12.1",
      "id": "85c134d3-73da-4895-833f-c0f9be9d572f",
      "hostname": "station1",
      "ephemeral_id": "773255fb-5321-4590-9508-b92008dc4e66",
      "type": "winlogbeat",
      "name": "station1"
    },
    "tags": [
      "beats_input_codec_plain_applied"
    ]
  },
  "fields": {
    "@timestamp": [
      "2023-02-23T18:54:36.086Z"
    ],
    "event.created": [
      "2023-02-23T18:54:37.945Z"
    ]
  },
  "sort": [
    1677178476086
  ]
}

{
  "_index": "winlogbeat-2023.02.23",
  "_id": "IXGgf4YBVtBqFsU3oHbu",
  "_version": 1,
  "_score": null,
  "_source": {
    "host": {
      "architecture": "x86_64",
      "id": "e2fc0cf5-2212-4089-9184-8e2a827610ed",
      "hostname": "station1",
      "os": {
        "kernel": "10.0.18362.418 (WinBuild.160101.0800)",
        "version": "10.0",
        "family": "windows",
        "platform": "windows",
        "build": "18363.418",
        "type": "windows",
        "name": "Windows 10 Enterprise Evaluation"
      },
      "mac": [
        "00:0c:29:4c:0b:bd",
        "00:0c:29:4c:0b:b3"
      ],
      "name": "station1.windomain.local",
      "ip": [
        "192.168.56.110",
        "192.168.58.164"
      ]
    },
    "message": "A new process has been created.\n\nCreator Subject:\n\tSecurity ID:\t\tS-1-5-21-1620670665-1735723833-706467303-1002\n\tAccount Name:\t\toperator\n\tAccount Domain:\t\tSTATION1\n\tLogon ID:\t\t0x1E5AAE6\n\nTarget Subject:\n\tSecurity ID:\t\tS-1-0-0\n\tAccount Name:\t\t-\n\tAccount Domain:\t\t-\n\tLogon ID:\t\t0x0\n\nProcess Information:\n\tNew Process ID:\t\t0x1a20\n\tNew Process Name:\tC:\\Windows\\System32\\conhost.exe\n\tToken Elevation Type:\t%%1936\n\tMandatory Label:\t\tS-1-16-12288\n\tCreator Process ID:\t0xa20\n\tCreator Process Name:\tC:\\Windows\\SysWOW64\\curl.exe\n\tProcess Command Line:\t\\??\\C:\\Windows\\system32\\conhost.exe 0xffffffff -ForceV1\n\nToken Elevation Type indicates the type of token that was assigned to the new process in accordance with User Account Control policy.\n\nType 1 is a full token with no privileges removed or groups disabled.  A full token is only used if User Account Control is disabled or if the user is the built-in Administrator account or a service account.\n\nType 2 is an elevated token with no privileges removed or groups disabled.  An elevated token is used when User Account Control is enabled and the user chooses to start the program using Run as administrator.  An elevated token is also used when an application is configured to always require administrative privilege or to always require maximum privilege, and the user is a member of the Administrators group.\n\nType 3 is a limited token with administrative privileges removed and administrative groups disabled.  The limited token is used when User Account Control is enabled, the application does not require administrative privilege, and the user does not choose to start the program using Run as administrator.",
    "ecs": {
      "version": "1.8.0"
    },
    "log": {
      "level": "information"
    },
    "@timestamp": "2023-02-23T18:54:36.527Z",
    "winlog": {
      "provider_name": "Microsoft-Windows-Security-Auditing",
      "keywords": [
        "Audit Success"
      ],
      "task": "Process Creation",
      "process": {
        "thread": {
          "id": 8800
        },
        "pid": 4
      },
      "event_data": {
        "TokenElevationType": "%%1936",
        "NewProcessName": "C:\\Windows\\System32\\conhost.exe",
        "CommandLine": "\\??\\C:\\Windows\\system32\\conhost.exe 0xffffffff -ForceV1",
        "TargetUserName": "-",
        "SubjectDomainName": "STATION1",
        "ParentProcessName": "C:\\Windows\\SysWOW64\\curl.exe",
        "NewProcessId": "0x1a20",
        "TargetUserSid": "S-1-0-0",
        "MandatoryLabel": "S-1-16-12288",
        "SubjectUserName": "operator",
        "SubjectLogonId": "0x1e5aae6",
        "TargetDomainName": "-",
        "TargetLogonId": "0x0",
        "ProcessId": "0xa20",
        "SubjectUserSid": "S-1-5-21-1620670665-1735723833-706467303-1002"
      },
      "provider_guid": "{54849625-5478-4994-a5ba-3e3b0328c30d}",
      "version": 2,
      "channel": "Security",
      "computer_name": "station1.windomain.local",
      "record_id": 98821,
      "opcode": "Info",
      "api": "wineventlog",
      "event_id": 4688
    },
    "event": {
      "code": 4688,
      "provider": "Microsoft-Windows-Security-Auditing",
      "action": "Process Creation",
      "outcome": "success",
      "created": "2023-02-23T18:54:37.945Z",
      "kind": "event"
    },
    "@version": "1",
    "agent": {
      "version": "7.12.1",
      "id": "85c134d3-73da-4895-833f-c0f9be9d572f",
      "hostname": "station1",
      "ephemeral_id": "773255fb-5321-4590-9508-b92008dc4e66",
      "type": "winlogbeat",
      "name": "station1"
    },
    "tags": [
      "beats_input_codec_plain_applied"
    ]
  },
  "fields": {
    "@timestamp": [
      "2023-02-23T18:54:36.527Z"
    ],
    "event.created": [
      "2023-02-23T18:54:37.945Z"
    ]
  },
  "sort": [
    1677178476527
  ]
}
3

Thanks @aceresponder, this is awesome!
Yes, I’m still working on this, and your example could be very helpful. Do you mind sharing your mappings? If it doesn’t fit the post, you can probably attach this as a file or as a link to a gist.

Unfortunately, SQL plugin has limited support of wildcards in FROM clause. Can you try specifying exact index name? event.code is a number in your doc sample, so it should be without quotes in WHERE clause.
This query should work:

select `@timestamp`, event.code, winlog.event_data.TargetUserSid, winlog.event_data.SubjectUserName,winlog.event_data.SubjectUserSid,winlog.event_data.SubjectLogonId,winlog.event_data.TargetLogonId,winlog.event_data.SubjectDomainName,winlog.event_data.TargetDomainName,winlog.event_data.TokenElevationType,winlog.event_data.TargetUserName,winlog.event_data.ParentProcessName,winlog.event_data.NewProcessName,winlog.event_data.CommandLine,winlog.event_data.MandatoryLabel,winlog.event_data.ProcessId,winlog.event_data.NewProcessId 
from `winlogbeat-2023.02.23`
where event.code=4688 and `@timestamp`>"2023-02-23 18:53:55.000" and `@timestamp` < "2023-02-23 18:55:00.000"
order by `@timestamp` asc

I wrapped @timestamp and index name with backticks to ensure that they are not misinterpreted by the SQL parser.

Looking forward to your reply,
Yury

4

Oh yeah works perfectly! Thanks for the tips! Here’s a link to the gist: winlogbeat-2023.02.23 mapping · GitHub

5

Great news!

Feel free to ask for help or post a bug on SQL plugin repository.