topic Re: splunk concatenate field in table in Splunk Search

splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 09:52:14 GMT

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 <vivek.sood@swiftsecuritas.in>
<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 '<2132122449.43046.1600730091044.JavaMail.zimbra@swiftsecuritas.in>'
<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 i=@swiftsecuritas.in)
<38>Sep 22 02:15:13 mail_logs: Info: MID 3035876 SPF: mailfrom identity vivek.sood@swiftsecuritas.in Pass (v=spf1)
<38>Sep 22 02:15:11 mail_logs: Info: MID 3035876 SPF: helo identity postmaster@mx.gulshanindia.com None
<38>Sep 22 02:15:11 mail_logs: Info: MID 3035876 ICID 1856276 RID 0 To: <info@mycompany.com>
<38>Sep 22 02:15:11 mail_logs: Info: MID 3035876 ICID 1856276 From: <vivek.sood@swiftsecuritas.in>
<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

Re: splunk concatenate field in table

ITWhisperer — Tue, 22 Sep 2020 09:58:47 GMT

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

| stats count

Count by Sender

| stats count by Sender

Re: splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 10:16:45 GMT

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

vivek.sood@swiftsecuritas.in,info@mycompany.com,Revised Order 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

Re: splunk concatenate field in table

ITWhisperer — Tue, 22 Sep 2020 10:12:15 GMT

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

Re: splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 10:37:27 GMT

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,vivek.sood@swiftsecuritas.in,info@mycompany.com,Revised Order 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

vivek.sood@swiftsecuritas.in is in a line,

each field are in different lines

Re: splunk concatenate field in table

ITWhisperer — Tue, 22 Sep 2020 10:35:03 GMT

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

Re: splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 10:42:21 GMT

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,vivek.sood@swiftsecuritas.in,info@mycompany.com,Revised Order 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

3035879,vivek.sood2@swiftsecuritas.in,info2@mycompany.com,Invoice 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-B

3035880,vivek.sood3@swiftsecuritas.in,info@mycompany.com,Invoice2 21-09-20, "Virus" (a/v verdict:VIRAL), CXmail/RtfObf-D

Re: splunk concatenate field in table

ITWhisperer — Tue, 22 Sep 2020 10:42:38 GMT

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

Re: splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 10:52:02 GMT

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

Re: splunk concatenate field in table

ITWhisperer — Tue, 22 Sep 2020 10:46:09 GMT

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

Re: splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 11:48:46 GMT

I want to check MID with the value :

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

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

Re: splunk concatenate field in table

ITWhisperer — Tue, 22 Sep 2020 11:55:09 GMT

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]+)"

Re: splunk concatenate field in table

silverem78 — Tue, 22 Sep 2020 13:48:47 GMT

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