Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Getting Data In

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Splunk Administration
- :
- Getting Data In
- :
- Combine data across multiple sources and then spli...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Combine data across multiple sources and then split answer to separate rows when exported to csv

newill

New Member

08-20-2018
02:49 PM

Good afternoon,

I am trying to take data from multiple sourcestypes, combine it by a common field and then output it to one entry per line when exporting to CSV. I'm having difficulty because there are several fields but only a couple have multiple values. The fields with multiple values show up in one cell. I have tried several suggestions I have come across in searching, but none of them seem to do what I'm attempting.

To start with, another organization hosts the SPLUNK instance, so I do not have access to any back end modifications such as props.conf. I am able to run searches and create dashboards, that is about it.

We have 1 index assigned to this data, and 4 source types. The data I need is spread across all 4 source types and there is one common field (key) between the four. Three of the four source types will return a single event per key, but the 4th can return multiple events per key. When I run my search using stats, I get the data from the first three pretty in a line, then the fields from the fourth will have multiple lines per row. When exported, these show up as a single cell in excel. Hope this makes sense.

Example:

Sourcetype1 contains Fielda Fieldb Fieldc

Sourcetype2 contains Fielda Fieldd Fielde

Sourcetype3 contains Fielda Fieldf Fieldg

Sourcetype4 contains Fielda FieldH FieldI FieldJ

index=* [search index=* Search_criteria | table Fielda | rename Fielda as query] |stats values(*) as * by Fielda

| stats list(Fieldb) as Fieldb, list(Fieldc) as Filedc, list(fieldd) as Fieldd, list(fielde) as Fielde, list(fieldf) as Fieldf, list(Fieldg) as Fieldg, list(FieldH) as FieldH, list(FieldI) as FieldI, list(FieldJ) as FieldJ by Fielda

Result would look like:

Fieldb Fieldc Fieldd Fielde Fieldf Fieldg FieldH FieldI FieldJ

A1 A1 A1 A1 A1 A1 A1 A1 A1

A1.1 A1.1

A2 A2 A2 A2 A2 A2 A2 A2 A2

A2.1 A2.1

A2.2 A2.2

A3 A3 A3 A3 A3 A3 A3 A3 A3

A4 A4 A4 A4 A4 A4 A4 A4 A4

A5 A5 A5 A5 A5 A5 A5 A5 A5

A5.1 A5.1

A5.2 A5.2

And I need it to look like this when exported to CSV:

Fieldb Fieldc Fieldd Fielde Fieldf Fieldg FieldH FieldI FieldJ

A1 A1 A1 A1 A1 A1 A1 A1 A1

A1 A1 A1 A1 A1 A1 A1 A1.1 A1.1

A2 A2 A2 A2 A2 A2 A2 A2 A2

A2 A2 A2 A2 A2 A2 A2 A2.1 A2.1

A2 A2 A2 A2 A2 A2 A2 A2.2 A2.2

A3 A3 A3 A3 A3 A3 A3 A3 A3

A4 A4 A4 A4 A4 A4 A4 A4 A4

A5 A5 A5 A5 A5 A5 A5 A5 A5

A5 A5 A5 A5 A5 A5 A5 A5.1 A5.1

A5 A5 A5 A5 A5 A5 A5 A5.2 A5.2

I've tried using transaction instead of the stats command. I've also tried adding "by fiedla FieldI FieldJ" at the end of the stats and that just seems to created multiple entries for each possible combination of .1 and .2 answers. Any help that could be offered would be greatly appreciated.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

08-21-2018
11:41 AM

If there were only one, you could use this...

```
| mvexpand FieldJ
```

For multiple mv fields, you need to do this...

```
| eval CountI=coalesce(mvcount(FieldI),0)+1
| eval CountJ=coalesce(mvcount(FieldJ),0)+1
| eval MyFan=if(CountI>CountJ,CountI,CountJ)
| mvexpand MyFan
| eval FieldI=mvindex(FieldI,MyFan)
| eval FieldJ=mvindex(FieldJ,MyFan)
```

Then just add three more lines for each additional mv field.

```
| eval CountI=coalesce(mvcount(FieldI),0)+1
| eval CountJ=coalesce(mvcount(FieldJ),0)+1
| eval MyFan=if(CountI>CountJ,CountI,CountJ)
| eval CountK=coalesce(mvcount(FieldK),0)+1
| eval MyFan=if(MyFan>CountK,MyFan,CountK)
| mvexpand MyFan
| eval FieldI=mvindex(FieldI,MyFan)
| eval FieldJ=mvindex(FieldJ,MyFan)
| eval FieldK=mvindex(FieldK,MyFan)
```

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

newill

New Member

08-21-2018
12:10 PM

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

08-21-2018
11:33 AM

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

newill

New Member

08-21-2018
08:29 AM

I just realized how horrible that example turned out after I hit save. Let me try a picture.

State of Splunk Careers

Find out what your skills are worth!

Read the report >