How to de-aggregate the STATS result ?

[zacksoft_wf]

I have this query where I need to use stats to aggregate the results based on account_number.  
Now, some of the results are multivalued. I need the output to be like a table, one values for each row.  like somehow expand the aggregated results into a tabular  format.

=========================================
`index_list` account_type="Service Account"
| addinfo
| eventstats dc(sourcetype) as dc_sourcetype by service_number
| where dc_sourcetype>1
| stats values(is_interactive) as is_interactive,
values(account_name) as account_name,
values(full_name) as full_name,
values(email_address) as email_address,
values(manager_name) as manager_name,
values(service_account_name) as service_account_name,
values(account_type) as account_type,
values(service_account_id) as service_account_id,
values(au_owner_name) as au_owner_name,
values(au_owner_email) as au_owner_email
BY account_number

=======================================

account_type, service_account_id, account_number are multivalue fields.

[ITWhisperer · Accepted solution]

mvexpand will only do one field at a time, if you want to expand multiple multivalue field, I wrote a post about how this might be done back in April https://community.splunk.com/t5/Splunk-Search/mvexpand-limits/m-p/549178 

To be honest, you might be better off modifying the stats command to get what you want in the first place. For example, if account_type, service_account_id, account_number all have values you could do something like this:

`index_list` account_type="Service Account"
| addinfo
| eventstats dc(sourcetype) as dc_sourcetype by service_number
| where dc_sourcetype>1
| stats values(is_interactive) as is_interactive,
values(account_name) as account_name,
values(full_name) as full_name,
values(email_address) as email_address,
values(manager_name) as manager_name,
values(service_account_name) as service_account_name,
values(au_owner_name) as au_owner_name,
values(au_owner_email) as au_owner_email
BY account_type, service_account_id, account_number

[zacksoft_wf]

Thank you @ITWhisperer . This greatly helps.  There is just one little issue.
Field like "service_account_id"  also present in events as "src_account_id". They are essentially the same field with different name. And I am thinking  to use coalesce function like,
eval service_account_id = coalesce(service_account_id,src_account_id)

The question that comes to my mind is , this field is also used in stats " BY" clause, now should the coalesce function be used before or after | stats ..BY ...  ?
Not just 'service_Account_id' , the other fields like account_type also has to be used in conjuntion with coalesce as, it has another field with slightly different name and i have to fetch the one that has value and is used in BY clause.

[ITWhisperer]

I would do the coalescing before the stats command - if a field from the by clause is null, the event doesn't get included in the stats

[isoutamo]

Hi

You are looking this https://docs.splunk.com/Documentation/Splunk/8.2.3/SearchReference/Mvexpand

r. Ismo