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.
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 |
A | 14-02-22 | PASS | a b | a1 a2 b1
|
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.
@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 |
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.
...
| Name Date Status Group Task SubGroup
Is that last line missing a command like table?
| table Name Date Status Group Task SubGroup
Sorry, It was by mistake while writing this question. The error comes in the mvzip part as expected ")".
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.
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.
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.
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.
Please can you share you full search (preferably in a code block </>)?
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
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:
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
Error in 'eval' command: The expression is malformed. Expected ).
| makeresults
| eval tmp=mvzip(mvzip(Name,Task","),SubGroup,",")
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.
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
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 |
Your syntax as you have shown looks OK. The field names you have shown look OK too.
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.