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!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...