topic Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)? in Splunk Search

How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Sun, 05 Nov 2017 01:35:46 GMT

How to write a query which displays all the requests count for every hour in 24 hours access logs. The log timings are between [04/Nov/2017:00:00:00 -0400] and [04/Nov/2017:23:59:59 -0400]

sample access logs:
10.10.1xx.2xx - - 1000000 [04/Nov/2017:01:14:35 -0400] - /web/Ext HTTP/1.1 server.net TIME:0/909972 "GET /web/Ext HTTP/1.1" 200 1158

sample output:

Start Time End Time Total # of executions
04/Nov/2017:00:00:00 04/Nov/2017:00:59:59 150
04/Nov/2017:01:00:00 04/Nov/2017:01:59:59 120
. . .
. . .
04/Nov/2017:23:00:00 04/Nov/2017:23:59:59 110

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

kamlesh_vaghela — Tue, 29 Sep 2020 16:35:26 GMT

Can you please try below search ??

index=_internal earliest=11/04/2017:0:0:0 latest=11/04/2017:23:59:59 | stats count as "Total # of executions" min(_time) as start_time max(_time) as end_time by date_hour | sort date_hour | eval "Start Time"=strftime(start_time, "%d/%m/%Y %I:%M:%S %p") | eval "End Time"=strftime(end_time, "%d/%m/%Y %I:%M:%S %p") | table "Start Time" "End Time" "Total # of executions"

This search will display the count of current hour events. Start Time & End time is considered minimum and maximum time respectively of current hour events.

Thanks

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Mon, 06 Nov 2017 20:50:50 GMT

Thanks Kamlesh , It worked for me.

i also need response time and size in additional to above, but i need to split the value to get the response time.
From the logs Resp Time can be found from TIME:0/909972.
Resp Time= 909972 micro seconds.

I need Resp time in seconds.

Columns required:

Min Resp Time Avg Resp Time Max Resp Time Std Dev Of Resp Time 90th percentile Resp Time Min Size of Response Avg Size Max Size Std Dev 90th percentile

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Tue, 29 Sep 2020 16:38:23 GMT

I did tried the below query and worked for auv,min,max of size.

index="nam"earliest=09/05/2017:00:00:00 latest=09/05/2017:23:59:59| stats count AS "Total # of executions" min(_time) AS start_time max(_time) as end_time avg(field14) AS "AVGSIZE" min(field14) AS "MINSIZE" max(field14) AS "MAXSIZE" by date_hour| eval "Start Time"=strftime(start_time,"%d/%m/%Y %I:%M:%S:%p")|eval "End Time"=strftime(end_time,"%d/%m/%Y %I:%M:%S:%p")|table "Start Time" "End Time" "Total # of executions" "AVGSIZE" "MINSIZE" "MAXSIZE"

field14= size from the logs

similarly i need for avg, min,max,90 percentile, stddev for Resp Time.
Resp Time in logs are TIME:0/909972 , I need just the millsec value from it

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

kamlesh_vaghela — Tue, 07 Nov 2017 06:55:15 GMT

HI
Can you please try this?

index="nam" earliest=09/05/2017:00:00:00 latest=09/05/2017:23:59:59 | rex field="Resp Time" "TIME:0\/(?<RESP_TIME>.*)" | stats count AS "Total # of executions" min(_time) AS start_time max(_time) as end_time min(RESP_TIME) AS "Min Resp Time" avg(RESP_TIME) AS "Avg Resp Time" max(RESP_TIME) AS "Max Resp Time" stdev(RESP_TIME) AS "Std Dev Of Resp Time" perc90(RESP_TIME) AS "90th percentile Resp Time" min(field14) AS "Min Size of Response" avg(field14) AS "Avg Size of Response" max(field14) AS "Max Size of Response" stdev(field14) AS "Std Dev of Response" perc90(field14) AS "90th percentile of Response" by date_hour | eval "Start Time"=strftime(start_time,"%d/%m/%Y %I:%M:%S:%p") | eval "End Time"=strftime(end_time,"%d/%m/%Y %I:%M:%S:%p") | table "Start Time" "End Time" "Total # of executions" "Min Resp Time" "Avg Resp Time" "Max Resp Time" "Std Dev Of Resp Time" "90th percentile Resp Time" "Min Size of Response" "Avg Size of Response" "Max Size of Response" "Std Dev of Response"  "90th percentile of Response"

Happy Splunking

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

lfedak_splunk — Tue, 07 Nov 2017 17:56:55 GMT

Hey @saifullakhali, I converted @kamlesh_vaghela's answer to an answer (it was a comment). You can accept it and upvote any comments that helped you out to award karma points and to show that there's a working solution for your question.

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Tue, 07 Nov 2017 18:27:42 GMT

rex field="Resp Time" "TIME:0\/(?.*)"

The above code does not extract anything. It is blank when I run even though the log have entries.
TIME:0/140248

----------------------logs sample-----------
1x.xx.xxx.xxx - - xxxxxx [04/Oct/2017:00:00:32 -0400] - /lr/Ext/ HTTP/1.1 oxxxxm.xxx.xxx.net TIME:0/140248 "GET /lr/Ext/? HTTP/1.1" 200 19689

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Tue, 07 Nov 2017 23:29:03 GMT

I tried your latest query it worked after few changes in rex.

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Tue, 07 Nov 2017 23:29:50 GMT

Thanks kamlesh for the support.

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

kamlesh_vaghela — Wed, 08 Nov 2017 05:58:08 GMT

HI @saifullakhali,

Can you please accept my answer and close this question and upvote my comments that helped you?

Happy Splunking

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Tue, 29 Sep 2020 16:39:23 GMT

There is one small issue when I give search has
index="nam_404" earliest=10/03/2017:00:00:00 latest=10/04/2017:23:59:59

The Output is:

start time end time .................................
3/10/2017 00:00:00 4/10/2017 00:59:59 ..................................

expected is :
start time end time .....................................
3/10/2017 00:00:00 3/10/2017 00:59:59 .....................................
3/10/2017 01:00:00 3/10/2017 01:59:59 .....................................
.
.
3/10/2017 23:00:00 3/10/2017 23:59:59 .....................................

4/10/2017 00:00:00 4/10/2017 00:59:59 .....................................
4/10/2017 00:01:00 4/10/2017 01:59:59 .....................................
.
.
4/10/2017 23:00:00 4/10/2017 23:59:59 .....................................

-full search string---------
index="nam_404" earliest=10/03/2017:00:00:00 latest=10/04/2017:23:59:59 | rex field="Resp Time" "TIME:(?.)\/(?.)" | stats count AS "Total # of executions" min(_time) AS start_time max(_time) as end_time min(RESP_TIME) AS "Min Resp Time" avg(RESP_TIME) AS "Avg Resp Time" max(RESP_TIME) AS "Max Resp Time" stdev(RESP_TIME) AS "Std Dev Of Resp Time" perc90(RESP_TIME) AS "90th percentile Resp Time" min(field14) AS "Min Size of Response" avg(field14) AS "Avg Size of Response" max(field14) AS "Max Size of Response" stdev(field14) AS "Std Dev of Size" perc90(field14) AS "90th percentile of Size" by date_hour | eval "Start Time"=strftime(start_time,"%d/%m/%Y %I:%M:%S:%p") | eval "End Time"=strftime(end_time,"%d/%m/%Y %I:%M:%S:%p") | table "Start Time" "End Time" "Total # of executions" "Min Resp Time" "Avg Resp Time" "Max Resp Time" "Std Dev Of Resp Time" "90th percentile Resp Time" "Min Size of Response" "Avg Size of Response" "Max Size of Response" "Std Dev of Size" "90th percentile of Size"

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Thu, 09 Nov 2017 14:16:13 GMT

Can you please answer my last question as the output I am getting is the not the expected one.

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

kamlesh_vaghela — Tue, 29 Sep 2020 16:40:51 GMT

Hi
Sure,

index="nam_404" earliest=10/03/2017:00:00:00 latest=10/04/2017:23:59:59 
| rex field="Resp Time" "TIME:(?.)\/(?.)" 
| stats count AS "Total # of executions" min(_time) AS start_time max(_time) as end_time min(RESP_TIME) AS "Min Resp Time" avg(RESP_TIME) AS "Avg Resp Time" max(RESP_TIME) AS "Max Resp Time" stdev(RESP_TIME) AS "Std Dev Of Resp Time" perc90(RESP_TIME) AS "90th percentile Resp Time" min(field14) AS "Min Size of Response" avg(field14) AS "Avg Size of Response" max(field14) AS "Max Size of Response" stdev(field14) AS "Std Dev of Size" perc90(field14) AS "90th percentile of Size" **by date_month,date_mday,date_hour**
| eval "Start Time"=strftime(start_time,"%d/%m/%Y %I:%M:%S:%p") 
| eval "End Time"=strftime(end_time,"%d/%m/%Y %I:%M:%S:%p") 
| table "Start Time" "End Time" "Total # of executions" "Min Resp Time" "Avg Resp Time" "Max Resp Time" "Std Dev Of Resp Time" "90th percentile Resp Time" "Min Size of Response" "Avg Size of Response" "Max Size of Response" "Std Dev of Size" "90th percentile of Size"

Just Replace by date_hour with by date_month,date_mday,date_hour

Thanks

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Thu, 09 Nov 2017 14:51:54 GMT

Thanks it worked

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

saifullakhalid — Thu, 09 Nov 2017 16:42:36 GMT

Can you please answer the below question which is the continuation of same question

https://answers.splunk.com/answers/590385/how-do-you-display-the-of-requests-per-24-hours-of-1.html?minQuestionBodyLength=80

Re: How do you display the # of requests per 24 hours of log access (Between 00:00:00 and 23:59:59)?

kamlesh_vaghela — Fri, 10 Nov 2017 12:06:20 GMT

sure @saifullakhalid.