How to calculate the monthly wise data if we have data from (1jan 2018 to sep 31).

[rajhemant26]

Hello everyone.

Want to display the output only for the time which crosses 18 months (earliest time)

[Vijeta]

Try this

host=sbej* sourcetype=kekc_thjs R=* index=perf earliest=1514764800 latest=1538265600
| eval host_type=case(host LIKE "%wad%", "WAd") | bin span=1mon _time 
| streamstats count as Request by host_type, _time
| eval RequestsPerMin=Request/24*30/60
| eval RequestsPerSec=RequestsPerMin/60
|stats count(R) as Requests avg(RequestsPerSec) as AvgRequestsPerSec, max(RequestsPerSec) as MaxRequestsPerSec, p95(RequestsPerSec) as P95RequestsPerSec by _time host_type 
|eval AvgRequestsPerSec=round(AvgRequestsPerSec,2), MaxRequestsPerSec=round(MaxRequestsPerSec,2), P95RequestsPerSec=round(P95RequestsPerSec,2)
|sort -MaxRequestsPerSec, -P95RequestsPerSec

[somesoni2]

Give this a try. Since every month doesn't have same number of days, we'll actually calculate averages at hour level and then aggregating them at month level.

host=sbej* sourcetype=kekc_thjs R=* index=perf earliest=1514764800 latest=1538265600
| eval host_type=case(host LIKE "%wad%", "WAd") | bin span=1d _time 
| stats count as Request by host_type, _time
| eval RequestsPerMin=round(Request/(24*60),2)
| eval RequestsPerSec=round(RequestsPerMin/60,2)
| eval Month=strftime(_time,"%B") 
|stats sum(Request) as Requests avg(RequestsPerSec) as AvgRequestsPerSec, max(RequestsPerSec) as MaxRequestsPerSec, p95(RequestsPerSec) as P95RequestsPerSec by Month host_type 
| foreach Avg* [ eval <<FIELD>>=round('<<FIELD>>',2) ] 
|sort -MaxRequestsPerSec, -P95RequestsPerSec

[somesoni2]

Whats the expected output? what all columns you want to see?

[pramit46]

@rajhemant26,

If I were you, I would start with the base search and also change that streamstats to stats, just to see how it behaves. If that gives you the expected result then you can move forward. Can you please try this and let me know the output?

host=sbej* sourcetype=kekc_thjs R=* index=perf earliest=1514764800 latest=1538265600
| eval host_type=case(host LIKE "%wad%", "WAd") | bin span=1mon _time
| stats count as Request by host_type, _time

[vishaltaneja070]

Hello Rajhemant26,

Why don't you create a use field date_month which is already availabe by default. Based on that you can use stats command to club event by month.

Thanks.