How to achieve the transpose or xyseries

[dtccsundar]

Hi ,

I have  4 fields and those need to be in a tabular format .Out of which one field has the ratings which need to be converter to column to row format with count and rest 3 columns need to be same .

I have tried using transpose and xyseries but not able to achieve in both .

Ex : current table format

Name	Domain	Area	Rating
Nsp -1	IT	End user service	H
NSP-2	IT	Mainframe	M
NTS-10	G&A	ENT	L
NTL -05	EPP	Distributed	M
WMC-04	AES	corp	L

How this can be changed to the below format using splunk search ,

Expected table format :

Name	Domain	Area	Rating(H) count	Rating(M) count	Rating(L) count
Nsp -1	IT	End user service	1	0	0
NSP-2	IT	Mainframe	0	1	0
NTS-10	G&A	ENT	0	0	1
NTL -05	EPP	Distributed	0	1	0
WMC-04	AES	corp	0	0	0

Please let me know how to achieve this in using Splunk search.

[ITWhisperer]

<<your current search>>
| stats count(eval(Rating="H")) as RatingH, count(eval(Rating="M")) as RatingM, count(eval(Rating="L")) as RatingL by Name, Domain, Area
| table Name Domain Area RatingH RatingM RatingL
| rename RatingH as "Rating(H) count", RatingM as "Rating(M) count", RatingL as "Rating(L) count"

[richgalloway]

I think the stats command can do that.

<<your current search>>
| stats sum(eval(Rating="H")) as RatingH, sum(eval(Rating="M")) as RatingM, sum(eval(Rating="L")) as RatingL by Name, Domain, Area
| table Name Domain Area RatingH RatingM RatingL
| rename RatingH as "Rating(H) count", RatingM as "Rating(M) count", RatingL as "Rating(L) count"

[dtccsundar]

Hi richgalloway,

Thank you for your search.

When i ran this search , i am not getting the count for RatingH,RatingM,RatingL.All these 3 fields are blank.

Please let me know how to bring the values/count using this search .

[richgalloway]

Please share the full query.