Dear Elastic colleagues && folks,
Recently there is a request from our security team that they need to collect the Microsoft exchange message tracking logs from our exchange servers,
every line from original exchange logs is the csv-based format and separated by a comma(,)
and there are 30 fields in total. but not every field have values, the fields without value are expressed by a comma(,) as well.
I choose Filebeat to collect the local exchange logs and sent to logstash.
My question is that How logstash split field message into 30 fields separately with Jason format from logs that collected by Filebeat?
Then it’s easy for our employee to search for any fields that they wanted.
I definitely appreciated that If anybody can give a detail configuration file for the logstash pipeline.
P.S.
Enviroment Description:
filebeat version: V5.5.1
logstash version: V5.5.1
Log Description:
collected log: Microsoft Exchange messages tracking log
numers of raw log fields: 30
log format from raw log: csv
log field separator: ,
Collected exchange log by filebeat and output to local file as following:
{"@timestamp":"2018-03-30T08:56:36.909Z","beat":{"hostname":"testexchange01","name":"testexchange01","version":"5.5.1"},"input_type":"log","message":"2018-03-26T04:58:43.592Z,fe80::90cc:10e2:b67e:8495%20,testexchange01,,,\"MDB:6c0c4cd6-7e89-4770-9658-b529eccf9358, Mailbox:db39206e-b989-45cf-bc10-af063b1f2f35, Event:8615100, MessageClass:IPM.Note.SMIME.MultipartSigned, CreationTime:2018-03-26T04:58:43.444Z, ClientType:MOMT, SubmissionAssistant:MailboxTransportSubmissionEmailAssistant\",,STOREDRIVER,NOTIFYMAPI,,,,,,,,,,,irina.vassilyeva@test.com,,2018-03-26T04:58:43.444Z;LSRV=testexchange01.tls.ad:TOTAL-SUB=0.148|SA=0.148|MTSS-PEN=0.000,,,,,S:ItemEntryId=00-00-00-00-80-F6-84-83-45-EB-E0-47-AE-E0-39-92-79-C0-C6-84-07-00-4D-8D-60-8B-A2-F1-B5-47-82-29-82-76-47-24-E2-DB-00-00-00-00-01-0B-00-00-4D-8D-60-8B-A2-F1-B5-47-82-29-82-76-47-24-E2-DB-00-00-2B-17-16-23-00-00,,4e75b863-4186-466a-a2b4-08d592d63fec,15.01.1034.026","offset":107222,"source":"C:\\Program Files\\Microsoft\\Exchange Server\\V15\\TransportRoles\\Logs\\MessageTracking\\MSGTRKMS2018032604-1.LOG","type":"log"}
{"@timestamp":"2018-03-30T08:56:36.909Z","beat":{"hostname":"testexchange01","name":"testexchange01","version":"5.5.1"},"input_type":"log","message":"2018-03-26T04:58:43.630Z,fe80::90cc:10e2:b67e:8495,testexchange01.tls.ad,fe80::90cc:10e2:b67e:8495%20,testexchange01,\"MDB:6c0c4cd6-7e89-4770-9658-b529eccf9358, Mailbox:db39206e-b989-45cf-bc10-af063b1f2f35, Event:8615100, MessageClass:IPM.Note.SMIME.MultipartSigned, CreationTime:2018-03-26T04:58:43.444Z, ClientType:MOMT, SubmissionAssistant:MailboxTransportSubmissionEmailAssistant, ServerMdbConnectionId:08D5811A991435BF\",,STOREDRIVER,RECEIVE,28538,\u003c59928cc29aa74e0ab339da74ad3842ef@test.com\u003e,523f9788-357e-4af6-4968-08d592d63ff2,sherry.pang@test.com;raushan.karamurza@test.com;yuliya.melnik@test.com,To;Cc;Cc,118167,3,,,RE: [kz2uk][OPS] Almaty and Astana timesheet March 2018,irina.vassilyeva@test.com,irina.vassilyeva@test.com,04I: ,Originating,,1.1.98.77,1.1.86.50,S:MailboxDatabaseGuid=6c0c4cd6-7e89-4770-9658-b529eccf9358;S:ItemEntryId=00-00-00-00-80-F6-84-83-45-EB-E0-47-AE-E0-39-92-79-C0-C6-84-07-00-4D-8D-60-8B-A2-F1-B5-47-82-29-82-76-47-24-E2-DB-00-00-00-00-01-0B-00-00-4D-8D-60-8B-A2-F1-B5-47-82-29-82-76-47-24-E2-DB-00-00-2B-17-16-23-00-00;S:DeliveryPriority=Normal;S:AccountForest=tls.ad,Email,44aed7d5-cd45-4b07-7083-08d592d63ff2,15.01.1034.026","offset":108413,"source":"C:\\Program Files\\Microsoft\\Exchange Server\\V15\\TransportRoles\\Logs\\MessageTracking\\MSGTRKMS2018032604-1.LOG","type":"log"}
{"@timestamp":"2018-03-30T08:56:36.909Z","beat":{"hostname":"testexchange01","name":"testexchange01","version":"5.5.1"},"input_type":"log","message":"2018-03-26T04:58:43.976Z,fe80::90cc:10e2:b67e:8495%20,testexchange01,,testexchange01.tls.ad,\"MDB:6c0c4cd6-7e89-4770-9658-b529eccf9358, Mailbox:db39206e-b989-45cf-bc10-af063b1f2f35, Event:8615100, MessageClass:IPM.Note.SMIME.MultipartSigned, CreationTime:2018-03-26T04:58:43.444Z, ClientType:MOMT, SubmissionAssistant:MailboxTransportSubmissionEmailAssistant\",,STOREDRIVER,SUBMIT,,\u003c59928cc29aa74e0ab339da74ad3842ef@test.com\u003e,523f9788-357e-4af6-4968-08d592d63ff2,sherry.pang@test.com;raushan.karamurza@test.com;yuliya.melnik@test.com,,,3,,,RE: [kz2uk][OPS] Almaty and Astana timesheet March 2018,irina.vassilyeva@test.com,,2018-03-26T04:58:43.444Z;LSRV=testexchange01.tls.ad:TOTAL-SUB=0.532|SA=0.148|MTSS=0.384(MTSSD=0.384(MTSSDA=0.005|MTSSDC=0.016|SDSSO=0.340(SMSC=0.007(X-SMSDR=0.001)|SMS=0.330)|X-MTSSDPL=0.013|X-MTSSDSS=0.006)),Originating,,1.1.98.77,,S:ItemEntryId=00-00-00-00-80-F6-84-83-45-EB-E0-47-AE-E0-39-92-79-C0-C6-84-07-00-4D-8D-60-8B-A2-F1-B5-47-82-29-82-76-47-24-E2-DB-00-00-00-00-01-0B-00-00-4D-8D-60-8B-A2-F1-B5-47-82-29-82-76-47-24-E2-DB-00-00-2B-17-16-23-00-00;S:DeliveryPriority=Normal,Email,8c0603ef-394c-41b7-4b02-08d592d64027,15.01.1034.026","offset":109593,"source":"C:\\Program Files\\Microsoft\\Exchange Server\\V15\\TransportRoles\\Logs\\MessageTracking\\MSGTRKMS2018032604-1.LOG","type":"log"}
The result need to be seprated for field message as below:
Note: Both keywords "key" and "value" are just header I describe here,
For key from a to ad is the column I wanted(assumption).
If there are results for column value, we'll fill it with results,
If there are no results for column value, just need to fill with double quotations.
key value
a "true" or ""
b "true" or ""
c "true" or ""
d "true" or ""
e "true" or ""
f "true" or ""
g "true" or ""
h "true" or ""
i "true" or ""
j "true" or ""
k "true" or ""
l "true" or ""
m "true" or ""
n "true" or ""
o "true" or ""
p "true" or ""
q "true" or ""
r "true" or ""
s "true" or ""
t "true" or ""
u "true" or ""
v "true" or ""
w "true" or ""
x "true" or ""
y "true" or ""
z "true" or ""
aa "true" or ""
ab "true" or ""
ac "true" or ""
ad "true" or ""