Splunk Search

Group search results by result-values/-wildcards

hse8fe
Explorer

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

0 Karma
1 Solution

hse8fe
Explorer

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:

View solution in original post

0 Karma

hse8fe
Explorer

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. "

0 Karma

hse8fe
Explorer

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:

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

vinod94
Contributor

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

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

hse8fe
Explorer

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.

0 Karma

kmaron
Motivator

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

0 Karma

hse8fe
Explorer

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.

0 Karma

tiagofbmm
Influencer

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

0 Karma

hse8fe
Explorer

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!

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...