Unable to get the statistics in one line

[SG]

HI, I wanted to see the results for each service in one line. But I see each hour in a different line as per the below screenshot. Can you please let me know what changes need to be done to get the results in one line even though we select multiple hrs in the time while doing the search?

My Search query - 

index=*****
| stats list(service_calls) as service_calls list(service_errors) as service_errors list(service_error_rate) as service_error_rate by service

Thanks,

SG 

[SG]

Apologies for not giving complete information about the question. Please find the same for below...

index=*** 
| stats count as service_calls, count(eval(status >= 400)) as service_errors by service
| eval service_error_rate = round((service_errors / service_calls) * 100,2)
| where service_error_rate > 0
| stats list(service_calls) as service_calls list(service_errors) as service_errors list(service_error_rate) as service_error_rate by service

The above query will give output as below if i search for 3 hrs. This data is taken from the original index where the logs are written to it directly from the server.

Now i am writing these logs to a summary index by using below query

index=*** 
| stats count as service_calls, count(eval(status >= 400)) as service_errors by service
| eval service_error_rate = round((service_errors / service_calls) * 100,2)
| where service_error_rate > 0
| sistats list(service_calls) as service_calls list(service_errors) as service_errors list(service_error_rate) as service_error_rate by service

After writing the logs into summary index if i am searching for the data with this query for same time period i am getting data in multiple lines not as per the original data.

Search query in my summary index 

index= *** report=****
| stats list(service_calls) as service_calls list(service_errors) as service_errors list(service_error_rate) as service_error_rate by service

Results for the above query in multiple lines

I scheduled report to run for every one hr, below are the details of schedule

With above data, can you please tell me what needs to be changed to get the data in single line like i get if i search for data in the original index.

Thanks,

SG

[ITWhisperer]

You are writing to the summary index every hour, one line per service

When you search the summary index, you are getting one result per our per service, so if your time period is now 3 hours (1am to 4am), you can expect to get upto 3 results per service.

if you just want to see the results from the summary index, don't use stats.

If you want to aggregate the results from the 3 hours, try summing them as I suggested. You might want to recalculate the error rate based on the sum of requests and sum of errors.

[ITWhisperer]

Do you mean this?

index=*****
| stats sum(service_calls) as service_calls sum(service_errors) as service_errors sum(service_error_rate) as service_error_rate by service

[gcusello]

Hi @SG,

sorry but I dont see the hours in your screenshot and search?

what are you meaning?

if you want to ungroup rows, you have to put the fields in list in the BY clause, something like this:

index=ghs_xapi_summary
| stats by service service_calls service_errors service_error_rate 

Ciao.

Giuseppe

[SG]

I don't want to separate the data based on hrs. Thats the reason i didn't mention the time chart.

If i select one hr data comes in single row, but if i select more than one hr data comes as below in multiple rows.

[gcusello]

Hi @SG,

don't use list as option in the stats command, but sum or last, you can see the possible options at https://docs.splunk.com/Documentation/Splunk/8.2.5/SearchReference/Stats#Stats_function_options

Ciao.

Giuseppe

[SG]

HI,

If i use sum option i am not getting any data for my search...

Below is my search schedule to write data to summary index - 

index=*** 
| eval endpoint=coalesce(Address_operation)
| eval endpoint_status = endpoint."-".status
| search NOT endpoint_status IN ("GetPointsDetails-404")
| convert num(status)
| sistats count as Total_service_calls, count(eval(status >= 400)) as service_errors by service

After writing the data to summary index i could see the logs in the summary index looks like below...

Let me know if i am doing something wrong the way writing data to summary index.

Thanks,

SG

[gcusello]

Hi @SG,

put table and collect commands at the end of your search

index=*** 
| eval endpoint=coalesce(Address_operation)
| eval endpoint_status = endpoint."-".status
| search NOT endpoint_status IN ("GetPointsDetails-404")
| convert num(status)
| sistats count as Total_service_calls, count(eval(status >= 400)) as service_errors by service
| table Total_service_calls service_errors service
| collect index=your_summary_index

Ciao.

Giuseppe