Splunk Search

How to extract field name and field values from multi-value field?

dm1
Contributor

I am indexing email data that Splunk reads from an inbox folder (via TA-mailclient). Those emails contain a csv file that comes as file attachment to the email. 

Below is an example where the field name of the attachment is file_content and the field value is below:

 

 

Stopped by Reputation Filtering,Stopped as Invalid Recipients,Spam Detected,Virus Detected,Stopped by Content Filter,Total Threat Messages,Clean Messages,Total Attempted Messages
9.28068485506,0.0,45.1350500141,0.00114191624597,1.53311465023,55.9499914356,44.0500085644,--
251946,0,1225297,31,41620,1518894,1195841,2714735

 

 

 

I want to be able to manipulate the results to look like below:

Stopped by Reputation Filtering Stopped as Invalid Recipients Spam Detected Virus Detected Stopped by Content Filter Total Threat Messages Clean Messages Total Attempted Messages
9.280684855 0 45.13505001 0.001141916 1.53311465 55.94999144 44.05000856 --
251946 0 1225297 31 41620 1518894 1195841 2714735

 

Can someone please advise how to achieve this ?

Labels (5)
Tags (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

I could swear that I had seen the same question recently but cannot find it.  I also do not know an elegant solution. (The ask is really to extract fields from a field of CSV.)  So, here we go:

 

| eval file_content = split(file_content, "
") ``` chop field into lines ```
| eval header = mvindex(file_content, 0)
| eval file_content = mvindex(file_content, 1, -1) ``` get values ```
| mvexpand file_content ``` one value per line ```
| eval _raw = mvzip(split(header, ","), split(file_content, ","), "=") ``` pair headers with values ```
| kv pairdelim="
" kvdelim="="
| fields - _raw file_content header

 

 The sample data will give

Clean MessagesDetectedFilterFilteringMessagesRecipientsSpam_DetectedStopped as Invalid_RecipientsStopped_by_Content_FilterStopped_by_Reputation_FilteringTotal_Attempted_MessagesTotal_Threat_MessagesVirus_Detected
44.050008564445.13505001411.533114650239.2806848550655.94999143560.045.13505001410.01.533114650239.28068485506--55.94999143560.00114191624597
11958411225297416202519461518894012252970416202519462714735151889431

dm1
Contributor

@yuanliu Thanks for your prompt reply.

I am trying to understand your query. 

When I fully ran your query, the file_content field and values entirely disappeared. Results were just empty lines.

Then I tried to execute query line by line.

| eval file_content = split(file_content, "") ``` chop field into lines ```

1st attempt with the above returned file_content values with each character as single value like, 

s

t

o

p

p

e

d

After I added a "," between double quotes | eval file_content = split(file_content, ",") ``` chop field lines ```, it returned each cell value as single value

like 

Stopped by Reputation Filtering
Stopped as Invalid Recipients
etc

 still figuring out...

0 Karma

yuanliu
SplunkTrust
SplunkTrust

@dm1 As ITWhisperer explained, the split character is a literal newline, which you can enter in search window by holding down Ctrl (or Mac 'control') key while pressing "Enter" (or Mac 'return').  Similarly, pairdelim is also a literal newline.

The last line removes file_content just to show results more clearly.  It is not part of the solution.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

As @yuanliu  showed, the split has to be by new line, you have removed this in your search

| eval file_content = split(file_content, "
") ``` chop field into lines ```

This assume that file_content is a single value with new lines delimiting the rows of the csv, not a multi-value field. If it is a multi-value field already, then the split is unnecessary.

Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...