splunk concatenate field in table

[silverem78]

Hi,

As newcomer to splunk , i have the following ironport log :

<38>Sep 22 02:15:35 mail_logs: Info: Message finished MID 3035876 done
<38>Sep 22 02:15:35 mail_logs: Info: MID 3035876 quarantined to "Virus" (a/v verdict:VIRAL)
<38>Sep 22 02:15:34 mail_logs: Info: MID 3035877 was generated based on MID 3035876 by antivirus
<38>Sep 22 02:15:32 mail_logs: Info: MID 3035876 attachment 'Revised=20Order.doc'
<38>Sep 22 02:15:32 mail_logs: Info: MID 3035876 antivirus positive 'CXmail/RtfObf-D'
<38>Sep 22 02:15:32 mail_logs: Info: MID 3035876 interim AV verdict using Sophos VIRAL
<38>Sep 22 02:15:32 mail_logs: Info: MID 3035876 was too big (1456210/1048576) for scanning by CASE
<38>Sep 22 02:15:32 mail_logs: Info: MID 3035876 matched all recipients for per-recipient policy DEFAULT in the inbound table
<38>Sep 22 02:15:31 mail_logs: Info: MID 3035876 ready 1456210 bytes from <[email protected]>
<38>Sep 22 02:15:31 mail_logs: Info: MID 3035876 Subject 'Revised Order 21-09-20'
<38>Sep 22 02:15:31 mail_logs: Info: MID 3035876 Message-ID '<[email protected]>'
<38>Sep 22 02:15:31 mail_logs: Info: MID 3035876 DMARC: Verification passed
<38>Sep 22 02:15:31 mail_logs: Info: MID 3035876 DMARC: Message from domain swiftsecuritas.in, DMARC pass (SPF aligned True, DKIM aligned True)
<38>Sep 22 02:15:31 mail_logs: Info: MID 3035876 DKIM: pass signature verified (d=swiftsecuritas.in s=73FEA6D0-E5D5-11EA-A7BE-617208D79BCE [email protected])
<38>Sep 22 02:15:13 mail_logs: Info: MID 3035876 SPF: mailfrom identity [email protected] Pass (v=spf1)
<38>Sep 22 02:15:11 mail_logs: Info: MID 3035876 SPF: helo identity [email protected] None
<38>Sep 22 02:15:11 mail_logs: Info: MID 3035876 ICID 1856276 RID 0 To: <[email protected]>
<38>Sep 22 02:15:11 mail_logs: Info: MID 3035876 ICID 1856276 From: <[email protected]>
<38>Sep 22 02:15:11 mail_logs: Info: Start MID 3035876 ICID 1856276

I have extract the field and i  want to create a table to get statistic:

table sender,message_subject,recipient,quarantine_dest,reason,virus_vendor_category

When i try it, i got a table per one line. How to concatenate all line to get all statistics, please

Rgds

silverem

[silverem78]

I want in fact a table which give me per line for every threat per user the follwing details

example:

sender,message_subject,recipient,reason,virus_vendor_category

[email protected],[email protected],Revised Order 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

[ITWhisperer]

That doesn't really explain what statistic you want. Can you give an example of what you would like the table to look like?

[silverem78]

Example:  in one line get the following extract from multiple line in the search: for each specific MID, display the associated field sender,message_subject,recipient,reason,virus_vendor_category

,MID, sender,message_subject,recipient,reason,virus_vendor_category

3035876,[email protected],[email protected],Revised Order 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

[email protected] is in a line,

each field are in different lines

[ITWhisperer]

Which field identifies these as all being part of the same threat?

| stats values(sender) as sender, values(message_subject) as message_subject, values(recipient) as recipient, values(reason) as reason, values(virus_vendor_category) by virus_vendor_category by id
| fields - id

[silverem78]

Ok thank you.

Example:  in one line get the following extract from multiple line in the search: for each specific MID, display the associated field sender,message_subject,recipient,reason,virus_vendor_category

,MID, sender,message_subject,recipient,reason,virus_vendor_category

3035876,[email protected],[email protected],Revised Order 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

3035879,[email protected],[email protected],Invoice 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-B

3035880,[email protected],[email protected],Invoice2 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

[ITWhisperer]

| stats values(sender) as sender, values(message_subject) as message_subject, values(recipient) as recipient, values(reason) as reason, values(virus_vendor_category) by virus_vendor_category by MID
| fields - MID

The fields line removes the MID from the table if you don't want to show that

[silverem78]

MID is not a field but is displayed in the line. So i think i need to use regex expression.

For each specific MID displayed in each line,   diplays in a table , MID id and associated stats values

[ITWhisperer]

What is the field name that you have extracted the MID into?

[silverem78]

I want to check MID with the value :

14:44:56 mail_logs: Info: MID 3037591 SPF: mailfrom

|rex "\s+MID\s+\<\|?(?<MID>[^\>]+)" ?

[ITWhisperer]

Based on the example log, there are no brackets around MID value and I suspect you just want the MID near the beginning of the log, so try

| rex "info:\s+MID\s+(?<MID>[^\s]+)"

[silverem78]

I found what i want :

index = * sourcetype="cisco:esa:textmail" | eventstats values(src) AS cs_ip BY icid
| eventstats values(dest) AS ss_ip BY dcid
| eval recipient_count=mvcount(recipient)
| stats values(internal_message_id) AS tmpMID
values(icid) AS icid
values(sender) AS sender
values(recipient) AS recipient
values(message_size) AS message_size values(antivirus_status) as antivirus_status
values(quarantine_dest) as quarantine_dest values(reason) as reason values(virus_vendor_category) as virus_vendor_category
values(response) AS response
values(message_subject) as message_subject
values(cs_ip) AS cs_ip
values(ss_ip) AS ss_ip
values(dcid) AS dcid BY internal_message_id
| eval recipient_count=mvcount(recipient)
| eval mid=tmpMID
| mvexpand mid
| eventstats values(tmpMID) AS tmp BY mid | eval t=mvjoin(tmp, " ")
| stats values(sender) AS sender values(recipient) AS recipient
values(message_subject) as subject values(antivirus_status) as antivirus_status
values(quarantine_dest) as quarantine_dest values(reason) as reason values(virus_vendor_category) as virus_vendor_category
max(message_size) AS message_size
max(recipient_count) AS recipient_count
values(internal_message_id) AS internal_message_id
values(dcid) AS dcid
values(response) AS response
values(cs_ip) AS cs_ip
values(ss_ip) AS ss_ip BY icid

[ITWhisperer]

What statistic do you want? Just a count of all events?

| stats count

Count by Sender

| stats count by Sender