topic Re: How to de-aggregate the STATS result ? in Splunk Search

How to de-aggregate the STATS result ?

zacksoft_wf — Wed, 08 Dec 2021 07:29:47 GMT

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.

Re: How to de-aggregate the STATS result ?

isoutamo — Wed, 08 Dec 2021 09:13:24 GMT

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

r. Ismo

Re: How to de-aggregate the STATS result ?

ITWhisperer — Wed, 08 Dec 2021 12:03:04 GMT

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

Re: How to de-aggregate the STATS result ?

zacksoft_wf — Thu, 09 Dec 2021 06:14:47 GMT

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.

Re: How to de-aggregate the STATS result ?

ITWhisperer — Thu, 09 Dec 2021 09:34:41 GMT

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