Historical Data Through WebUI Tabular Charts

[ArturoSantander]

Hello everyone,

We are currently facing the necessity of extracting CPU Busy % and DISC % data from our NonStop servers, we have already created the report for 50 days of the information on the GUI stored on Trouble Shooting DB collection, this per CPU.

The challenge here is that we need that information on a tabular chart view for the client because with that raw data they can analyse it and find trends on capacity planning.

We have explored the WEB REPORT option on reports central to schedule the extraction of data every month but when we SEND TO WEB the generated report , we only see one interval of data per day. The colletion interval is set to 30 minutes, but our users need the information of an entire day. If my math is not wrong, we need to see 48 intervals, 30min each for every CPU on our systems.

Please your help on this team.

[ChristopherS]

@ArturoSantander 

The complicating factor here is that you are trying to display multiple metrics and multiple intervals (30 minute windows) per day in a tabular display. So there is a quite a few caveats sometimes on doing this in the IRGUI and a few more in WebUI via a display.

If you are familar with PQL and Extractor this is very easy.. though we acknowledge not everyone is.. so in the Services Organization we have done this for a couple of customers on other records but not specifically what you have here.

But doing it in PQL would be something like the following but adding a breakdown over a 30 minute window vs. by day.

"SELECT 'NonStopCPU', META.NODE, DATEPART(DAYOFYEAR, META.INTERVAL) as JulianDay, DATEPART(YEAR,META.INTERVAL) + '-' + DATEPART(MONTH,META.INTERVAL) + '-' + DATEPART(DAY,META.INTERVAL) as DataDay, MAX(BUSYPercent) as MaxCPU , MIN(BUSYPercent) as MinCPU, ROUND(AVG(BUSYPercent),3) as AvgCPU, ROUND(MAX(ACCELBUS),2) as MaxACL, ROUND(MIN(ACCELBUS),2) as MinACL, ROUND(AVG(ACCELBUS),2) as AvgACL, ROUND(MAX(BUSYPercentSystem),2) as SysMax, ROUND(MIN(BUsyPercentSystem),2) as SysMin, ROUND(AVG(BusyPercentSystem),2) as SysAvg FROM CPU DATABASE 'Trouble Shooting' DBNODE '\NSKIT06' PERIOD StartofDay() - 30 DAYS TO END GROUP BY META.NODE, DataDay, JulianDay ORDER BY DataDay Desc"

NonStopCPU | META.Node | JulianDay | DataDay   | MaxCPU | MinCPU | AvgCPU | MaxACL | MinACL | AvgACL | SysMax | SysMin | SysAvg |

__________________________________________________________________________________________________________________________________

NonStopCPU | \IRFREE |        40 | 2020-2-9  |     32 |      1 |  5.082 |  32.00 |   1.00 |   5.08 |   8.10 |   0.30 |   1.87 |

NonStopCPU | \IRFREE  |        39 | 2020-2-8  |     58 |      1 |  7.784 |  58.00 |   1.00 |   7.78 |  14.70 |   0.30 |   2.79 |

NonStopCPU | \IRFREE  |        38 | 2020-2-7  |     60 |      1 |  9.697 |  60.00 |   1.00 |   9.70 |  16.70 |   0.40 |   3.40 |

NonStopCPU | \IRFREE  |        37 | 2020-2-6  |     60 |      1 |  9.907 |  60.00 |   1.00 |   9.91 |  15.90 |   0.40 |   3.56 |

Can I ask if you have gotten it working in IRGUI / Windows Desktop Client exactly the way you want it to work and can you share that (emailing me is fine)?

[ArturoSantander]

Hello @ChristopherS !

First of all hope you are doing great, thanks a lot for the response on IR Forum!

We are not familiar with using PQL/Extractor and for what I know we do not have that module configured.

Is it 100% necessary to get the data through the use of PQL or could we get the information other way around?

To explain a little bit further, on GUI we are getting the following information:

But we would need something similar of an historic display of information, also in a tabular way so as to be able to later on proceed to send them to 3^rd users to analyze the information.

Like, the whole one day of data on 30min intervals (or any other interval required, maybe 1 hour, etc). The need here is for that, how was the CPU performance on a whole day (later to extract a month of information).

Like the following, i have emailed you the complete information.

Node	BUSY	DISC	CPU	Interval	TIMESTAMP
Name	%	%	NO	Start
\SPRMY03	35	13.1	2	02/07/2020 00:00	Thu Jul 02nd 2020 00:00:00
\SPRMY03	28	5.4	3	02/07/2020 00:00	Thu Jul 02nd 2020 00:00:00
\SPRMY03	39	4.4	1	02/07/2020 00:00	Thu Jul 02nd 2020 00:00:00
\SPRMY03	23	4.5	0	02/07/2020 00:00	Thu Jul 02nd 2020 00:00:00
\SPRMY03	43	11.6	2	02/07/2020 00:30	Thu Jul 02nd 2020 00:30:00
\SPRMY03	44	11.1	3	02/07/2020 00:30

[ChristopherS]

The following PQL will return results in 30 minute windows for the last 2 days by CPU.

..\x64\irpqlcli -r -w 300 "SELECT 'NonStopCPU', META.NODE, CASE WHEN DATEPART(MINUTE,META.INTERVAL) <= 30  THEN datepart(year, meta.interval) + '-' + datepart(month,meta.interval) + '-' + datepart(day,meta.interval)  + ' ' + datepart(hour,meta.interval) + ':' + '00' ELSE datepart(year, meta.interval) + '-' + datepart(month,meta.interval) + '-' + datepart(day,meta.interval)  + ' ' +  datepart(hour,meta.interval) + ':' + '30' END as TimeSlot, CPUNO,  MAX(BUSYPercent) as MaxCPU , MIN(BUSYPercent) as MinCPU, ROUND(AVG(BUSYPercent),3) as AvgCPU, ROUND(MAX(ACCELBUS),2) as MaxACL, ROUND(MIN(ACCELBUS),2) as MinACL, ROUND(AVG(ACCELBUS),2) as AvgACL, ROUND(MAX(BUSYPercentSystem),2) as SysMax, ROUND(MIN(BUsyPercentSystem),2) as SysMin, ROUND(AVG(BusyPercentSystem),2) as SysAvg, MAX(DISC%) as DiskMax, ROUND(AVG(DISC%),3) as DiskAVG  FROM CPU DATABASE 'Trouble Shooting' DBNODE '\NSK' PERIOD StartofDay() - 2 DAYS TO END GROUP BY META.NODE, CPUNO, TimeSlot ORDER BY TimeSlot, CPUNO" > NSKCPUSTAT.txt

Example Output:

NonStopCPU | META.Node | TimeSlot        | CPUNO | MaxCPU | MinCPU | AvgCPU | MaxACL | MinACL | AvgACL | SysMax | SysMin | SysAvg | DiskMax | DiskAVG |

_________________________________________________________________________________________________

NonStopCPU | \NSKIT06  | 2020-8-15 10:00 |     0 |      8 |      6 |  6.571 |   8.00 |   6.00 |   6.57 |   5.10 |   3.50 |   4.13 |     4.0 |   3.129 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:00 |     1 |      3 |      1 |  1.857 |   3.00 |   1.00 |   1.86 |   0.50 |   0.50 |   0.50 |     0.2 |   0.200 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:00 |     2 |      4 |      2 |  3.286 |   4.00 |   2.00 |   3.29 |   2.50 |   1.40 |   1.94 |     0.3 |   0.214 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:00 |     3 |      6 |      4 |  4.714 |   6.00 |   4.00 |   4.71 |   1.10 |   0.70 |   0.87 |     0.1 |   0.029 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:30 |     0 |      4 |      2 |  3.000 |   4.00 |   2.00 |   3.00 |   2.10 |   1.40 |   1.68 |     1.4 |   1.060 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:30 |     1 |      1 |      0 |  0.200 |   1.00 |   0.00 |   0.20 |   0.30 |   0.20 |   0.28 |     0.1 |   0.100 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:30 |     2 |      1 |      0 |  0.600 |   1.00 |   0.00 |   0.60 |   0.60 |   0.10 |   0.42 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 10:30 |     3 |      3 |      2 |  2.400 |   3.00 |   2.00 |   2.40 |   0.60 |   0.50 |   0.56 |     0.1 |   0.040 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:00 |     0 |      3 |      2 |  2.571 |   3.00 |   2.00 |   2.57 |   1.80 |   1.20 |   1.41 |     1.2 |   0.829 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:00 |     1 |      0 |      0 |  0.000 |   0.00 |   0.00 |   0.00 |   0.30 |   0.30 |   0.30 |     0.1 |   0.100 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:00 |     2 |      1 |      0 |  0.429 |   1.00 |   0.00 |   0.43 |   0.60 |   0.10 |   0.36 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:00 |     3 |      3 |      1 |  1.571 |   3.00 |   1.00 |   1.57 |   0.60 |   0.50 |   0.53 |     0.1 |   0.029 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:30 |     0 |      3 |      2 |  2.600 |   3.00 |   2.00 |   2.60 |   1.80 |   1.20 |   1.50 |     1.2 |   0.940 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:30 |     1 |      1 |      0 |  0.400 |   1.00 |   0.00 |   0.40 |   0.30 |   0.20 |   0.28 |     0.1 |   0.080 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:30 |     2 |      1 |      0 |  0.400 |   1.00 |   0.00 |   0.40 |   0.60 |   0.20 |   0.42 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 11:30 |     3 |      3 |      1 |  1.800 |   3.00 |   1.00 |   1.80 |   0.60 |   0.50 |   0.54 |     0.1 |   0.040 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:00 |     0 |      3 |      2 |  2.714 |   3.00 |   2.00 |   2.71 |   2.00 |   1.40 |   1.60 |     1.4 |   1.029 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:00 |     1 |      1 |      1 |  1.000 |   1.00 |   1.00 |   1.00 |   0.30 |   0.30 |   0.30 |     0.1 |   0.100 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:00 |     2 |      1 |      0 |  0.571 |   1.00 |   0.00 |   0.57 |   0.60 |   0.20 |   0.43 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:00 |     3 |      3 |      1 |  1.571 |   3.00 |   1.00 |   1.57 |   0.70 |   0.50 |   0.54 |     0.1 |   0.029 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:30 |     0 |      3 |      2 |  2.600 |   3.00 |   2.00 |   2.60 |   2.00 |   1.40 |   1.62 |     1.4 |   1.040 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:30 |     1 |      1 |      0 |  0.800 |   1.00 |   0.00 |   0.80 |   0.30 |   0.30 |   0.30 |     0.1 |   0.100 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:30 |     2 |      1 |      0 |  0.600 |   1.00 |   0.00 |   0.60 |   0.60 |   0.10 |   0.34 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 12:30 |     3 |      3 |      1 |  1.800 |   3.00 |   1.00 |   1.80 |   0.60 |   0.50 |   0.56 |     0.1 |   0.040 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:00 |     0 |      3 |      2 |  2.429 |   3.00 |   2.00 |   2.43 |   1.90 |   1.40 |   1.59 |     1.3 |   1.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:00 |     1 |      0 |      0 |  0.000 |   0.00 |   0.00 |   0.00 |   0.30 |   0.30 |   0.30 |     0.1 |   0.086 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:00 |     2 |      1 |      1 |  1.000 |   1.00 |   1.00 |   1.00 |   0.60 |   0.20 |   0.41 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:00 |     3 |      3 |      1 |  1.571 |   3.00 |   1.00 |   1.57 |   0.60 |   0.50 |   0.54 |     0.1 |   0.029 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:30 |     0 |      3 |      2 |  2.400 |   3.00 |   2.00 |   2.40 |   2.00 |   1.40 |   1.66 |     1.4 |   1.080 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:30 |     1 |      0 |      0 |  0.000 |   0.00 |   0.00 |   0.00 |   0.30 |   0.30 |   0.30 |     0.1 |   0.100 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:30 |     2 |      2 |      0 |  1.000 |   2.00 |   0.00 |   1.00 |   0.60 |   0.10 |   0.42 |     0.0 |   0.000 |

NonStopCPU | \NSKIT06  | 2020-8-15 13:30 |     3 |      3 |      1 |  2.200 |   3.00 |   1.00 |   2.20 |   0.60 |   0.50 |   0.56 |     0.1 |   0.040 |