How to create a scatter chart over an undetermined number of events?

[tobias]

Hello,

I am very new to splunk and would greatly appreciate any advice.

I have a collection of reports that each contain the following fields:

BM3_SGA_TGT_SIZE BM2_SGA_TGT_SIZE BM1_SGA_TGT_SIZE B_SGA_TGT_SIZE BP1_SGA_TGT_SIZE BP2_SGA_TGT_SIZE BP3_SGA_TGT_SIZE

BM3_EST_PHYREAD BM2_EST_PHYREAD BM1_EST_PHYREAD B_EST_PHYREAD BP1_EST_PHYREAD BP2_EST_PHYREAD BP3_EST_PHYREAD

BM3_EST_DBTIME BM2_EST_DBTIME BM1_EST_DBTIME B_EST_DBTIME BP1_EST_DBTIME BP2_EST_DBTIME BP3_EST_DBTIME

Each report has been added to splunk as an event.

I would like to create a single scatter that aggregates the data across all the events and plots according to the following:

(1st Y-Axis) BM3_EST_PHYREAD and (2nd Y-Axis)BM3_EST_DBTIME over (X-axis) BM3_SGA_TGT_SIZE

(1st Y-Axis) BM2_EST_PHYREAD and (2nd Y-Axis)BM2_EST_DBTIME over (X-axis) BM2_SGA_TGT_SIZE

(1st Y-Axis) BM1_EST_PHYREAD and (2nd Y-Axis)BM1_EST_DBTIME over (X-axis) BM1_SGA_TGT_SIZE

(1st Y-Axis) B_EST_PHYREAD and (2nd Y-Axis)B_EST_DBTIME over (X-axis) B_SGA_TGT_SIZE

(1st Y-Axis) BP1_EST_PHYREAD and (2nd Y-Axis)BP1_EST_DBTIME over (X-axis) BP1_SGA_TGT_SIZE

(1st Y-Axis) BP2_EST_PHYREAD and (2nd Y-Axis)BP2_EST_DBTIME over (X-axis) BP2_SGA_TGT_SIZE

(1st Y-Axis) BP3_EST_PHYREAD and (2nd Y-Axis)BP3_EST_DBTIME over (X-axis) BP3_SGA_TGT_SIZE

The values and order of TGT_SIZE will be consistent across all events.

The values of PYHREAD and DBTIME will vary across events.

Thus far I have created the following search query:

index=oraawr sourcetype="awr_general_stats" CUSTOMER_NAME="U*" DB_NAME="SA*" INSTANCE_NAME="sa*1" SNAP_ID=*
| eval TGT_SIZE=mvappend(BM3_SGA_TGT_SIZE,BM2_SGA_TGT_SIZE,BM1_SGA_TGT_SIZE,B_SGA_TGT_SIZE,BP1_SGA_TGT_SIZE,BP2_SGA_TGT_SIZE,BP3_SGA_TGT_SIZE)
| eval EST_PHYREAD=mvappend(BM3_EST_PHYREAD,BM2_EST_PHYREAD,BM1_EST_PHYREAD,B_EST_PHYREAD,BP1_EST_PHYREAD,BP2_EST_PHYREAD,BP3_EST_PHYREAD)
| eval EST_DBTIME=mvappend(BM3_EST_DBTIME,BM2_EST_DBTIME,BM1_EST_DBTIME,B_EST_DBTIME,BP1_EST_DBTIME,BP2_EST_DBTIME,BP3_EST_DBTIME)
| table TGT_SIZE EST_PHYREAD EST_DBTIME

It returns the following table:

TGT_SIZE	EST_PHYREAD	EST_PHYREAD
4280 5136 5992 6848 7704 8560 9416	153575927 87554052 58277513 47511424 42042859 38764571 38764571	521192 460130 440788 433676 430033 427867 427433
4280 5136 5992 6848 7704 8560 9416	146642502 78447471 57377856 45913304 41634184 38158547 36836244	505028 448212 434136 426544 423644 421383 420402
4280 5136 5992 6848 7704 8560 9416	115711228 68383308 53069056 45564571 40324645 37909723 36597463	480300 441426 424928 419806 416406 414684 413761
4280 5136 5992 6848 7704 8560 9416	107453756 62497844 48506164 41866187 38114977 35611379 33961851	466344 427121 417780 413316 410795 409100 407984

......<continue>.....

Each row corresponds to a single event.  In this particular case there are 93 events, so there are 93 rows.  

My initial thought was to consolidate  the rows.  So instead of 93x 7 values of TGT_SIZE I would have 691 rows, keeping the order.  Do the same for EST_PHYREAD and EST_DBTIME.

But I can't seem to figure out the correct commands to use.

I guidance would be greatly appreciated.

Thanks,

[bowesmana · Accepted solution]

If you want to expand those rows, you first make a composite field of the 3 values. expand them and split the values out again. like this. I'm guessing the second column in your example was EST_DBTIME, not EST_PHYREAD

... your search ...
| eval data=mvzip(mvzip(TGT_SIZE,EST_PHYREAD,","),EST_DBTIME, ",")
| fields data
| mvexpand data
| rex field=data "(?<TGT_SIZE>[^,]*),(?<EST_PHYREAD>[^,]*),(?<EST_DBTIME>.*)"
| fields TGT_SIZE EST_PHYREAD EST_DBTIME

[tobias]

Thank you very much.  That was very helpful.  Now I just have to find a good scatter visualization.  Do you have any suggestions?

Thanks!

[bowesmana]

I've used the 3D Scatterplot viz from Splunkbase

https://splunkbase.splunk.com/app/3138

as well as the default one, but the default is a bit clunky.

Other than that, I've not used any other