Support on SPL Query

[dinesh001kumar]

I need to display the Success percentage for each service day wise.

I am doing stats and then table getting output as below.

Service Name	Date	Success(%)
Service 1	29-12-2025	100
Service 1	30-12-2025	99
Service 1	31-12-2025	98
Service 1	01-01-2026	95
Service 1	02-01-2026	94
Service 2	29-12-2025	92
Service 2	30-12-2025	100
Service 2	31-12-2025	89
Service 2	01-01-2026	98
Service 2	02-01-2026	95
Service 3	29-12-2025	94
Service 3	30-12-2025	92
Service 3	31-12-2025	100
Service 3	01-01-2026	89
Service 3	02-01-2026	99

But i wanted output as below:

Excepted Output:

Service Name	29-12-2025	30-12-2025	31-12-2025	01-01-2026	02-01-2026
Service 1	100	99	98	95	94
Service 2	92	100	89	98	95
Service 3	94	92	100	89	99

But when i use xyseries command getting result as below which getting 1st in 1st column and last month data getting at the last.

Service Name	01-01-2026	02-01-2026	29-12-2025	30-12-2025	31-12-2025
Service 1	95	94	100	99	98
Service 2	98	95	92	100	89
Service 3	89	99	94	92	100

If I use transpose command getting below format.

29-12-2025	30-12-2025	31-12-2025	01-01-2026	02-01-2026
Service 1	Service 1	Service 1	Service 1	Service 1
100	99	98	95	94

so on..

Please help on this request.

[gcusello]

Hi @dinesh001kumar ,

if it's acceptable for you, the easiest solution is to use a different date format: yyyy-mm-dd instead of dd-mm-yyyy, because in this way dates are automatically sorted.

If it isn't possible you should pass throgh this format to sort the results and then display the output in the requested format.

Could you share your SPL search?

Supponing that Your SPL is something like this, you could try something like the following:

<your_search>
| eval date=strftime(strptime(your_date,"%d-%m-%Y"),"Y-%m-%d")
| chart max(success) AS success OVER Service_Name BY date

Ciao.

Giuseppe

[dinesh001kumar]

Hi @gcusello ,

Thanks for quick response below is my SPL query, the concern her was they wanted to see the date format in "%d-%b-%Y" not in "%Y-%b-%d".

index="main"
| eval txstatus=case(status=200,"Success",status >200 AND status <500,"Business_Fail",status >=500,"System_Fail")
| eval success_pass_count = Success + Business_Fail
| eval svc_nm=case(match(api_name,"login"),"Login",match(api_name,"Prepaid"),"Prepaid Registration",match(api_name,"Postpaid"),"Postpaid Registration",true(),"unKnown")
| bin span=1d _time
| stats count as total sum(success_pass_count) as Success_pass_count
by svc_nm,_time
| eval Success=round((Success_pass_count/total) * 100,2)
| eval Date = strftime(_time, "%Y-%b-%d")
| eval metric="Response", value=Success, col=Date
| xyseries svc_nm,col value
| foreach "*-*-*" [ eval <<FIELD>> = if(isnull('<<FIELD>>'), "- ", round('<<FIELD>>',2)) ]
|rename svc_nm AS "Service Name"

[gcusello]

Hi @dinesh001kumar ,

my hint is to guide your customer to the easiste solution, anyway, please try this following my approach:

index="main"
| eval txstatus=case(status=200,"Success",status >200 AND status <500,"Business_Fail",status >=500,"System_Fail")
| eval success_pass_count = Success + Business_Fail
| eval svc_nm=case(match(api_name,"login"),"Login",match(api_name,"Prepaid"),"Prepaid Registration",match(api_name,"Postpaid"),"Postpaid Registration",true(),"unKnown")
| bin span=1d _time
| stats count as total sum(success_pass_count) as Success_pass_count
by svc_nm,_time
| eval Success=round((Success_pass_count/total) * 100,2)
| eval Date = strftime(_time, "%Y-%b-%d")
| chart max(Success) AS Success OVER Date BY svc_nm
| eval Date=substr(Date,9,2)."-".substr(Date,6,2)."-".substr(Date,1,4)
| transpose header_field=Date
|rename svc_nm AS "Service Name"

 Ciao.

Giuseppe

[ITWhisperer]

Try something like this

index="main"
| eval txstatus=case(status=200,"Success",status >200 AND status <500,"Business_Fail",status >=500,"System_Fail")
| eval success_pass_count = Success + Business_Fail
| eval svc_nm=case(match(api_name,"login"),"Login",match(api_name,"Prepaid"),"Prepaid Registration",match(api_name,"Postpaid"),"Postpaid Registration",true(),"unKnown")
| bin span=1d _time
| stats count as total sum(success_pass_count) as Success_pass_count
by svc_nm,_time
| eval Success=round((Success_pass_count/total) * 100,2)
| xyseries svc_nm _time Success
| transpose 0 header_field=svc_nm column_name=date
| eval date=strftime(date,"%d-%b-%Y")
| transpose 0 header_field=date column_name="Service Name"