topic Re: stats query in Splunk Search

stats query

PaulaCom — Mon, 11 Nov 2024 11:38:23 GMT

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

Re: stats query

ITWhisperer — Mon, 11 Nov 2024 12:32:29 GMT

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

Re: stats query

PaulaCom — Mon, 11 Nov 2024 12:35:44 GMT

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

Re: stats query

ITWhisperer — Mon, 11 Nov 2024 12:47:39 GMT

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!

Re: stats query

PaulaCom — Mon, 11 Nov 2024 12:50:03 GMT

thank you for explaining 🙂 much appreciated