topic Re: How to rewite a query to change the columns to rows and the rows to columns? in Splunk Search

How to rewite a query to change the columns to rows and the rows to columns?

sangs8788 — Thu, 30 Aug 2018 04:58:37 GMT

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.

Re: How to rewite a query to change the columns to rows and the rows to columns?

Shan — Thu, 30 Aug 2018 05:19:23 GMT

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

Re: How to rewite a query to change the columns to rows and the rows to columns?

sangs8788 — Thu, 30 Aug 2018 06:57:51 GMT

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

Re: How to rewite a query to change the columns to rows and the rows to columns?

niketn — Thu, 30 Aug 2018 07:30:14 GMT

@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"

Re: How to rewite a query to change the columns to rows and the rows to columns?

sangs8788 — Thu, 30 Aug 2018 07:42:15 GMT

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 ?

Re: How to rewite a query to change the columns to rows and the rows to columns?

niketn — Thu, 30 Aug 2018 09:11:52 GMT

@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)

Re: How to rewite a query to change the columns to rows and the rows to columns?

sangs8788 — Thu, 30 Aug 2018 10:00:51 GMT

Thanks a lot for your response. This would work for me.