topic Re: Split Group by Values in Splunk Search

How to Split Group by Values?

user33 — Tue, 08 Nov 2022 15:35:07 GMT

Hello, I am very new to Splunk. I am wondering how to split these two values into separate rows. The "API_Name" values are grouped but I need them separated by date. Any assistance is appreciated!

SPL:

index=... | fields source, timestamp, a_timestamp, transaction_id, a_session_id, a_api_name, api_name, API_ID | convert timeformat="%Y-%m-%d" ctime(_time) AS date | eval sessionID=coalesce(a_session_id, transaction_id) | stats values(date) as date dc(source) as cnt values(timestamp) as start_time values(a_timestamp) as end_time values(api_name) as API_Name by sessionID | where cnt>1 | eval start=strptime(start_time, "%F %T.%Q") | eval end=strptime(end_time, "%FT%T.%Q") | eval duration(ms)=abs((end-start)*1000) | stats count, perc95(duration(ms)) as 95thPercentileRespTime(ms) values(API_Name) as API_Name by date

Re: Split Group by Values

yuanliu — Sun, 06 Nov 2022 19:49:20 GMT

Your screenshot shows two values under "date", meaning that your last stats command is working to separate values by date. Can you explain why this is not meeting your requirement? In other words, what exact output are you expecting?

Re: Split Group by Values

user33 — Sun, 06 Nov 2022 20:16:44 GMT

Hello, I am looking to separate by date and API_Name. I am looking for something like the below. I need a count and 95thPercentileRespTime(ms) specifically for accountstatements-v1 and a count and 95thPercentileRespTime(ms) specifically for Realtime_Image_Access_Service_V2 organized by date.

date	count	95thPercentileRespTime(ms)	API_Name
2022-11-05	x	x	accountstatements-v1
2022-11-06	x	x	Realtime_Image_Access_Service_V2
2022-11-06	x	x	accountstatements-v1

Re: Split Group by Values

yuanliu — Sun, 06 Nov 2022 21:21:51 GMT

To group by date and api_name, you put both in groupby clause, right?

Does this give you what you expect?

Re: Split Group by Values

user33 — Sun, 06 Nov 2022 21:26:07 GMT

It does not unfortunately. I get errors:

Re: Split Group by Values

user33 — Sun, 06 Nov 2022 21:28:01 GMT

I get an error even if I use a comma between: date, API_Name.

Re: Split Group by Values

yuanliu — Sun, 06 Nov 2022 22:08:06 GMT

You didn't try the command in my post; it doesn't contain values(API_Name). (Whether you use comma or not is immaterial.)

Re: Split Group by Values

user33 — Sun, 06 Nov 2022 22:48:43 GMT

Apologies! You are correct. I had a typo. This works perfectly! Thank you very much yuanliu!!