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

[saifullakhalid]

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

[kamlesh_vaghela · Accepted solution]

Hi

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

[saifullakhalid]

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

[kamlesh_vaghela]

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

[saifullakhalid]

Thanks kamlesh for the support.

[kamlesh_vaghela]

HI @saifullakhali,

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

Happy Splunking

[saifullakhalid]

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

[kamlesh_vaghela]

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

[saifullakhalid]

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?...

[kamlesh_vaghela]

sure @saifullakhalid.

[saifullakhalid]

Thanks it worked

[saifullakhalid]

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"

[saifullakhalid]

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

[saifullakhalid]

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

[lfedak_splunk]

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.

[saifullakhalid]

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

---