sof-elk
sof-elk copied to clipboard
philhagen
GUI exported UAL logs are not properly processed if at all (6701-office365.conf)
Hi, I experiencing problems with processing of UAL logs exported from GUI. In Logstash logs I can see following error:
[2022-08-21T15:00:18,976][WARN ][logstash.outputs.elasticsearch][main][78ba23061637f571536de8c17020910dffdb78b462421999c63525961184ceef] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"office365-2022.08", :routing=>nil, :_type=>"_doc"}, #LogStash::Event:0x2adf8da1], :response=>{"index"=>{"_index"=>"office365-2022.08", "_type"=>"_doc", "_id"=>"UojrwIIB42I3XTvFP7kk", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [ps_show_computer_name] of type [boolean] in document with id 'UojrwIIB42I3XTvFP7kk'. Preview of field's value: '2'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Failed to parse value [2] as only [true] or [false] are allowed."}}}}}
As per my finding it should be related to this record:
Workload: AzureActiveDirectory Line: RecordType"":2
I do not see anything related to boolean field in logstash config file, so I guess it is somewhere in helping rb scripts?
Thanks in advance for any help.
New Purview export from GUI has the following fields: RecordId,CreationDate,RecordType,Operation,UserId,AuditData
RecordId,CreationDate,RecordType,Operation,UserId,AuditData 0030f965-ed34-48c0-c61b-08da573650db,6/26/2022 5:40:02 AM,3,HardDelete,[email protected],"{""CreationTime"":""2022-06-26T05:40:02"",""Id"":""0030f965-ed34-48c0-c61b-08da573650db"",""Operation"":""HardDelete"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""FFFFFFFFFFFFFFFF"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""180.150.36.168"",""UserId"":""[email protected]"",""ClientIPAddress"":""180.150.36.168"",""ClientInfoString"":""Client=ActiveSync"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxGuid"":""11111111-2222-2222-3333-444444444444"",""MailboxOwnerSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""testtenancy.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""AffectedItems"":[{""Id"":""RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGMMzcsAAAJ"",""InternetMessageId"":""[email protected]"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""Action""}],""CrossMailboxOperation"":false,""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""}}" 0034779e-f875-4015-d49b-08da4763e1a9,6/6/2022 2:25:53 AM,6,FilePreviewed,[email protected],"{""AppAccessContext"":{""CorrelationId"":""219044a0-106f-1000-4ccf-dff97fcd0a5d""},""CreationTime"":""2022-06-06T02:25:53"",""Id"":""0034779e-f875-4015-d49b-08da4763e1a9"",""Operation"":""FilePreviewed"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":6,""UserKey"":""i:0h.f|membership|[email protected]"",""UserType"":0,""Version"":1,""Workload"":""SharePoint"",""ClientIP"":""180.150.36.168"",""ObjectId"":""https://testtenancy.sharepoint.com/sites/test/Shared Documents/files/R1.doc"",""UserId"":""[email protected]"",""CorrelationId"":""219044a0-106f-1000-4ccf-dff97fcd0a5d"",""DoNotDistributeEvent"":true,""EventSource"":""SharePoint"",""ItemType"":""File"",""ListId"":""e7362856-6fde-4908-b82a-e6e06e699ff8"",""ListItemUniqueId"":""b78ac8b8-7a97-4635-a28e-d029a3792dc4"",""Site"":""00000000-3333-3333-3333-000000000000"",""UserAgent"":""OneDriveMpc-Transform_Thumbnail/1.0"",""WebId"":""22949a7a-8ce8-4bc0-a70c-7d3eb9dd0920"",""HighPriorityMediaProcessing"":false,""SourceFileExtension"":""doc"",""SiteUrl"":""https://testtenancy.sharepoint.com/sites/test/"",""SourceFileName"":""R1.doc"",""SourceRelativeUrl"":""Shared Documents/files/Reports""}" 006221cd-dc7d-40a6-f0f5-08da82b96bd7,8/20/2022 2:36:51 PM,3,HardDelete,[email protected],"{""CreationTime"":""2022-08-20T14:36:51"",""Id"":""006221cd-dc7d-40a6-f0f5-08da82b96bd7"",""Operation"":""HardDelete"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""FFFFFFFFFFFFFFFF"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""2001:8004:4500:b5d2:a5cd:7528:628e:61a7"",""UserId"":""[email protected]"",""ClientIPAddress"":""2001:8004:4500:b5d2:a5cd:7528:628e:61a7"",""ClientInfoString"":""Client=ActiveSync"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxGuid"":""11111111-2222-2222-3333-444444444444"",""MailboxOwnerSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""testtenancy.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""AffectedItems"":[{""Id"":""RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGxPFA1AAAJ"",""InternetMessageId"":""[email protected]"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""Your email""}],""CrossMailboxOperation"":false,""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""}}" 00a2cb6e-89c4-430e-a2ff-08da741fa69b,8/2/2022 12:40:51 AM,3,HardDelete,[email protected],"{""CreationTime"":""2022-08-02T00:40:51"",""Id"":""00a2cb6e-89c4-430e-a2ff-08da741fa69b"",""Operation"":""HardDelete"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""FFFFFFFFFFFFFFFF"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""2403:5801:1768:0:9da9:38c3:d08b:9799"",""UserId"":""[email protected]"",""ClientIPAddress"":""2403:5801:1768:0:9da9:38c3:d08b:9799"",""ClientInfoString"":""Client=ActiveSync"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxGuid"":""11111111-2222-2222-3333-444444444444"",""MailboxOwnerSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""testtenancy.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""AffectedItems"":[{""Id"":""RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGkatJeAAAJ"",""InternetMessageId"":""[email protected]"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""Your email""}],""CrossMailboxOperation"":false,""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""}}" 00aa9aa0-cba1-42d0-a8a3-08da4d4395f5,6/13/2022 1:49:50 PM,3,HardDelete,[email protected],"{""CreationTime"":""2022-06-13T13:49:50"",""Id"":""00aa9aa0-cba1-42d0-a8a3-08da4d4395f5"",""Operation"":""HardDelete"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""FFFFFFFFFFFFFFFF"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""180.150.36.168"",""UserId"":""[email protected]"",""ClientIPAddress"":""180.150.36.168"",""ClientInfoString"":""Client=ActiveSync"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxGuid"":""11111111-2222-2222-3333-444444444444"",""MailboxOwnerSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""testtenancy.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""AffectedItems"":[{""Id"":""RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGEW37sAAAJ"",""InternetMessageId"":""[email protected]"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""Action required""}],""CrossMailboxOperation"":false,""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""}}" 0176b00a-27b5-4a4f-5153-08da4e88d686,6/15/2022 4:38:04 AM,4,FileMoved,[email protected],"{""AppAccessContext"":{""CorrelationId"":""417d47a0-701c-1000-5979-46ce8a88a543""},""CreationTime"":""2022-06-15T04:38:04"",""Id"":""0176b00a-27b5-4a4f-5153-08da4e88d686"",""Operation"":""FileMoved"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":4,""UserKey"":""i:0h.f|membership|[email protected]"",""UserType"":0,""Version"":1,""Workload"":""SharePoint"",""ClientIP"":"""",""ObjectId"":""https://testtenancy.sharepoint.com/sites/test/Shared Documents/files/Urban.xlsx"",""UserId"":""[email protected]"",""CorrelationId"":""417d47a0-701c-1000-5979-46ce8a88a543"",""CustomUniqueId"":false,""EventSource"":""SharePoint"",""ItemType"":""File"",""ListId"":""e7362856-6fde-4908-b82a-e6e06e699ff8"",""ListItemUniqueId"":""4d6c1b75-8bc1-477b-b5f4-fe235c61f1df"",""Site"":""00000000-3333-3333-3333-000000000000"",""UserAgent"":"""",""WebId"":""22949a7a-8ce8-4bc0-a70c-7d3eb9dd0920"",""EventData"":""<SourceFileGeo>AUS</SourceFileGeo><TargetFileGeo>AUS</TargetFileGeo><SourceFileUrl>https://testtenancy-my.sharepoint.com/personal/x/Documents/Urban.xlsx</SourceFileUrl><TargetFileUrl>https://testtenancy.sharepoint.com/sites/test/Shared Documents/files/Urban.xlsx</TargetFileUrl><TargetWebUrl>https://testtenancy.sharepoint.com/sites/test</TargetWebUrl><SourceItemId>5b9389eb-4d48-4b5a-adae-9e8e2de9442c</SourceItemId><TargetItemId>4d6c1b75-8bc1-477b-b5f4-fe235c61f1df</TargetItemId><SourceSiteId>cb7fe1d9-2bde-43f8-b4c9-2af0f0b433dd</SourceSiteId><SourceWebId>a05b1dc7-c67a-4828-9393-3659a8ad40b1</SourceWebId>""}" 018fe3b5-b5f8-4673-c39a-08da6c7a5c1f,7/23/2022 7:10:01 AM,3,HardDelete,[email protected],"{""CreationTime"":""2022-07-23T07:10:01"",""Id"":""018fe3b5-b5f8-4673-c39a-08da6c7a5c1f"",""Operation"":""HardDelete"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""FFFFFFFFFFFFFFFF"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""2403:5801:1768:0:d5e4:cc8c:3470:5607"",""UserId"":""[email protected]"",""ClientIPAddress"":""2403:5801:1768:0:d5e4:cc8c:3470:5607"",""ClientInfoString"":""Client=ActiveSync"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxGuid"":""11111111-2222-2222-3333-444444444444"",""MailboxOwnerSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""testtenancy.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""AffectedItems"":[{""Id"":""RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGeYWqOAAAJ"",""InternetMessageId"":""SY4P282MB3551EB74C8C23A34CFD085AECE939@SY4P282MB3551.AUSP282.PROD.OUTLOOK.COM"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""Your email""}],""CrossMailboxOperation"":false,""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""}}" 01ce7848-fd55-4e0b-be32-08da75b918e9,8/4/2022 1:31:47 AM,3,HardDelete,[email protected],"{""CreationTime"":""2022-08-04T01:31:47"",""Id"":""01ce7848-fd55-4e0b-be32-08da75b918e9"",""Operation"":""HardDelete"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""FFFFFFFFFFFFFFFF"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""2403:5801:1768:0:2421:8aa1:b2dc:d23e"",""UserId"":""[email protected]"",""ClientIPAddress"":""2403:5801:1768:0:2421:8aa1:b2dc:d23e"",""ClientInfoString"":""Client=ActiveSync"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxGuid"":""11111111-2222-2222-3333-444444444444"",""MailboxOwnerSid"":""S-1-5-21-1234-1234-1234-1234"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""testtenancy.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""AffectedItems"":[{""Id"":""RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGkbEKDAAAJ"",""InternetMessageId"":""SY4P282MB3551FC12C2EB1892453E701DCE9F9@SY4P282MB3551.AUSP282.PROD.OUTLOOK.COM"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""Your email""}],""CrossMailboxOperation"":false,""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""}}" 01d58cf0-d066-4f5c-1e76-08da74e20371,8/2/2022 11:52:09 PM,6,FilePreviewed,[email protected],"{""AppAccessContext"":{""CorrelationId"":""623257a0-10e2-1000-7a1e-565e8576b57d""},""CreationTime"":""2022-08-02T23:52:09"",""Id"":""01d58cf0-d066-4f5c-1e76-08da74e20371"",""Operation"":""FilePreviewed"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":6,""UserKey"":""i:0h.f|membership|[email protected]"",""UserType"":0,""Version"":1,""Workload"":""SharePoint"",""ClientIP"":""180.150.36.168"",""ObjectId"":""https://testtenancy.sharepoint.com/sites/test/Shared Documents/R1.doc"",""UserId"":""[email protected]"",""CorrelationId"":""623257a0-10e2-1000-7a1e-565e8576b57d"",""DoNotDistributeEvent"":true,""EventSource"":""SharePoint"",""ItemType"":""File"",""ListId"":""e7362856-6fde-4908-b82a-e6e06e699ff8"",""ListItemUniqueId"":""7f21ff7c-074b-42c4-9d0f-22a4932a3b40"",""Site"":""00000000-3333-3333-3333-000000000000"",""UserAgent"":""OneDriveMpc-Transform_Thumbnail/1.0"",""WebId"":""22949a7a-8ce8-4bc0-a70c-7d3eb9dd0920"",""HighPriorityMediaProcessing"":false,""IsManagedDevice"":true,""SourceFileExtension"":""doc"",""SiteUrl"":""https://testtenancy.sharepoint.com/sites/test/"",""SourceFileName"":""R1.doc"",""SourceRelativeUrl"":""Shared Documents//Reports""}"
yes, that's right but even if I change columns in order as I see an example from 509 class I still got this error message mentioned above.
I had to replace any backslash (\) characters in the AuditData JSON with forward slashes (/) to prevent the JSON handler from failing. I don't like to do these things but research and experimentation showed that preserving them was all but impossible to do reliably, and would likely cause issues in Kibana even if they were preserved.
This is ready for testing on the develop branch. To test, please do the following. (Tested on a FOR509 VM, but should work with current public version as well.)
-
systemctl stop logstash -
cd /usr/local/sof-elk -
git checkout develop -
git pull -
systemctl start logstash
Then, place the GUI-extracted CSV in /logstash/office365/ as a *.csv file. Review via Kibana. If this looks good, let me know here and I'll promote that fix to all current operational branches. If there are issues, let me know that here too and I'll crank on it.
Will come back to testing. It's mostly ok. There's a Json parse failure on "movetodeleteditems" so I'll have to get you an example to look at
I wanted to test it on 509 VM but when I'm in develop branch logstash does not start with following error
[2022-09-03T17:23:10,282][FATAL][logstash.runner ] An unexpected error occurred! {:error=>#<LogStash::Error: Don't know how to handle Java::JavaLang::IllegalStateException for PipelineAction::Create<main>>, :backtrace=>["org/logstash/execution/ConvergeResultExt.java:129:in create'", "org/logstash/execution/ConvergeResultExt.java:57:in add'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:380:in `block in converge_state'"]}
[2022-09-03T17:23:10,292][FATAL][org.logstash.Logstash ] Logstash stopped processing because of an error: (SystemExit) exit
org.jruby.exceptions.SystemExit: (SystemExit) exit
I just wanted to mentioned it that something just does not click with 509 VM. I will try to build SOF-ELK from repo and test it.
Did test on VM from repo and logstash is working just fine. Tested UAL parses and work ok-ish. As mentioned by @randomaccess3 - problem with "movetodeleteditems"
Here's an event!
f55e4951-32ed-4c73-2aed-08da111123ad,8/28/2022 11:58:01 PM,3,MoveToDeletedItems,[email protected]"{""CreationTime"":""2022-01-11T23:58:01"",""Id"":""f55e4951-32ed-4c73-2aed-08da895123ad"",""Operation"":""MoveToDeletedItems"",""OrganizationId"":""12341234-1234-1234-1234-123412341234"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""1001100111111111"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""1.1.1.1"",""UserId"":""[email protected]"",""AppId"":""00000002-0000-0ff1-ce00-000000000000"",""ClientIPAddress"":""1.1.1.1"",""ClientInfoString"":""Client=OWA;Action=ViaProxy"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-111111111-1111111111-1111111111-1234567"",""MailboxGuid"":""1234123-1234-1234-1234-1234123412"",""MailboxOwnerSid"":""S-1-5-21-111111111-1111111111-1111111111-1234567"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""test.onmicrosoft.com"",""OriginatingServer"":""SY4P212MB3551 (15.20.4200.000)\r\n"",""SessionId"":""f7111a8d-7d51-4f11-99e9-7e18c7d0911c"",""AffectedItems"":[{""Attachments"":""image001.jpg (2116b); image002.jpg (1111b)"",""Id"":""RgA111BrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAND8AAAosCml+oLISIgtXqbEv8XmAAG2daILAAA1””,””InternetMessageId"":""ME3P282MB2386FF8E2C1117807A211111111111@ME3P282MB1234.AUSP282.PROD.OUTLOOK.COM"",""ParentFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAND8AAAB"",""Path"":""\RSS Feeds""},""Subject"":""RE: Subject""}],""CrossMailboxOperation"":false,""DestFolder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEKAAA1””,””Path"":""\Deleted Items""},""Folder"":{""Id"":""LgAAAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAND8AAA1””,””Path"":""\RSS Feeds""}}"
@randomaccess3 I'm assuming you're missing a , between the UserId and AuditData fields. Going on that assumption as I track this through the parser
following up here - with the modifications listed below, the above log parsed fine. I think this can be closed but will await confirmation from @randomaccess3.
- add comma after
[email protected]and before"{""CreationTime...... - converted fancy quotes to real quotes
Will check now - but the fancy quotes and error with the comma came from me copying the data out of my dataset, replacing stuff manually in Textedit (and messing up the formatting apparently) The data that I put into SOF-ELK wouldnt have those issues as it was direct from MS and failed to parse Will load it up again and see what I can tell
Went through again - most of them parse fine. I have two events that have json parse failures, here's one with redacted contents and I shouldnt have messed up the quotes and commas this time
{"CreationTime":"2022-08-29T02:52:38","Id":"11111be6-690d-46aa-6a46-111111118880","Operation":"MoveToDeletedItems","OrganizationId":"12312311-1111-1111-1111-4098b024fcf6","RecordType":3,"ResultStatus":"Succeeded","UserKey":"11111000F28EEE93","UserType":0,"Version":1,"Workload":"Exchange","ClientIP":"1.1.1.1","UserId":"[email protected]","AppId":"27922004-5251-4030-b22d-91ecd9a37ea4","ClientIPAddress":"1.1.1.1","ClientInfoString":"Client=OutlookService;Outlook-iOS//2.0;","ClientRequestId":"11111","ExternalAccess":false,"InternalLogonType":0,"LogonType":0,"LogonUserSid":"S-1-5-21-369853385-3642537750-1111111111-1111111","MailboxGuid":"11111111-eab6-4892-9a8b-324af691096a","MailboxOwnerSid":"S-1-5-21-369853385-3642537750-3469971377-1111111","MailboxOwnerUPN":"[email protected]","OrganizationName":"testu.onmicrosoft.com","OriginatingServer":"SY4P282MB3551 (15.20.4200.000)/r/n","SessionId":"11111111-c615-4956-a572-65143a7d6f56","AffectedItems":[{"Id":"RgAAAABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAosCml+oLISIgtXqbEv8XmAAGSgWjv1111","InternetMessageId":"11111181d1e084c4-b77ea617-98cc-4fe4-b9a4-3d25d5341111-000000@us-west-2.amazonses.com","ParentFolder":{"Id":"111AAABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAB","Path":"//Inbox"},"Subject":"email subject"}],"CrossMailboxOperation":false,"DestFolder":{"Id":"LgAAAABrQsWN1X22SKsI3ZybZGsPA111sCml+oLISIgtXqbEv8XmAAAAAAEKAAAB","Path":"//Deleted Items"},"Folder":{"Id":"1111AABrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEM1111","Path":"//Inbox"}}
11111111-690d-46aa-6a46-08da89698880,8/29/2022 2:52:38 AM,3,MoveToDeletedItems,[email protected],"{""CreationTime"":""2022-08-29T02:52:38"",""Id"":""11111111-690d-46aa-6a46-08da89691111"",""Operation"":""MoveToDeletedItems"",""OrganizationId"":""11111111-1111-1111-1111-4098b024fcf6"",""RecordType"":3,""ResultStatus"":""Succeeded"",""UserKey"":""11111100F28E1111"",""UserType"":0,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""1.1.1.1"",""UserId"":""[email protected]"",""AppId"":""27922004-5251-4030-b22d-91ecd9a37ea4"",""ClientIPAddress"":""1.1.1.1"",""ClientInfoString"":""Client=OutlookService;Outlook-iOS/2.0;"",""ClientRequestId"":""15210"",""ExternalAccess"":false,""InternalLogonType"":0,""LogonType"":0,""LogonUserSid"":""S-1-5-21-111111111-3642537750-3469971377-1111111"",""MailboxGuid"":""11111111-1111-4892-9a8b-11111111096a"",""MailboxOwnerSid"":""S-1-5-21-111111111-3642537750-3469971377-1111111"",""MailboxOwnerUPN"":""[email protected]"",""OrganizationName"":""test.onmicrosoft.com"",""OriginatingServer"":""SY4P282MB3551 (15.20.4200.000)\r\n"",""SessionId"":""11111111-c615-4956-a572-651411111111"",""AffectedItems"":[{""Id"":""111AABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAo1111+oLISIgtXqbEv8XmAAGSgWjvAAAJ"",""InternetMessageId"":""11111111111184c4-b77ea617-98cc-4fe4-b9a4-3d25d5342b35-000000@x.com"",""ParentFolder"":{""Id"":""1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEMAAAB"",""Path"":""\Inbox""},""Subject"":""test""}],""CrossMailboxOperation"":false,""DestFolder"":{""Id"":""1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEKAAAB"",""Path"":""\Deleted Items""},""Folder"":{""Id"":""111111BrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMA111"",""Path"":""\Inbox""}}"
so strange - that parsed fine on this end. No jsonparsefailure in tags...
{
"_index": "office365-2022.08",
"_type": "_doc",
"_id": "6poWGIMBBXjwRUZMUag5",
"_version": 1,
"_score": 1,
"_source": {
"session_guid": "11111111-c615-4956-a572-651411111111",
"record_id": "11111111-690d-46aa-6a46-08da89698880",
"user_key": "11111100F28E1111",
"workload": "Exchange",
"client_geo": {},
"ClientRequestId": "15210",
"app_id": "27922004-5251-4030-b22d-91ecd9a37ea4",
"version": 1,
"record_type": 3,
"logon_type": 0,
"mailbox_owner_upn": "[email protected]",
"internal_logon_type": 0,
"logon_user_sid": "S-1-5-21-111111111-3642537750-3469971377-1111111",
"user_type": 0,
"ecs": {
"version": "1.12.0"
},
"user_name": "[email protected]",
"@timestamp": "2022-08-29T02:52:38.000Z",
"source_geo": {},
"result_status": "Succeeded",
"source_ip": "1.1.1.1",
"@version": "1",
"originating_server": "SY4P282MB3551 (15.20.4200.000)/r/n",
"agent": {
"ephemeral_id": "284511be-acdb-45b3-9196-ccef2d8c0670",
"name": "sof-elk",
"version": "7.17.1",
"id": "247e7557-4131-40ff-b9ee-32178531784e",
"hostname": "sof-elk",
"type": "filebeat"
},
"operation": "MoveToDeletedItems",
"organization_name": "test.onmicrosoft.com",
"type": "office365",
"log": {
"file": {
"path": "/logstash/office365/test_del2_001.csv"
},
"offset": 0
},
"tags": [
"process_archive",
"filebeat",
"beats_input_codec_plain_applied",
"_geoip_lookup_failure"
],
"folder": {
"Id": "111111BrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMA111",
"Path": "/Inbox"
},
"input": {
"type": "log"
},
"host": {
"name": "sof-elk"
},
"mailbox_owner_sid": "S-1-5-21-111111111-3642537750-3469971377-1111111",
"destination_folder": {
"Id": "1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEKAAAB",
"Path": "/Deleted Items"
},
"ips": [
"1.1.1.1",
"1.1.1.1"
],
"client_ip": "1.1.1.1",
"cross_mailbox_operation": false,
"external_access": false,
"client_info_string": "Client=OutlookService;Outlook-iOS/2.0;",
"report_guid": "11111111-690d-46aa-6a46-08da89691111",
"affected_items": [
{
"InternetMessageId": "11111111111184c4-b77ea617-98cc-4fe4-b9a4-3d25d5342b35-000000@x.com",
"ParentFolder": {
"Id": "1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEMAAAB",
"Path": "/Inbox"
},
"Subject": "test",
"Id": "111AABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAo1111+oLISIgtXqbEv8XmAAGSgWjvAAAJ"
}
],
"organization_guid": "11111111-1111-1111-1111-4098b024fcf6",
"mailbox_guid": "11111111-1111-4892-9a8b-11111111096a"
},
"fields": {
"agent.version.keyword": [
"7.17.1"
],
"session_guid": [
"11111111-c615-4956-a572-651411111111"
],
"client_info_string.keyword": [
"Client=OutlookService;Outlook-iOS/2.0;"
],
"mailbox_owner_upn": [
"[email protected]"
],
"host.name.keyword": [
"sof-elk"
],
"affected_items.ParentFolder.Path": [
"/Inbox"
],
"cross_mailbox_operation": [
false
],
"affected_items.Subject.keyword": [
"test"
],
"destination_folder.Id.keyword": [
"1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEKAAAB"
],
"affected_items.Id": [
"111AABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAo1111+oLISIgtXqbEv8XmAAGSgWjvAAAJ"
],
"type": [
"office365"
],
"agent.hostname.keyword": [
"sof-elk"
],
"source_ip": [
"1.1.1.1"
],
"originating_server": [
"SY4P282MB3551 (15.20.4200.000)/r/n"
],
"affected_items.ParentFolder.Path.keyword": [
"/Inbox"
],
"folder.Path": [
"/Inbox"
],
"user_type": [
0
],
"folder.Path.keyword": [
"/Inbox"
],
"ecs.version.keyword": [
"1.12.0"
],
"affected_items.ParentFolder.Id.keyword": [
"1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEMAAAB"
],
"agent.name": [
"sof-elk"
],
"client_ip": [
"1.1.1.1"
],
"host.name": [
"sof-elk"
],
"app_id": [
"27922004-5251-4030-b22d-91ecd9a37ea4"
],
"agent.id.keyword": [
"247e7557-4131-40ff-b9ee-32178531784e"
],
"mailbox_owner_sid": [
"S-1-5-21-111111111-3642537750-3469971377-1111111"
],
"input.type": [
"log"
],
"organization_name": [
"test.onmicrosoft.com"
],
"log.offset": [
0
],
"agent.hostname": [
"sof-elk"
],
"organization_guid": [
"11111111-1111-1111-1111-4098b024fcf6"
],
"version": [
1
],
"ips": [
"1.1.1.1",
"1.1.1.1"
],
"affected_items.ParentFolder.Id": [
"1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEMAAAB"
],
"tags": [
"process_archive",
"filebeat",
"beats_input_codec_plain_applied",
"_geoip_lookup_failure"
],
"user_name.keyword": [
"[email protected]"
],
"user_key.keyword": [
"11111100F28E1111"
],
"agent.id": [
"247e7557-4131-40ff-b9ee-32178531784e"
],
"destination_folder.Path.keyword": [
"/Deleted Items"
],
"result_status": [
"Succeeded"
],
"ecs.version": [
"1.12.0"
],
"mailbox_guid": [
"11111111-1111-4892-9a8b-11111111096a"
],
"agent.version": [
"7.17.1"
],
"affected_items.Id.keyword": [
"111AABrQsWN1X22SKsI3ZybZGsPBwAosCml+oLISIgtXqbEv8XmAAAAAAEMAAAo1111+oLISIgtXqbEv8XmAAGSgWjvAAAJ"
],
"destination_folder.Id": [
"1111111rQsWN1X22SKsI3ZybZGsPAQAosCml+oLISI111qbEv8XmAAAAAAEKAAAB"
],
"external_access": [
false
],
"originating_server.keyword": [
"SY4P282MB3551 (15.20.4200.000)/r/n"
],
"input.type.keyword": [
"log"
],
"affected_items.Subject": [
"test"
],
"user_name": [
"[email protected]"
],
"tags.keyword": [
"process_archive",
"filebeat",
"beats_input_codec_plain_applied",
"_geoip_lookup_failure"
],
"user_key": [
"11111100F28E1111"
],
"folder.Id": [
"111111BrQsWN1X22SKsI3ZybZGsPAQAosCml+oLISIgtXqbEv8XmAAAAAAEMA111"
],
"app_id.keyword": [
"27922004-5251-4030-b22d-91ecd9a37ea4"
],
"affected_items.InternetMessageId": [
"11111111111184c4-b77ea617-98cc-4fe4-b9a4-3d25d5342b35-000000@x.com"
],
"organization_name.keyword": [
"test.onmicrosoft.com"
],
"internal_logon_type": [
0
],
"agent.type": [
"filebeat"
],
"record_id.keyword": [
"11111111-690d-46aa-6a46-08da89698880"
],
"client_info_string": [
"Client=OutlookService;Outlook-iOS/2.0;"
],
"@version": [
"1"
],
"log.file.path.keyword": [
"/logstash/office365/test_del2_001.csv"
],
"agent.type.keyword": [
"filebeat"
],
"agent.ephemeral_id.keyword": [
"284511be-acdb-45b3-9196-ccef2d8c0670"
],
"agent.name.keyword": [
"sof-elk"
],
"workload": [
"Exchange"
],
"ClientRequestId.keyword": [
"15210"
],
"logon_user_sid": [
"S-1-5-21-111111111-3642537750-3469971377-1111111"
],
"logon_type": [
0
],
"record_type": [
3
],
"record_id": [
"11111111-690d-46aa-6a46-08da89698880"
],
"ClientRequestId": [
"15210"
],
"@timestamp": [
"2022-08-29T02:52:38.000Z"
],
"mailbox_owner_upn.keyword": [
"[email protected]"
],
"log.file.path": [
"/logstash/office365/test_del2_001.csv"
],
"agent.ephemeral_id": [
"284511be-acdb-45b3-9196-ccef2d8c0670"
],
"destination_folder.Path": [
"/Deleted Items"
],
"report_guid": [
"11111111-690d-46aa-6a46-08da89691111"
],
"affected_items.InternetMessageId.keyword": [
"11111111111184c4-b77ea617-98cc-4fe4-b9a4-3d25d5342b35-000000@x.com"
],
"operation": [
"MoveToDeletedItems"
]
}
}
I figured it out! In my case data, this is the subject for the record that has the JSON parsing error. In what I provided you I just removed the subject content because sensitivity.
""Subject"":""XXXXXXX \""XXXXXXXX\""!""
Do we know if the new UAL format has been updated in SOF-ELK? this is an example of the new CSV "RecordType","CreationDate","UserIds","Operations","AuditData","ResultIndex","Re sultCount","Identity","IsValid","ObjectState"
Very unlikely - does it have a JSON export rather than a CSV export? Supporting that is probably preferable
Do we know if the new UAL format has been updated in SOF-ELK? this is an example of the new CSV "RecordType","CreationDate","UserIds","Operations","AuditData","ResultIndex","Re sultCount","Identity","IsValid","ObjectState"
I just processed logs exported from Purview the other day and they had the following headers: RecordId CreationDate RecordType Operation UserId AuditData
@hackcalde23 how did you get an export with those headers?
There definitely is no handler for that format, but we haven't seen it before - as @randomaccess3 said.