Expand the multivalue fields

[RSS_STT]

Trying to expand the multivalue field with one to one mapping as shown in image.

mvexpand create multiple row with all column matching value.

Actual data with multivalue.

child	child_Name	dv_class	n_name	direction	name	parent
55555

[PickleRick]

As @gcusello already noticed, you probably used a lot of values() in your stats.

As you can see yourself, it results with a list of multivalued fields. The problem with that is that each of those multivalued fields is a separate entity and there is no relationship between them. So you can reliably split those values into single rows unless you have a very very strong guarantee for some properties of your data.

For example, for source data like

field1	field2	field3
1	2
1		3
1	4	5
1	6
1	8
1		9
1		11

 You'd get a result like this:

values(field1)	values(field2)	values(field3)
1	2 4 6 8	3 5 9 11

If you tried "unpacking" it assuming that the values from the two latter colums match 1-1 you'd get completely ridiculous results.

[ITWhisperer]

It is not clear how you arrived at your current state, and it might be easier to solve with some sight of your current search and events (as @gcusello  has indicated), however, assuming you still want to go forward from where you seem to be, you could try something like this:

| eval row=mvrange(0,mvcount(child_Name))
| mvexpand row
| foreach child_Name direction dv_u_parent_class parent
    [| eval <<FIELD>>=mvindex(<<FIELD>>,row)]
| fields - _row

[RSS_STT]

it's possible to write partial data in to lookup file on base on some condition.

Like...

if dv_u_parent_class = ci_appld than outputlookup append=true abc.csv

|where dv_u_parent_class != ci_appld

and run time query should show rest two events.

child	child_Name	dv_u_parent_class	fqdn_name	direction	name	parent
55555	xyz PROD	ci_appld	xyz.srv.com	R toY	xyz	111111
55555	abc PROD	ci_appld	xyz.srv.com	R to Y	xyz	222222
55555	zzzz-FSE2	ci_netcom	xyz.srv.com	Y to R	xyz	333333
55555	abc.srv.com	ci_esx_app	xyz.srv.com	Y to R	xyz	444444

[gcusello]

Hi @RSS_STT ,

as I said, you can use my search and after use some conditions to filter results.

Ciao.

Giuseppe

[RSS_STT]

Im able to get that result but not getting logic to write filter data into lookup and later drop the event which written to lookup.

[ITWhisperer]

Logic that you could use could go something like this

<your search>
| appendpipe
  [| where <conditions for events you want to output>
   | outputlookup <your csv>
   | where false()
   ``` This removes all the events so that they are not appended to your main event pipeline ```
  ]
| where <conditions for events you want to keep i.e. not the events you wrote to the csv>

[gcusello]

Hi @RSS_STT,

I suppose that your search is something like this:

<your-search>
| stats 
   values(child_Name) AS child_Name
   values(dv_u_parent_class) AS dv_u_parent_class
   values(fqdn_name) AS fqdn_name
   values(direction) AS direction
   values(name) AS name
   values(parent) AS parent
   BY child	

You shoud try something like this:

<your-search>
| stats 
   values(fqdn_name) AS fqdn_name
   values(name) AS name
   BY child child_Name dv_u_parent_class direction parent

I could be more detailed if you can share your search.

Ciao.

Giuseppe

[RSS_STT]

Yes, I was trying the first query shared by you previously but second query shared by you also creating 96 record where i'm expecting only 4 record.

[gcusello]

Hi @RSS_STT,

probably there are more combinations of your values, not only 4.

You have two solutions: use less fields as keys in the stats command, but you'll have some filed with multivalue.

Otherwise you should identify some rules to filter your results.

Anyway, the only way to have only one value in a stats command is to put it in the BY clause.

There's also another solution, but in this way you loose some results: instead values, you could use the first option, taking only one value for each: I don't know (but I don't think) that this is acceptable for you!

Ciao.

Giuseppe