topic Re: Group search results by result-values/-wildcards in Splunk Search

Group search results by result-values/-wildcards

hse8fe — Tue, 29 Sep 2020 18:43:17 GMT

Hello Splunk Community,

I have an selected field available called OBJECT_TYPE which could contain several values.
For example the values a_1, a_2, a_3, b_1, b_2, c_1, c_2, c_3, c_4
Now I want to get a grouped count result by a*, b*, c*. Which could be visualized in a pie chart.

How I can achieve this?
Means a result table like

Type | Count | %

a | 300 | 30

b | 200 | 20
c | 500 | 50

Thanks a lot for you support!
Sebastian

Re: Group search results by result-values/-wildcards

tiagofbmm — Wed, 28 Mar 2018 11:59:45 GMT

Hey

You can use rex to extract the common value from your values:

Yoursearch | rex field=OBJECT_TYPES "(?<Common>[^\_]*)"

Then do stats count by common

Re: Group search results by result-values/-wildcards

kmaron — Wed, 28 Mar 2018 12:02:41 GMT

I think the easiest way would be to create a new field that is just the piece you want of the object type.

| eval Object_Group=substr(OBJECT_TYPES,1,1)

Then you can do your transforming command using the Object_Group field

Re: Group search results by result-values/-wildcards

niketn — Wed, 28 Mar 2018 12:10:12 GMT

@hse8fe, please try the following search on the field OBJECT_TYPES

<yourBaseSearch>
| stats count by OBJECT_TYPES
| eval Type= replace(OBJECT_TYPES,"([^_]+)\_.+","\1")
| stats sum(count) as Count by Type
| eventstats sum(Count) as Total
| eval "%"=round((Count/Total)*100,1)
| fields - Total

Re: Group search results by result-values/-wildcards

hse8fe — Tue, 29 Sep 2020 18:43:22 GMT

Thanks a lot for your super fast reaction 🙂

Anyway I don't see the solution....
I explain again:
The field OBJECT_TYPE is already available in the selected fields. The value names were only examples.

Realistic values of the field OBJECT_TYPE:
WWV_DELVRY, WWV_DELFOR --> should be grouped to WWV
O2int, int, intAS2 --> should be grouped to int
IFTSTA, IFCSUM --> should be grouped to TMS

Result table should look like

Type | Count | %

WWV | 300 | 30
int | 200 | 20
TMS | 500 | 50

Thanks again for your support!

Re: Group search results by result-values/-wildcards

hse8fe — Wed, 28 Mar 2018 12:19:49 GMT

Thanks for this proposal... please notice my answer above, the value names were only examples. I need to freely define group names and fill these grouped values by wildcard or OR search clauses.

Re: Group search results by result-values/-wildcards

hse8fe — Wed, 28 Mar 2018 12:34:32 GMT

Thanks for this proposal... please notice my answer above, the values were only examples. I need to freely define group names and fill these grouped values by wildcard or OR search clauses.

Re: Group search results by result-values/-wildcards

vinod94 — Wed, 28 Mar 2018 13:18:48 GMT

You can try this,

| makeresults | eval test="a_1, a_2, a_3, b_1, b_2, c_1, c_2, c_3, c_4" | makemv delim="," test | mvexpand test | eval type=if(like(test, "%a%"), "a", if(like(test, "%b%"), "b", "c")) | stats count by type

Re: Group search results by result-values/-wildcards

hse8fe — Wed, 28 Mar 2018 13:54:11 GMT

Hello All,

thank for all of your inputs. It helped me a lot. Anyway the simple solution is to use the magic "transpose" command which is converting the result colums of a single row to multiple rows:

stats 
count(eval(like(OBJECT_TYPE,"WWV%"))) AS WWV 
count(eval(like(OBJECT_TYPE,"IFT%") OR like(OBJECT_TYPE,"IFC%") OR like(OBJECT_TYPE,"XML_INVOIC%") OR like(OBJECT_TYPE,"UTILMD%") OR like(OBJECT_TYPE,"XML_EPCIS"))) AS TMS 
count(eval(like(OBJECT_TYPE,"%VMI"))) AS VMI
count(eval(like(OBJECT_TYPE,"INVRPTE%")OR like(OBJECT_TYPE,"DELJIT_SUPO_EMS") OR like(OBJECT_TYPE,"APERAK"))) AS EMS
count(eval(like(OBJECT_TYPE,"DELFOR") OR like(OBJECT_TYPE,"DESADV") OR like(OBJECT_TYPE,"vda%") OR like(OBJECT_TYPE,"X12%") OR like(OBJECT_TYPE,"ORD%") OR like(OBJECT_TYPE,"INVRPT") OR like(OBJECT_TYPE,"edl") OR like(OBJECT_TYPE,"DELJIT"))) AS Procurment 
| transpose

See as well:

Re: Group search results by result-values/-wildcards

richgalloway — Wed, 28 Mar 2018 14:27:39 GMT

@hse8fe If your problem is resolved, please accept an answer to help future readers.

Re: Group search results by result-values/-wildcards

hse8fe — Wed, 28 Mar 2018 14:51:54 GMT

Thanks to all your inputs, this helped a lot!!!
But the magic command which is solving my issue is Transpose
"Use the transpose command to convert the columns of the single row into multiple rows. "