stats query

[PaulaCom]

Morning All 

appreciate some guidance on a spl i'm working on and just cant get the information i require

my dataset is tickets on our helpdesk . Im looking for the total number of ticket each team has for each different request type. 

team is called techGroupLevel

request type is call problem_detail

here's my search so far and it's just note right. 

| table _time id displayClient location_Name problem_detail detail bookmarkableLink status priority techGroupId techGroupLevel tech_Name reportDateUtc lastUpdated closeDate
| stats values(problem_detail) as problem_detail count(problem_detail) as total by techGroupLevel

under the i'm getting the following 

you can see that the figure returned on total is the combined total for all problem_details for each team 
i'd prefer to see a separate figure for each problem detail and then perhaps a total sum under each team but dont know how to go about this 

for example

techGroupLevel                                        problem_detail         Sub-Total                   Total 

Systems & Network                                 Email                               10                                     20

                                                                          Server                               5

                                                                         Shared Drive                   5

appreciate some guidance 

thanks 

Paula 

[PaulaCom]

thanks 
i used the first line and that looks better
the second line just repeats the total on each line like this 🙂 

[ITWhisperer · Accepted solution]

That's as I described it. There are ways to remove this extra information but they are a little involved and may not give you what you need. One of the more simpler ways is to do this

| stats count by techGroupLevel problem_detail
| eventstats sum(count) as total by techGroupLevel
| stats list(problem_detail) as problem_detail list(count) as count values(total) as total by techGroupLevel

Note that problem_detail and count are now multivalue fields and you have to visually align the count with the problem detail rather than them being in separate events and therefore in alternating background colours. Btw, total is also technically a multivalue field but since there is only one value per techGroupLevel, this isn't immediately obvious!

[PaulaCom]

thank you for explaining 🙂 much appreciated 

[ITWhisperer]

It is not so easy to get exactly the layout that you want as each resulting stats event contains a repeat of the information at the techGroupLevel level

| stats count by techGroupLevel problem_detail
| eventstats sum(count) as total by techGroupLevel