How to convert below query such that rows are converted to columns
index=data earliest=-1w@w latest=now |eval requestcount=1 | timechart per_second(requestcount) AS RequestPerSec
| eventstats max(RequestPerSec) as peakRequestPerSec | timechart span=1w avg(RequestPerSec) as avgRequestPerSec max(RequestPerSec) as peakRequestPerSec p99(RequestPerSec) as p95RequestPerSec
| fieldformat peakTime=strftime(peakTime,"%m/%y %H:%M") | eval avgRequestPerSec=round(avgRequestPerSec,2) | eval peakRequestPerSec=round(peakRequestPerSec,2)| eval p95RequestPerSec=round(p95RequestPerSec,2)|rename avgRequestPerSec as "Average Requests/Sec" peakRequestPerSec as "Max Requests/Sec" p95RequestPerSec as "P95 Requests/Sec"
And also have an additional column added to the converted table which is to calculate/show the Percentage of increase/decrease compared to previous week data? Please let me know.
@sangs8788 add the following to your existing query (PS: Time needs to be formatted as String time in YYYY-mm-dd for sorting of date columns):
<yourCurrentSearch>
| eval Time=strftime(_time,"%Y-%m-%d")
| fields - _*
| transpose header_field="Time" column_name="Group"
@sangs8788 add the following to your existing query (PS: Time needs to be formatted as String time in YYYY-mm-dd for sorting of date columns):
<yourCurrentSearch>
| eval Time=strftime(_time,"%Y-%m-%d")
| fields - _*
| transpose header_field="Time" column_name="Group"
Perfect. Thanks. This will do. But again, if i have to calculate the percentage of increase or decrease between two dates it has to be done after the transpose isnt it ?
@sangs8788 , yes but if you want to further calculations you can give your Columns with Date static names like Last Week
and Current Week
Following is a run anywhere example based on Splunk's _internal index
index=_internal earliest=-1w@w latest=now
| eval requestcount=1
| timechart per_second(requestcount) AS RequestPerSec
| eventstats max(RequestPerSec) as peakRequestPerSec
| bin _time span=1w
| eval Time=strftime(_time,"%Y-%m-%d")
| chart avg(RequestPerSec) as avgRequestPerSec max(RequestPerSec) as peakRequestPerSec p99(RequestPerSec) as p95RequestPerSec by Time
| fieldformat peakTime=strftime(peakTime,"%m/%y %H:%M")
| eval avgRequestPerSec=round(avgRequestPerSec,2)
| eval peakRequestPerSec=round(peakRequestPerSec,2)
| eval p95RequestPerSec=round(p95RequestPerSec,2)
| rename avgRequestPerSec as "Average Requests/Sec" peakRequestPerSec as "Max Requests/Sec" p95RequestPerSec as "P95 Requests/Sec"
| streamstats count as sno
| eval Time=sno
| fields - _* sno
| transpose header_field="Time" column_name="Group"
| rename "1" as "Last Week", "2" as "Current Week"
| eval rate=round((('Current Week'-'Last Week')/'Current Week')*100,2)
Thanks a lot for your response. This would work for me.
@sangs8788,
Please try below query..
index=data earliest=-1w@w latest=now
| eval requestcount=1
| timechart per_second(requestcount) AS RequestPerSec
| eventstats max(RequestPerSec) as peakRequestPerSec
| timechart span=1w avg(RequestPerSec) as avgRequestPerSec max(RequestPerSec) as peakRequestPerSec p99(RequestPerSec) as p95RequestPerSec
| fieldformat peakTime=strftime(peakTime,"%m/%y %H:%M")
| eval avgRequestPerSec=round(avgRequestPerSec,2)
| eval peakRequestPerSec=round(peakRequestPerSec,2)
| eval p95RequestPerSec=round(p95RequestPerSec,2)
| eval group=avgRequestPerSec."#".peakRequestPerSec | chart Count as check over group by p95RequestPerSec | rex field=group "(?<avgRequestPerSec>[^#]+)#(?<peakRequestPerSec>[^#]+)") | fields - group
| table avgRequestPerSec peakRequestPerSec p95RequestPerSec
refer below answer also
https://answers.splunk.com/answers/467941/how-to-convert-partial-rows-into-columns-1.html
Thanks ..
This doesnt work since it also again gives me the Avg,Peak & p95 as columns. I need it in below format
Group | 12-08-2018 | 05-08-2018
AvgRequestPerSec | 34 |65
MaxRequestPerSec | 20 |99
P95RequestPerSec | 19 | 50