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
a | 300 | 30
b | 200 | 20
c | 500 | 50
Thanks a lot for you support!
Sebastian
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:
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. "
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:
@hse8fe If your problem is resolved, please accept an answer to help future readers.
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
@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
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.
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
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.
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
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
WWV | 300 | 30
int | 200 | 20
TMS | 500 | 50
Thanks again for your support!