Consolidate events

[raysonjoberts]

I have a lookup table I am using to pull in contact information based on correlation of a couple of fields. The way the lookup table is formatted, it makes my results look different than what I want to see. If I can consolidate the lookup table, it will fix my issue, but I can't figure out how to do it. The table currently looks like this:

Org	Branch	Role	Name
Org A	Branch 1	President	Jack
Org A	Branch 1	VP	Jill
Org A	Branch 1	Manager	Mary
Org A	Branch 2	President	Hansel
Org A	Branch 2	VP	Gretel
Org A	Branch 3	VP	Mickey
Org A	Branch 3	Manager	Minnie

I use the Org and Branch as matching criteria and want to pull out the names for each role.  I do not want to see multivalue fields when I am done, the current search looks like:

[base search]
| lookup orgchart Org Branch OUTPUTNEW Role
| mvexpand Role
| lookup orgchart Org Branch Role OUTPUTNEW Name

This works, but the mvexpand (obviously) creates a new line for each role and I do not want multiple lines for each in my final results.  I want a single line for every Org/Branch pair showing all the Roles and names.

 I am thinking the way of solving this is reformatting the lookup table to look like the table below, then modifying my lookup.  Is there a way to "transpose" just the 2 fields? 

[base search]
| lookup orgchart Org Branch OUTPUTNEW President, VP, Manager

Org	Branch	President	VP	Manager
Org A	Branch 1	Jack	Jill	Mary
Org A	Branch 2	Hansel	Gretel
Org A	Branch 3		MIckey	Minnie

Thank you!

[PickleRick]

It seems you're trying to do xyseries - transform a series of values into a x-y chart.

The problem with this is that you can only have one field on each axis and you want two fields on one of them.

But fear not, you can always do a trick of "combine and then split".

<your_search>
| eval orgbranch=Org.":".Branch
| xyseries orgbranch Role Name
| eval Org=mvindex(split(orgbranch,":"),0)
| eval Branch=mvindex(split(orgbranch,":"),1)
| fields - orgbranch

[dtburrows3]

Splitting your lookup to include new fields "President", "VP", "Manager" would work but doesn't really scale if the role field has high cardinality.

Here is another approach that is more scalable and can be generalized.


You could make a net-new field in your lookup named role_json that would contain the mapping info of role<-->name. 

Edit: Just realized your request was to not have results in the mvexpanded format I first showed. So here is an updated method.

<base_search>
    | lookup orgchart Org, Branch OUTPUT role_json
    | foreach mode=multivalue role_json
        [
            | eval
                tmp_json=if(
                    isnull(tmp_json),
                        json_object(spath('<<ITEM>>', "Role"), spath('<<ITEM>>', "Name")),
                        json_set(tmp_json, spath('<<ITEM>>', "Role"), spath('<<ITEM>>', "Name"))
                    )
            ]
    ``` capture any role_json values that are single values ```
    | eval
        tmp_json=if(
            mvcount(role_json)==1,
                json_object(spath(role_json, "Role"), spath(role_json, "Name")),
                'tmp_json'
            )
    ``` remove role_json (no longer needed) ```
    | fields - role_json
    ``` parse out tmp_json to table all the proper mappings ```
    | spath input=tmp_json
    ``` remove tmp_json (no longer needed) ```
    | fields - tmp_json

 Directly after the lookup results would look something like this:

Then after the foreach loops.

I added a few extra entries to lookup to demonstrate that this method is dynamic and doesn't need any hardcoded fieldnames to account for potential new values.

To get a new json object field into your existing lookup would look something like this (Provided its a CSV, if it is a kvstore then you would probably need to update the definition to include the new field)

| inputlookup orgchart
    | tojson str(Role) str(Name) output_field=role_json
    | outputlookup orgchart

[gcusello]

Hi @raysonjoberts,

using a lookup as the one you described, please try something like this:

| inputlookup <your_lookup>
| stats 
   values(eval(if(Role="President",Name,""))) AS President 
   values(eval(if(Role="VP",Name,""))) AS VP 
   values(eval(if(Role="Manager",Name,""))) AS Manager 
   BY Org Branch

Ciao.

Giuseppe