Splunk Search

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

sangs8788
Communicator

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.

0 Karma
1 Solution

niketn
Legend

@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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

sangs8788
Communicator

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 ?

0 Karma

niketn
Legend

@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)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sangs8788
Communicator

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

Shan
Builder

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

0 Karma

sangs8788
Communicator

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...