topic Re: How can you make data to come in different rows using mvzip? in Splunk Search

How can you make data to come in different rows using mvzip?

anooshac — Tue, 15 Feb 2022 16:32:41 GMT

Hi all,

I have a query which gives this kind of table.

Name Date Status Task SubGroup

A 14-02-22 PASS a a1

b b1

The data will come together and which but i want separate rows for all the data. Also there are subgroup for some tasks but with this result it one cannot be able to differentiate between them.

I have tried using mvzip like this,

I am not getting why a error comes in eval command as expected ). I don't know whether it is a small mistake, i have tried alot but not able to solve this.

Re: make data to come in different rows using mvzip.

ITWhisperer — Tue, 15 Feb 2022 06:49:41 GMT

There doesn't appear to be anything wrong with the eval example you have given. How close is it to the real search?

Btw, I think you may be missing a table (or fields) command at the beginning of the last line, and the table command in the middle is redundant.

Also, it is not clear what you events look like at the beginning .........| - if the "table" is supposed to represent a single event with multivalue fields Group Task and SubGroup, then mvzip will lose some of the data since there are only two values in Group, i.e. these will be zipped with two (of the three) values in Task.

Re: How can you make data to come in different rows using mvzip?

PickleRick — Tue, 15 Feb 2022 07:06:24 GMT

If Group, Task and SubGroup ar multivalued things, the only reasonable thing to do is mvexpand them

<your query>
| mvexpand Group
| mvexpand Task
| mvexpand SubGrou

Remember that if you from this single line you'll get 18 resulting lines after such operation.

Re: How can you make data to come in different rows using mvzip?

yuanliu — Tue, 15 Feb 2022 07:26:54 GMT

...
| Name Date Status Group Task SubGroup

Is that last line missing a command like table?

| table Name Date Status Group Task SubGroup

Re: How can you make data to come in different rows using mvzip?

anooshac — Tue, 15 Feb 2022 08:26:24 GMT

Hi,

I tried using mvexpand but it is giving some unwanted results. The group has Tasks and Task further has SubGroup. By using only mvexpand the data will not come as expected.

Re: How can you make data to come in different rows using mvzip?

anooshac — Tue, 15 Feb 2022 08:27:33 GMT

Sorry, It was by mistake while writing this question. The error comes in the mvzip part as expected ")".

Re: make data to come in different rows using mvzip.

anooshac — Tue, 15 Feb 2022 11:04:30 GMT

Hi, i have used table command. It was a mistake while writing the question here.

I want a table that consists of Name, Date, Status, Task,SubGroup.

The Name has Task and the Task further has SubGroup. When i directly put them in to the table all data will get mixed. It is difficult to differentiate which Task the SubGroup belongs to and which Group the Task belongs to. And the error comes in the mvzip command as "Error in 'eval' command: The expression is malformed. Expected )." Not getting what went wrong.

Re: make data to come in different rows using mvzip.

ITWhisperer — Tue, 15 Feb 2022 08:54:27 GMT

Please can you share you full search (preferably in a code block </>)?

Re: make data to come in different rows using mvzip.

anooshac — Tue, 15 Feb 2022 10:56:21 GMT

sure. This is the query which i am using. I am using JSON input.

index= "abc" sourcetype="xyz"

|eval "Date"=strftime(TASK_TIME/1000,"%F %H:%M")
| rename GROUP_NUM as "Name" GROUP_STATUS as "Status" GROUP_COMPONENTS{}.TASK_NAME as Task GROUP_COMPONENTS{}.SUBTASK{} as "SubGroup"| eval tmp=mvzip(mvzip(Name,Task","),SubGroup,",")
| mvexpand tmp
| table "Date" "Status" tmp| eval Name=mvindex(split(tmp,","),0)
|eval Task=mvindex(split(tmp,","),1)|eval SubGroup=mvindex(split(tmp,","),2)
| table Name Date Status Task SubGroup

Re: make data to come in different rows using mvzip.

ITWhisperer — Tue, 15 Feb 2022 15:09:52 GMT

Your syntax as you have shown looks OK. The field names you have shown look OK too.

Re: How can you make data to come in different rows using mvzip?

somesoni2 — Tue, 15 Feb 2022 15:18:35 GMT

Can there be cases where your data looks like this? Is there a pattern between values of fields Task and SubGroup?

Name

Date

Status

Task

SubGroup

14-02-22

PASS

Re: How can you make data to come in different rows using mvzip?

anooshac — Tue, 15 Feb 2022 17:08:42 GMT

Name Date status Task SubGroup

A 14-02-22 PASS a a1

A 14-02-22 PASS a a2

A 14-02-22 PASS b b1

This is how the results are supposed to come.

This is the query which i am using. I am using JSON input. There is relation between Task and SubGroup.

index= "abc" sourcetype="xyz"

I am getting a error as "Error in 'eval' command: The expression is malformed. Expected )." Don't know what went wrong.

Re: How can you make data to come in different rows using mvzip?

PickleRick — Tue, 15 Feb 2022 17:08:40 GMT

And how would you decide which of those mvfield values correspond with which ones from another mvfield? I understand that you're creating it from json. Unfortunately, splunk isn't very good at manipulating complex data structures.

Re: make data to come in different rows using mvzip.

anooshac — Wed, 16 Feb 2022 04:18:19 GMT

Yes. This looks ok but I am getting a error as "Error in 'eval' command: The expression is malformed. Expected )." I don't know whether this error is related to mvzip.

Re: make data to come in different rows using mvzip.

yuanliu — Wed, 16 Feb 2022 06:42:44 GMT

There is a typo in the inner mvzip: missing a comma between Task and the joiner ",". The correct search would read

| eval "Date"=strftime(TASK_TIME/1000,"%F %H:%M") | rename GROUP_NUM as "Name" GROUP_STATUS as "Status" GROUP_COMPONENTS{}.TASK_NAME as Task GROUP_COMPONENTS{}.SUBTASK{} as "SubGroup" | eval tmp=mvzip(mvzip(Name,Task,","),SubGroup,",") ``` pay attention to this ``` | mvexpand tmp | table "Date" "Status" tmp | eval Name=mvindex(split(tmp,","),0) | eval Task=mvindex(split(tmp,","),1) | eval SubGroup=mvindex(split(tmp,","),2) | table Name Date Status Task SubGroup

This is how I diagnose the problem:

Spread every pipe (|) into its own line. (This is a good habit in everyday programming, too.)
Take away half the eval()s and observe.
If error exists, take away another half of remainder, and so on.
If the first half did not contain error, take away the first half, and split the second half. And so on.

Really what you are doing is to simply spot the syntax error (or formatting error in this case), not to worry about output.

Eventually I spotted this

| makeresults
| eval tmp=mvzip(mvzip(Name,Task","),SubGroup,",")
Error in 'eval' command: The expression is malformed. Expected ).

Re: make data to come in different rows using mvzip.

anooshac — Wed, 16 Feb 2022 09:42:35 GMT

Thank you.. i didn't notice the comma! and thanks for the info!

Now i am able to get the table but it has only one row. It should have multiple rows.

Re: make data to come in different rows using mvzip.

yuanliu — Wed, 16 Feb 2022 11:11:13 GMT

Now i am able to get the table but it has only one row. It should have multiple rows.

Apply the same diagnostic steps. Cut off everything below mvexpand, see if that is giving you multiple rows, i.e., whether that double mvzip (tmp) gives multivalue.

The reason why tmp only has one value is because Name is single value. Zip it to anything results in this value tied to the first value of the other field.

See the following simulation.

| makeresults | eval Name="A", Task=mvappend("a", "b"), Subgroup=mvappend("a1", "b1", "b2") ``` the above simulates original table ``` | eval tmp=mvzip(mvzip(Name, Task, ","), Subgroup, ",")

Name	Subgroup	Task	_time	tmp
A	a1 b1 b2	a b	2022-02-16 03:07:06	A,a,a1

Re: How can you make data to come in different rows using mvzip?

yuanliu — Wed, 16 Feb 2022 11:34:31 GMT

@anooshac wrote:
Name           Date                 status       Task                SubGroup
A                  14-02-22         PASS         a                         a1
A                  14-02-22         PASS         a                         a2
A                  14-02-22         PASS         b                         b1
This is how the results are supposed to come.

This is not a use case for mvzip; instead, just apply mvexpand and filter spurious rows using known relationship between Task and SubGroup.

| makeresults | eval Name="A", Task=mvappend("a", "b"), SubGroup=mvappend("a1", "b1", "b2"), Status="Pass" ``` the above simulates original table ``` | mvexpand SubGroup | mvexpand Task | where match(SubGroup, Task) ``` THIS IS APPLICATION SPECIFIC KNOWLEDGE. Use your real relationship ``` | table Name _time Status Task SubGroup ``` use _time for simulation ```

Name	_time	Status	Task	SubGroup
A	2022-02-16 03:29:50	Pass	a	a1
A	2022-02-16 03:29:50	Pass	b	b1
A	2022-02-16 03:29:50	Pass	b	b2

Re: make data to come in different rows using mvzip.

ITWhisperer — Wed, 16 Feb 2022 11:37:37 GMT

My guess would be that the events you have are structured something like this

{"GROUP_NUM":"123", "GROUP_STATUS":"OK", "GROUP_COMPONENTS":[ { "TASK_NAME":"t1", "SUBTASK":[ "a1","a2"] }, { "TASK_NAME":"t2", "SUBTASK":[ "b1","b2"] } ] }

You may want to consider extracting the GROUP_COMPONENTS collection out and using mvexpand on that before extracting the TASK_NAME and SUBTASK collection

| makeresults | eval _raw="{\"GROUP_NUM\":\"123\", \"GROUP_STATUS\":\"OK\", \"GROUP_COMPONENTS\":[{ \"TASK_NAME\":\"t1\", \"SUBTASK\":[\"a1\",\"a2\"] }, { \"TASK_NAME\":\"t2\", \"SUBTASK\":[\"b1\",\"b2\"] }] }" | spath GROUP_NUM | spath GROUP_STATUS | spath GROUP_COMPONENTS{} output=Components | mvexpand Components | spath input=Components