topic Re: Combine data across multiple sources and then split answer to separate rows when exported to csv in Getting Data In

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

newill — Mon, 20 Aug 2018 21:49:08 GMT

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.

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

newill — Tue, 21 Aug 2018 15:29:47 GMT

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

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

DalJeanis — Tue, 21 Aug 2018 18:33:44 GMT

If you put four spaces to the left of your code, the interface will leave them alone. You can also mark them with the code button on your browser (101 010)

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

DalJeanis — Tue, 21 Aug 2018 18:41:25 GMT

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)

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

newill — Tue, 21 Aug 2018 19:10:05 GMT

I added this, now fieldI is blank and FieldJ still has multiple values. I don't really understand what this code is doing. FieldI and Field J will always have the same count as they are tied together in that sourcetype.