Splunk Search

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

anooshac
Communicator

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

                                                                                              b2

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,

...............| eval tmp=mvzip(mvzip(Name,Task,","),SubGroup,",")

| mvexpand tmp
| table  Name   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

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.

Labels (3)
0 Karma

somesoni2
Revered Legend

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

 

NameDateStatus TaskSubGroup
A14-02-22PASS

a

b

a1

a2

b1

 

0 Karma

anooshac
Communicator

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"

|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

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

@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_timeStatusTaskSubGroup
A2022-02-16 03:29:50Passaa1
A2022-02-16 03:29:50Passbb1
A2022-02-16 03:29:50Passbb2
0 Karma

PickleRick
Ultra Champion

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

...
| Name  Date     Status Group Task   SubGroup

Is that last line missing a command like table?

| table Name  Date     Status Group Task   SubGroup
0 Karma

anooshac
Communicator

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

0 Karma

PickleRick
Ultra Champion

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.

0 Karma

anooshac
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

anooshac
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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:

  1. Spread every pipe (|) into its own line. (This is a good habit in everyday programming, too.)
  2. Take away half the eval()s and observe.
  3. If error exists, take away another half of remainder, and so on.
  4. 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 ).
0 Karma

anooshac
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
_timetmp
A
a1
b1
b2
a
b
2022-02-16 03:07:06A,a,a1
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...