Is there a way to group the data by time and another field?

[kuul13]

I was able to write a query that group by api (msgsource) to show the response times, but I am trying to see if I can extract the result in a different format. Here is the query I used:

query
| rex field=_raw "Time=(?<NewTime>\d{4}\.\d+)" 
| eval TimeMilliseconds=(NewTime*1000)
| timechart span=1d count as total,  
count(eval(TimeMilliseconds<=1000)) as "<1sec",
count(eval(TimeMilliseconds>1000 AND TimeMilliseconds<=2000)) as "1sec-2sec"
count(eval(TimeMilliseconds>2000 AND TimeMilliseconds<=5000)) as "2sec-5sec"
count(eval(TimeMilliseconds>48000 )) as "48sec+", by msgsource

Here is the output that I get today:

_time	total: retrieveApi	total: createApi	<1sec: retireveApi	<1sec: createApi	1sec-2sec: retrieveApi	1sec-2sec: createApi	2sec-5sec: retrieveApi	2sec-5sec: createApi
25-07-13	1234	200	1200	198	34	1	0	1
2025-07-14	1000	335	990	330	8	5	2	0

This is what I would like to see, the results grouped by `_time` and `msgsource` both.

_time	msgsource	total	<1sec	1sec-2sec	2sec-5sec
2025-07-13	retrieveApi	1234	1200	34	0
2025-07-13	createApi	200	198	1	1
2025-07-14	retrieveApi	1000	990	8	2
2025-07-14	createApi	335	330	5	0

[PickleRick]

As often, there is more than one way to do things in SPL 🙂

query
| rex field=_raw "Time=(?<NewTime>\d{4}\.\d+)" 

This one is pretty OK. 😉

I'd _not_ go into multiplying it at the moment. You can multiply the aggregate at the end. The performance difference is minuscule but let's optimize it anyway.

| eval timeperiod=case(Time<1,"<1",Time<2,"1<2",Time<5,"<5",Time<48,"5<48",1=1,">48)

This way you have a "classifier" field by which you can do your count

Now instead of timechart you can do

| bin _time span=1d
| stats count by _time timeperiod msgsource

So now we can "pack"

| eval timesource=_time . "|" . msgsource

and table

| xyseries timesource timeperiod count

and split

| eval _time=mvindex(split(timesource,"|"),0),msgsource=mvindex(split(timesource,"|"),1)
| fields - timesource

Warning: I'm writing this "on paper" because I don't have my Splunk instance at hand so it might contain some small syntax mistakes. The idea is there however.

[kuul13]

Sorry I didn't mention before that I know very basic of Splunk, so I couldn't follow everything you said. Here is the query that I tried and the result:

| rex field=_raw "Time=(?<NewTime>\d{4}\.\d+)" 
| eval TimeMilliseconds=(NewTime*1000)
| eval timeperiod=case(TimeMilliseconds<1,"<1",TimeMilliseconds<2,"1<2",TimeMilliseconds<5,"<5",TimeMilliseconds<48,"5<48",1=1,">48")
| bin _time span=1d
| stats count by _time timeperiod msgsource
| eval timesource=_time . "|" . msgsource
| xyseries timesource timeperiod count
| eval _time=mvindex(split(timesource,"|"),0),msgsource=mvindex(split(timesource,"|"),1)
| fields - timesource

output looks like this which is not what is expected.

5<48	>48	_time	msgsource
1	439	2025-07-20	createAPI
1	1943	2025-07-20	RetrieveAPI

I was expecting an output something

_time

msgsource

total

<1s

1-2s

2-5s

>5s

[PickleRick]

Right. If you don't have the values, you can use

| fillnull '<1' '1<2' '2<5' '5<48'  '>48'
| addtotals col=f row=t

Alternatively you can fiddle with @ITWhisperer 's approach - use timechart to get the data with already filled with zeros and then retransform its results.

The order of the fields you can change by adding fields command at the end.

[ITWhisperer]

Try something like this

| untable _time msgsource count
| eval group=mvindex(split(msgsource,": "),0)
| eval msgsource=mvindex(split(msgsource,": "),1)
| eval _time=_time.":".msgsource
| xyseries _time group count
| eval msgsource=mvindex(split(_time,":"),1)
| eval _time=mvindex(split(_time,":"),0)
| table _time msgsource total *

[kuul13]

Sorry, I know very basics of Splunk. I don't think I was able formulate the query you suggested as it return no output. Here is the query I ran:

my query
| rex field=_raw "Time=(?<NewTime>\d{4}\.\d+)" 
| eval TimeMilliseconds=(NewTime*1000)
| eval timeperiod=case(TimeMilliseconds<1,"<1s",TimeMilliseconds>=1 AND TimeMilliseconds<2,"1-2s",TimeMilliseconds>=2 AND TimeMilliseconds<5,"2-5s",1=1,">5s")
| untable _time msgsource count
| eval group=mvindex(split(msgsource,": "),0)
| eval msgsource=mvindex(split(msgsource,": "),1)
| eval _time=_time.":".msgsource
| xyseries _time group count
| eval msgsource=mvindex(split(_time,":"),1)
| eval _time=mvindex(split(_time,":"),0)
| table _time msgsource total *

[ITWhisperer · Accepted solution]

You still need the timechart from your original search

my query
| rex field=_raw "Time=(?<NewTime>\d{4}\.\d+)" 
| eval TimeMilliseconds=(NewTime*1000)
| timechart span=1d count as total,  
count(eval(TimeMilliseconds<=1000)) as "<1sec",
count(eval(TimeMilliseconds>1000 AND TimeMilliseconds<=2000)) as "1sec-2sec"
count(eval(TimeMilliseconds>2000 AND TimeMilliseconds<=5000)) as "2sec-5sec"
count(eval(TimeMilliseconds>48000 )) as "48sec+", by msgsource
| untable _time msgsource count
| eval group=mvindex(split(msgsource,": "),0)
| eval msgsource=mvindex(split(msgsource,": "),1)
| eval _time=_time.":".msgsource
| xyseries _time group count
| eval msgsource=mvindex(split(_time,":"),1)
| eval _time=mvindex(split(_time,":"),0)
| table _time msgsource total *