Splunk Search
Highlighted

Group search results by result-values/-wildcards

Explorer

Hello Splunk Community,

I have an selected field available called OBJECTTYPE which could contain several values.
For example the values a
1, a2, a3, b1, b2, c1, c2, c3, c4
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
Highlighted

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

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
Highlighted

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

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 OBJECTTYPE:
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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

SplunkTrust
SplunkTrust

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

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