diff options
37 files changed, 1994 insertions, 0 deletions
diff --git a/.github/ISSUE_TEMPLATE/bug_report.md b/.github/ISSUE_TEMPLATE/bug_report.md new file mode 100644 index 0000000..0dc6520 --- /dev/null +++ b/.github/ISSUE_TEMPLATE/bug_report.md @@ -0,0 +1,44 @@ +--- +name: Bug report +about: Create a report to help us improve +title: '' +labels: '' +assignees: '' + +--- + +## Describe the bug + +A clear and concise description of what the bug is. + +## To Reproduce + +Steps to reproduce the behavior: + +1. +2. +3. +4. + +## Expected behavior + +A clear and concise description of what you expected to happen. + +## Screenshots + +If applicable, add screenshots to help explain your problem. + +## Your Environment + +* Module version: +* Dependent module versions: +* Icinga Web 2 version and modules (System - About): +* Web browser and version: +* Icinga 2 version used (`icinga2 --version`): +* MySQL version (`mysql --version`): +* PHP version used (`php --version`): +* Server operating system and version: + +## Additional context + +Add any other context about the problem here. diff --git a/.github/ISSUE_TEMPLATE/feature_request.md b/.github/ISSUE_TEMPLATE/feature_request.md new file mode 100644 index 0000000..a7621fd --- /dev/null +++ b/.github/ISSUE_TEMPLATE/feature_request.md @@ -0,0 +1,20 @@ +--- +name: Feature request +about: Suggest an idea for this project +title: '' +labels: '' +assignees: '' + +--- + +## Is your feature request related to a problem? Please describe. +A clear and concise description of what the problem is. Ex. I'm always using this feature but am missing [...] + +## Describe the solution you'd like +A clear and concise description of what you want to happen. + +## Describe alternatives you've considered +A clear and concise description of any alternative solutions or features you've considered. + +## Additional context +Add any other context or screenshots about the feature request here. diff --git a/.github/workflows/php.yml b/.github/workflows/php.yml new file mode 100644 index 0000000..e3b2d7e --- /dev/null +++ b/.github/workflows/php.yml @@ -0,0 +1,42 @@ +name: PHP Tests + +on: + push: + branches: + - master + - release/* + pull_request: + branches: + - master + +jobs: + lint: + name: Static analysis for php ${{ matrix.php }} on ${{ matrix.os }} + runs-on: ${{ matrix.os }} + + strategy: + fail-fast: false + matrix: + php: ['7.2', '7.3', '7.4', '8.0', '8.1', '8.2'] + os: ['ubuntu-latest'] + + steps: + - name: Checkout code base + uses: actions/checkout@v3 + + - name: Setup PHP + uses: shivammathur/setup-php@v2 + with: + php-version: ${{ matrix.php }} + tools: phpcs + + - name: Setup dependencies + run: composer require -n --no-progress overtrue/phplint + + - name: PHP Lint + if: success() || matrix.allow_failure + run: ./vendor/bin/phplint -n --exclude={^vendor/.*} -- . + + - name: PHP CodeSniffer + if: success() || matrix.allow_failure + run: phpcs diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..1f03d4b --- /dev/null +++ b/.gitignore @@ -0,0 +1,5 @@ +# Exclude all hidden files +.* + +# Except those related to Git +!.git* diff --git a/.phpcs.xml b/.phpcs.xml new file mode 100644 index 0000000..2a93e4b --- /dev/null +++ b/.phpcs.xml @@ -0,0 +1,26 @@ +<?xml version="1.0"?> +<ruleset name="PHP_CodeSniffer"> + <description>Sniff our code a while</description> + + <file>./</file> + + <exclude-pattern>vendor/*</exclude-pattern> + + <arg name="report-width" value="auto"/> + <arg name="report-full"/> + <arg name="report-gitblame"/> + <arg name="report-summary"/> + <arg name="encoding" value="UTF-8"/> + <arg name="extensions" value="php"/> + + <rule ref="PSR12"> + <exclude name="PSR12.Properties.ConstantVisibility.NotFound"/> + </rule> + + <rule ref="Generic.Files.LineLength"> + <properties> + <property name="lineLimit" value="120"/> + <property name="absoluteLineLimit" value="0"/> + </properties> + </rule> +</ruleset> @@ -0,0 +1,339 @@ + GNU GENERAL PUBLIC LICENSE + Version 2, June 1991 + + Copyright (C) 1989, 1991 Free Software Foundation, Inc., + 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA + Everyone is permitted to copy and distribute verbatim copies + of this license document, but changing it is not allowed. + + Preamble + + The licenses for most software are designed to take away your +freedom to share and change it. By contrast, the GNU General Public +License is intended to guarantee your freedom to share and change free +software--to make sure the software is free for all its users. This +General Public License applies to most of the Free Software +Foundation's software and to any other program whose authors commit to +using it. (Some other Free Software Foundation software is covered by +the GNU Lesser General Public License instead.) You can apply it to +your programs, too. + + When we speak of free software, we are referring to freedom, not +price. Our General Public Licenses are designed to make sure that you +have the freedom to distribute copies of free software (and charge for +this service if you wish), that you receive source code or can get it +if you want it, that you can change the software or use pieces of it +in new free programs; and that you know you can do these things. + + To protect your rights, we need to make restrictions that forbid +anyone to deny you these rights or to ask you to surrender the rights. +These restrictions translate to certain responsibilities for you if you +distribute copies of the software, or if you modify it. + + For example, if you distribute copies of such a program, whether +gratis or for a fee, you must give the recipients all the rights that +you have. You must make sure that they, too, receive or can get the +source code. And you must show them these terms so they know their +rights. + + We protect your rights with two steps: (1) copyright the software, and +(2) offer you this license which gives you legal permission to copy, +distribute and/or modify the software. + + Also, for each author's protection and ours, we want to make certain +that everyone understands that there is no warranty for this free +software. If the software is modified by someone else and passed on, we +want its recipients to know that what they have is not the original, so +that any problems introduced by others will not reflect on the original +authors' reputations. + + Finally, any free program is threatened constantly by software +patents. We wish to avoid the danger that redistributors of a free +program will individually obtain patent licenses, in effect making the +program proprietary. To prevent this, we have made it clear that any +patent must be licensed for everyone's free use or not licensed at all. + + The precise terms and conditions for copying, distribution and +modification follow. + + GNU GENERAL PUBLIC LICENSE + TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION + + 0. This License applies to any program or other work which contains +a notice placed by the copyright holder saying it may be distributed +under the terms of this General Public License. The "Program", below, +refers to any such program or work, and a "work based on the Program" +means either the Program or any derivative work under copyright law: +that is to say, a work containing the Program or a portion of it, +either verbatim or with modifications and/or translated into another +language. (Hereinafter, translation is included without limitation in +the term "modification".) Each licensee is addressed as "you". + +Activities other than copying, distribution and modification are not +covered by this License; they are outside its scope. The act of +running the Program is not restricted, and the output from the Program +is covered only if its contents constitute a work based on the +Program (independent of having been made by running the Program). +Whether that is true depends on what the Program does. + + 1. You may copy and distribute verbatim copies of the Program's +source code as you receive it, in any medium, provided that you +conspicuously and appropriately publish on each copy an appropriate +copyright notice and disclaimer of warranty; keep intact all the +notices that refer to this License and to the absence of any warranty; +and give any other recipients of the Program a copy of this License +along with the Program. + +You may charge a fee for the physical act of transferring a copy, and +you may at your option offer warranty protection in exchange for a fee. + + 2. You may modify your copy or copies of the Program or any portion +of it, thus forming a work based on the Program, and copy and +distribute such modifications or work under the terms of Section 1 +above, provided that you also meet all of these conditions: + + a) You must cause the modified files to carry prominent notices + stating that you changed the files and the date of any change. + + b) You must cause any work that you distribute or publish, that in + whole or in part contains or is derived from the Program or any + part thereof, to be licensed as a whole at no charge to all third + parties under the terms of this License. + + c) If the modified program normally reads commands interactively + when run, you must cause it, when started running for such + interactive use in the most ordinary way, to print or display an + announcement including an appropriate copyright notice and a + notice that there is no warranty (or else, saying that you provide + a warranty) and that users may redistribute the program under + these conditions, and telling the user how to view a copy of this + License. (Exception: if the Program itself is interactive but + does not normally print such an announcement, your work based on + the Program is not required to print an announcement.) + +These requirements apply to the modified work as a whole. If +identifiable sections of that work are not derived from the Program, +and can be reasonably considered independent and separate works in +themselves, then this License, and its terms, do not apply to those +sections when you distribute them as separate works. But when you +distribute the same sections as part of a whole which is a work based +on the Program, the distribution of the whole must be on the terms of +this License, whose permissions for other licensees extend to the +entire whole, and thus to each and every part regardless of who wrote it. + +Thus, it is not the intent of this section to claim rights or contest +your rights to work written entirely by you; rather, the intent is to +exercise the right to control the distribution of derivative or +collective works based on the Program. + +In addition, mere aggregation of another work not based on the Program +with the Program (or with a work based on the Program) on a volume of +a storage or distribution medium does not bring the other work under +the scope of this License. + + 3. You may copy and distribute the Program (or a work based on it, +under Section 2) in object code or executable form under the terms of +Sections 1 and 2 above provided that you also do one of the following: + + a) Accompany it with the complete corresponding machine-readable + source code, which must be distributed under the terms of Sections + 1 and 2 above on a medium customarily used for software interchange; or, + + b) Accompany it with a written offer, valid for at least three + years, to give any third party, for a charge no more than your + cost of physically performing source distribution, a complete + machine-readable copy of the corresponding source code, to be + distributed under the terms of Sections 1 and 2 above on a medium + customarily used for software interchange; or, + + c) Accompany it with the information you received as to the offer + to distribute corresponding source code. (This alternative is + allowed only for noncommercial distribution and only if you + received the program in object code or executable form with such + an offer, in accord with Subsection b above.) + +The source code for a work means the preferred form of the work for +making modifications to it. For an executable work, complete source +code means all the source code for all modules it contains, plus any +associated interface definition files, plus the scripts used to +control compilation and installation of the executable. However, as a +special exception, the source code distributed need not include +anything that is normally distributed (in either source or binary +form) with the major components (compiler, kernel, and so on) of the +operating system on which the executable runs, unless that component +itself accompanies the executable. + +If distribution of executable or object code is made by offering +access to copy from a designated place, then offering equivalent +access to copy the source code from the same place counts as +distribution of the source code, even though third parties are not +compelled to copy the source along with the object code. + + 4. You may not copy, modify, sublicense, or distribute the Program +except as expressly provided under this License. Any attempt +otherwise to copy, modify, sublicense or distribute the Program is +void, and will automatically terminate your rights under this License. +However, parties who have received copies, or rights, from you under +this License will not have their licenses terminated so long as such +parties remain in full compliance. + + 5. You are not required to accept this License, since you have not +signed it. However, nothing else grants you permission to modify or +distribute the Program or its derivative works. These actions are +prohibited by law if you do not accept this License. Therefore, by +modifying or distributing the Program (or any work based on the +Program), you indicate your acceptance of this License to do so, and +all its terms and conditions for copying, distributing or modifying +the Program or works based on it. + + 6. Each time you redistribute the Program (or any work based on the +Program), the recipient automatically receives a license from the +original licensor to copy, distribute or modify the Program subject to +these terms and conditions. You may not impose any further +restrictions on the recipients' exercise of the rights granted herein. +You are not responsible for enforcing compliance by third parties to +this License. + + 7. If, as a consequence of a court judgment or allegation of patent +infringement or for any other reason (not limited to patent issues), +conditions are imposed on you (whether by court order, agreement or +otherwise) that contradict the conditions of this License, they do not +excuse you from the conditions of this License. If you cannot +distribute so as to satisfy simultaneously your obligations under this +License and any other pertinent obligations, then as a consequence you +may not distribute the Program at all. For example, if a patent +license would not permit royalty-free redistribution of the Program by +all those who receive copies directly or indirectly through you, then +the only way you could satisfy both it and this License would be to +refrain entirely from distribution of the Program. + +If any portion of this section is held invalid or unenforceable under +any particular circumstance, the balance of the section is intended to +apply and the section as a whole is intended to apply in other +circumstances. + +It is not the purpose of this section to induce you to infringe any +patents or other property right claims or to contest validity of any +such claims; this section has the sole purpose of protecting the +integrity of the free software distribution system, which is +implemented by public license practices. Many people have made +generous contributions to the wide range of software distributed +through that system in reliance on consistent application of that +system; it is up to the author/donor to decide if he or she is willing +to distribute software through any other system and a licensee cannot +impose that choice. + +This section is intended to make thoroughly clear what is believed to +be a consequence of the rest of this License. + + 8. If the distribution and/or use of the Program is restricted in +certain countries either by patents or by copyrighted interfaces, the +original copyright holder who places the Program under this License +may add an explicit geographical distribution limitation excluding +those countries, so that distribution is permitted only in or among +countries not thus excluded. In such case, this License incorporates +the limitation as if written in the body of this License. + + 9. The Free Software Foundation may publish revised and/or new versions +of the General Public License from time to time. Such new versions will +be similar in spirit to the present version, but may differ in detail to +address new problems or concerns. + +Each version is given a distinguishing version number. If the Program +specifies a version number of this License which applies to it and "any +later version", you have the option of following the terms and conditions +either of that version or of any later version published by the Free +Software Foundation. If the Program does not specify a version number of +this License, you may choose any version ever published by the Free Software +Foundation. + + 10. If you wish to incorporate parts of the Program into other free +programs whose distribution conditions are different, write to the author +to ask for permission. For software which is copyrighted by the Free +Software Foundation, write to the Free Software Foundation; we sometimes +make exceptions for this. Our decision will be guided by the two goals +of preserving the free status of all derivatives of our free software and +of promoting the sharing and reuse of software generally. + + NO WARRANTY + + 11. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY +FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN +OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES +PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED +OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS +TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE +PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, +REPAIR OR CORRECTION. + + 12. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING +WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR +REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, +INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING +OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED +TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY +YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER +PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE +POSSIBILITY OF SUCH DAMAGES. + + END OF TERMS AND CONDITIONS + + How to Apply These Terms to Your New Programs + + If you develop a new program, and you want it to be of the greatest +possible use to the public, the best way to achieve this is to make it +free software which everyone can redistribute and change under these terms. + + To do so, attach the following notices to the program. It is safest +to attach them to the start of each source file to most effectively +convey the exclusion of warranty; and each file should have at least +the "copyright" line and a pointer to where the full notice is found. + + <one line to give the program's name and a brief idea of what it does.> + Copyright (C) <year> <name of author> + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; either version 2 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License along + with this program; if not, write to the Free Software Foundation, Inc., + 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +Also add information on how to contact you by electronic and paper mail. + +If the program is interactive, make it output a short notice like this +when it starts in an interactive mode: + + Gnomovision version 69, Copyright (C) year name of author + Gnomovision comes with ABSOLUTELY NO WARRANTY; for details type `show w'. + This is free software, and you are welcome to redistribute it + under certain conditions; type `show c' for details. + +The hypothetical commands `show w' and `show c' should show the appropriate +parts of the General Public License. Of course, the commands you use may +be called something other than `show w' and `show c'; they could even be +mouse-clicks or menu items--whatever suits your program. + +You should also get your employer (if you work as a programmer) or your +school, if any, to sign a "copyright disclaimer" for the program, if +necessary. Here is a sample; alter the names: + + Yoyodyne, Inc., hereby disclaims all copyright interest in the program + `Gnomovision' (which makes passes at compilers) written by James Hacker. + + <signature of Ty Coon>, 1 April 1989 + Ty Coon, President of Vice + +This General Public License does not permit incorporating your program into +proprietary programs. If your program is a subroutine library, you may +consider it more useful to permit linking proprietary applications with the +library. If this is what you want to do, use the GNU Lesser General +Public License instead of this License.
\ No newline at end of file diff --git a/README.md b/README.md new file mode 100644 index 0000000..e08af74 --- /dev/null +++ b/README.md @@ -0,0 +1,16 @@ +# Icinga IDO Reports + +[](https://php.net/) + +[](https://github.com/Icinga/icingaweb2-module-idoreports) + + + +Icinga IDO Reports provides host and service availability reports for Icinga based on the monitoring +database (IDO). + + + +## Documentation + +* [Installation](doc/02-Installation.md) diff --git a/doc/02-Installation.md b/doc/02-Installation.md new file mode 100644 index 0000000..532d0ff --- /dev/null +++ b/doc/02-Installation.md @@ -0,0 +1,71 @@ +# Installation + +## Requirements + +* PHP (>= 7.0) +* Icinga Web 2 (>= 2.9) +* Icinga Web 2 libraries: + * [Icinga PHP Library (ipl)](https://github.com/Icinga/icinga-php-library) (>= 0.8) +* Icinga Web 2 modules: + * [reporting](https://github.com/Icinga/icingaweb2-module-reporting) (>= 0.9) +* MySQL, MariaDB or PostgreSQL + +## Database Setup + +### MySQL / MariaDB + +#### Grant Required Privileges + +Skip this step if you used the database configuration wizard during the Icinga 2 installation. + +Please proceed only if you did the setup manually as described here: +https://icinga.com/docs/icinga2/latest/doc/02-getting-started/#setting-up-the-mysql-database + +The import of the SQL functions will fail due to insufficient privileges. +The required privileges are `CREATE, CREATE ROUTINE, ALTER ROUTINE, EXECUTE`. + +The following example assumes that your MySQL database is hosted on **localhost** +and your Icinga database and user is named **icinga2**: + +``` +GRANT CREATE, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON icinga2.* TO 'icinga2'@'localhost'; +``` + +Please adapt the host, database and username to your environment. + +#### Import Database Files + +The module ships with database functions for calculating the host and service availability in `etc/schema/mysql`. +Please import those files into your Icinga database. + +The following example assumes that your Icinga database and user is named **icinga2**: + +``` +mysql -p -u icinga2 icinga2 < schema/mysql/slaperiods.sql +mysql -p -u icinga2 icinga2 < schema/mysql/get_sla_ok_percent.sql +``` + +Please adapt the database and username to your environment. + +### PostgreSQL + +#### Import Database Files + +The module ships with database functions for calculating the host and service availability in `etc/schema/postgresql`. +Please import those files into your Icinga database. + +The following example assumes that your Icinga database and user is named **icinga2**: + +``` +psql -U icinga2 icinga2 < schema/postgresql/slaperiods.sql +psql -U icinga2 icinga2 < schema/postgresql/get_sla_ok_percent.sql +``` + +Please adapt the database and username to your environment. + +## Installation + +Install it [like any other module](https://icinga.com/docs/icinga-web-2/latest/doc/08-Modules/#installation). +Use `idoreports` as name. + +This concludes the installation. You should now be able to create host and service availability reports. diff --git a/doc/res/host-sla-report.png b/doc/res/host-sla-report.png Binary files differnew file mode 100644 index 0000000..6b3b98b --- /dev/null +++ b/doc/res/host-sla-report.png diff --git a/library/Idoreports/HostSlaReport.php b/library/Idoreports/HostSlaReport.php new file mode 100644 index 0000000..70cbc1b --- /dev/null +++ b/library/Idoreports/HostSlaReport.php @@ -0,0 +1,104 @@ +<?php + +// Icinga IDO Reports | (c) 2018 Icinga GmbH | GPLv2 + +namespace Icinga\Module\Idoreports; + +use Icinga\Module\Reporting\ReportData; +use Icinga\Module\Reporting\ReportRow; +use Icinga\Module\Reporting\Timerange; +use ipl\Html\Form; + +class HostSlaReport extends IdoReport +{ + public function getName() + { + return 'Host SLA'; + } + + public function initConfigForm(Form $form) + { + $form->addElement('text', 'filter', [ + 'label' => 'Filter' + ]); + + $form->addElement('select', 'breakdown', [ + 'label' => 'Breakdown', + 'options' => [ + 'none' => 'None', + 'day' => 'Day', + 'week' => 'Week', + 'month' => 'Month' + ] + ]); + + $form->addElement('number', 'threshold', [ + 'label' => 'Threshold', + 'placeholder' => '99.5', + 'step' => '0.01', + 'min' => '1', + 'max' => '100' + ]); + } + + protected function fetchSla(Timerange $timerange, array $config = null) + { + $rd = new ReportData(); + + if (isset($config['breakdown']) && $config['breakdown'] !== 'none') { + switch ($config['breakdown']) { + case 'day': + $interval = new \DateInterval('P1D'); + $format = 'Y-m-d'; + $boundary = false; + break; + case 'week': + $interval = new \DateInterval('P1W'); + $format = 'Y-\WW'; + $boundary = 'monday next week midnight'; + break; + case 'month': + $interval = new \DateInterval('P1M'); + $format = 'Y-m'; + $boundary = 'first day of next month midnight'; + break; + } + + $rd + ->setDimensions(['Hostname', ucfirst($config['breakdown'])]) + ->setValues(['SLA in %']); + + $rows = []; + + foreach ($this->yieldTimerange($timerange, $interval, $boundary) as list($start, $end)) { + foreach ($this->fetchHostSla(new Timerange($start, $end), $config) as $row) { + if ($row->sla === null) { + continue; + } + + $rows[] = (new ReportRow()) + ->setDimensions([$row->host_display_name, $start->format($format)]) + ->setValues([(float) $row->sla]); + } + } + + $rd->setRows($rows); + } else { + $rd + ->setDimensions(['Hostname']) + ->setValues(['SLA in %']); + + $rows = []; + + foreach ($this->fetchHostSla($timerange, $config) as $row) { + $rows[] = (new ReportRow()) + ->setDimensions([$row->host_display_name]) + ->setValues([(float) $row->sla]); + } + + $rd->setRows($rows); + } + + return $rd; + } +} diff --git a/library/Idoreports/IdoReport.php b/library/Idoreports/IdoReport.php new file mode 100644 index 0000000..36e2d17 --- /dev/null +++ b/library/Idoreports/IdoReport.php @@ -0,0 +1,257 @@ +<?php + +// Icinga IDO Reports | (c) 2018 Icinga GmbH | GPLv2 + +namespace Icinga\Module\Idoreports; + +use Icinga\Application\Icinga; +use Icinga\Data\Filter\Filter; +use Icinga\Data\Filterable; +use Icinga\Exception\ConfigurationError; +use Icinga\Exception\QueryException; +use Icinga\Module\Monitoring\Backend\MonitoringBackend; +use Icinga\Module\Reporting\Hook\ReportHook; +use Icinga\Module\Reporting\ReportData; +use Icinga\Module\Reporting\Timerange; +use ipl\Html\Html; + +/** + * @TODO(el): Respect restrictions from monitoring module + */ +abstract class IdoReport extends ReportHook +{ + public function getData(Timerange $timerange, array $config = null) + { + return $this->fetchSla($timerange, $config); + } + + public function getHtml(Timerange $timerange, array $config = null) + { + $data = $this->fetchSla($timerange, $config); + + if (! count($data)) { + return Html::tag('p', 'No data found.'); + } + + $threshold = isset($config['threshold']) ? (float) $config['threshold'] : 99.5; + + $tableHeaderCells = []; + + foreach ($data->getDimensions() as $dimension) { + $tableHeaderCells[] = Html::tag('th', null, $dimension); + } + + foreach ($data->getValues() as $value) { + $tableHeaderCells[] = Html::tag('th', null, $value); + } + + $tableRows = []; + + foreach ($data->getRows() as $row) { + $cells = []; + + foreach ($row->getDimensions() as $dimension) { + $cells[] = Html::tag('td', null, $dimension); + } + + // We only have one metric + $sla = $row->getValues()[0]; + + if ($sla < $threshold) { + $slaClass = 'nok'; + } else { + $slaClass = 'ok'; + } + + $cells[] = Html::tag('td', ['class' => "sla-column $slaClass"], \round($sla, 2)); + + $tableRows[] = Html::tag('tr', null, $cells); + } + + // We only have one average + $average = $data->getAverages()[0]; + + if ($average < $threshold) { + $slaClass = 'nok'; + } else { + $slaClass = 'ok'; + } + + $tableRows[] = Html::tag('tr', null, [ + Html::tag('td', ['colspan' => count($data->getDimensions())], 'Total'), + Html::tag('td', ['class' => "sla-column $slaClass"], \round($average, 2)) + ]); + + $table = Html::tag( + 'table', + ['class' => 'common-table sla-table'], + [ + Html::tag( + 'thead', + null, + Html::tag( + 'tr', + null, + $tableHeaderCells + ) + ), + Html::tag('tbody', null, $tableRows) + ] + ); + + return $table; + } + + /** + * @param Timerange $timerange + * @param array $config + * + * @return ReportData + */ + abstract protected function fetchSla(Timerange $timerange, array $config = null); + + protected function applyFilterAndRestrictions($filter, Filterable $filterable) + { + $filters = Filter::matchAll(); + $filters->setAllowedFilterColumns(array( + 'host_name', + 'hostgroup_name', + 'instance_name', + 'service_description', + 'servicegroup_name', + function ($c) { + return \preg_match('/^_(?:host|service)_/i', $c); + } + )); + + try { + if ($filter !== '*') { + $filters->addFilter(Filter::fromQueryString($filter)); + } + + foreach ($this->yieldMonitoringRestrictions() as $filter) { + $filters->addFilter($filter); + } + } catch (QueryException $e) { + throw new ConfigurationError( + 'Cannot apply filter. You can only use the following columns: %s', + implode(', ', array( + 'instance_name', + 'host_name', + 'hostgroup_name', + 'service_description', + 'servicegroup_name', + '_(host|service)_<customvar-name>' + )), + $e + ); + } + + $filterable->applyFilter($filters); + } + + protected function getBackend() + { + MonitoringBackend::clearInstances(); + + return MonitoringBackend::instance(); + } + + protected function getRestrictions($name) + { + $app = Icinga::app(); + if (! $app->isCli()) { + $result = $app->getRequest()->getUser()->getRestrictions($name); + } else { + $result = []; + } + + return $result; + } + + protected function fetchHostSla(Timerange $timerange, array $config) + { + $sla = $this->getBackend()->select()->from('hoststatus', ['host_display_name'])->order('host_display_name'); + + $this->applyFilterAndRestrictions($config['filter'] ?: '*', $sla); + + /** @var \Zend_Db_Select $select */ + $select = $sla->getQuery()->getSelectQuery(); + + $columns = $sla->getQuery()->getColumns(); + $columns['sla'] = new \Zend_Db_Expr(\sprintf( + "idoreports_get_sla_ok_percent(%s, '%s', '%s', NULL)", + 'ho.object_id', + $timerange->getStart()->format('Y-m-d H:i:s'), + $timerange->getEnd()->format('Y-m-d H:i:s') + )); + + $select->columns($columns); + + return $this->getBackend()->getResource()->getDbAdapter()->query($select); + } + + protected function fetchServiceSla(Timerange $timerange, array $config) + { + $sla = $this + ->getBackend() + ->select() + ->from('servicestatus', ['host_display_name', 'service_display_name']) + ->order('host_display_name'); + + $this->applyFilterAndRestrictions($config['filter'] ?: '*', $sla); + + /** @var \Zend_Db_Select $select */ + $select = $sla->getQuery()->getSelectQuery(); + + $columns = $sla->getQuery()->getColumns(); + $columns['sla'] = new \Zend_Db_Expr(\sprintf( + "idoreports_get_sla_ok_percent(%s, '%s', '%s', NULL)", + 'so.object_id', + $timerange->getStart()->format('Y-m-d H:i:s'), + $timerange->getEnd()->format('Y-m-d H:i:s') + )); + + $select->columns($columns); + + return $this->getBackend()->getResource()->getDbAdapter()->query($select); + } + + protected function yieldMonitoringRestrictions() + { + foreach ($this->getRestrictions('monitoring/filter/objects') as $restriction) { + if ($restriction !== '*') { + yield Filter::fromQueryString($restriction); + } + } + } + + protected function yieldTimerange(Timerange $timerange, \DateInterval $interval, $boundary) + { + $start = clone $timerange->getStart(); + $end = clone $timerange->getEnd(); + $oneSecond = new \DateInterval('PT1S'); + + if ($boundary !== false) { + $intermediate = (clone $start)->modify($boundary); + if ($intermediate < $end) { + yield [clone $start, $intermediate->sub($oneSecond)]; + + $start->modify($boundary); + } + } + + $period = new \DatePeriod($start, $interval, $end, \DatePeriod::EXCLUDE_START_DATE); + + foreach ($period as $date) { + /** @var \DateTime $date */ + + yield [$start, (clone $date)->sub($oneSecond)]; + + $start = $date; + } + + // @TODO(el): Should we add if ($start < $end) here to protect us from invalid ranges? + yield [$start, $end]; + } +} diff --git a/library/Idoreports/ServiceSlaReport.php b/library/Idoreports/ServiceSlaReport.php new file mode 100644 index 0000000..7127736 --- /dev/null +++ b/library/Idoreports/ServiceSlaReport.php @@ -0,0 +1,104 @@ +<?php + +// Icinga IDO Reports | (c) 2018 Icinga GmbH | GPLv2 + +namespace Icinga\Module\Idoreports; + +use Icinga\Module\Reporting\ReportData; +use Icinga\Module\Reporting\ReportRow; +use Icinga\Module\Reporting\Timerange; +use ipl\Html\Form; + +class ServiceSlaReport extends IdoReport +{ + public function getName() + { + return 'Service SLA'; + } + + public function initConfigForm(Form $form) + { + $form->addElement('text', 'filter', [ + 'label' => 'Filter' + ]); + + $form->addElement('select', 'breakdown', [ + 'label' => 'Breakdown', + 'options' => [ + 'none' => 'None', + 'day' => 'Day', + 'week' => 'Week', + 'month' => 'Month' + ] + ]); + + $form->addElement('number', 'threshold', [ + 'label' => 'Threshold', + 'placeholder' => '99.5', + 'step' => '0.01', + 'min' => '1', + 'max' => '100' + ]); + } + + protected function fetchSla(Timerange $timerange, array $config = null) + { + $rd = new ReportData(); + + if (isset($config['breakdown']) && $config['breakdown'] !== 'none') { + switch ($config['breakdown']) { + case 'day': + $interval = new \DateInterval('P1D'); + $format = 'Y-m-d'; + $boundary = false; + break; + case 'week': + $interval = new \DateInterval('P1W'); + $format = 'Y-\WW'; + $boundary = 'monday next week midnight'; + break; + case 'month': + $interval = new \DateInterval('P1M'); + $format = 'Y-m'; + $boundary = 'first day of next month midnight'; + break; + } + + $rd + ->setDimensions(['Hostname', 'Service Name', ucfirst($config['breakdown'])]) + ->setValues(['SLA in %']); + + $rows = []; + + foreach ($this->yieldTimerange($timerange, $interval, $boundary) as list($start, $end)) { + foreach ($this->fetchServiceSla(new Timerange($start, $end), $config) as $row) { + if ($row->sla === null) { + continue; + } + + $rows[] = (new ReportRow()) + ->setDimensions([$row->host_display_name, $row->service_display_name, $start->format($format)]) + ->setValues([(float) $row->sla]); + } + } + + $rd->setRows($rows); + } else { + $rd + ->setDimensions(['Hostname', 'Service Name']) + ->setValues(['SLA in %']); + + $rows = []; + + foreach ($this->fetchServiceSla($timerange, $config) as $row) { + $rows[] = (new ReportRow()) + ->setDimensions([$row->host_display_name, $row->service_display_name]) + ->setValues([(float) $row->sla]); + } + + $rd->setRows($rows); + } + + return $rd; + } +} diff --git a/module.info b/module.info new file mode 100644 index 0000000..b26afb9 --- /dev/null +++ b/module.info @@ -0,0 +1,6 @@ +Module: IDO Reports +Version: 0.10.1 +Requires: + Libraries: icinga-php-library (>=0.8.0) + Modules: monitoring (>=2.9.0), reporting (>=0.9.0) +Description: Reports for IDO @@ -0,0 +1,13 @@ +<?php + +// Icinga IDO Reports | (c) 2018 Icinga GmbH | GPLv2 + +namespace Icinga\Module\Idoreports { + + use Icinga\Application\Icinga; + + /** @var \Icinga\Application\Modules\Module $this */ + + $this->provideHook('reporting/Report', '\\Icinga\\Module\\Idoreports\\HostSlaReport'); + $this->provideHook('reporting/Report', '\\Icinga\\Module\\Idoreports\\ServiceSlaReport'); +} diff --git a/schema/mysql/get_sla_ok_percent.sql b/schema/mysql/get_sla_ok_percent.sql new file mode 100644 index 0000000..a546326 --- /dev/null +++ b/schema/mysql/get_sla_ok_percent.sql @@ -0,0 +1,333 @@ +DROP FUNCTION IF EXISTS idoreports_get_sla_ok_percent; + +DELIMITER // + +CREATE FUNCTION idoreports_get_sla_ok_percent ( + id BIGINT UNSIGNED, + start DATETIME, + end DATETIME, + sla_timeperiod_object_id BIGINT UNSIGNED +) RETURNS DECIMAL(7, 4) + READS SQL DATA +BEGIN + DECLARE result DECIMAL(7, 4); + + -- We use user-defined @-vars, this allows for easier sub-queries testing + SET + -- First, set our parameters: + @id = id, + @start = start, + @end = end, + @sla_timeperiod_object_id = sla_timeperiod_object_id, + + -- Then fetch our object type id: + @type_id = (SELECT objecttype_id FROM icinga_objects WHERE object_id = id), + + -- Next, reset inline vars: + @next_type = NULL, + @last_ts = NULL, + @last_type = NULL, + @add_duration = 0, + @last_state = NULL, + @cnt_tp = null, + @cnt_dt = NULL, + + -- And finally reset all eventual result variables: + @sla_ok_seconds = NULL, + @sla_ok_percent = NULL, + @problem_seconds = NULL, + @problem_percent = NULL, + @problem_in_downtime_seconds = NULL, + @problem_in_downtime_percent = NULL, + @total_seconds = NULL + ; + + + IF @type_id NOT IN (1, 2) THEN + RETURN NULL; + END IF; + +SELECT CASE WHEN @last_state IS NULL THEN NULL ELSE CAST(sla_ok_seconds / total_time * 100 AS DECIMAL(7, 4)) END INTO result FROM ( +SELECT + @sla_ok_seconds := SUM( + CASE + WHEN in_downtime + out_of_slatime > 0 THEN 1 + WHEN is_problem THEN 0 + ELSE 1 + END * duration + ) AS sla_ok_seconds, + @sla_ok_percent := CAST(100 * SUM( + CASE + WHEN in_downtime + out_of_slatime > 0 THEN 1 + WHEN is_problem THEN 0 + ELSE 1 + END * duration / (UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start)) + ) AS DECIMAL(7, 4)) AS sla_ok_percent, + @problem_seconds := SUM(is_problem * duration) AS problem_seconds, + @problem_percent := CAST( + SUM(is_problem * duration) / SUM(duration) * 100 AS DECIMAL(7, 4) + ) AS problem_percent, + @problem_in_downtime_seconds := SUM( + is_problem * in_downtime * duration + ) AS problem_in_downtime_seconds, + @problem_in_downtime_percent := CAST(100 * SUM( + is_problem * in_downtime * duration + / (UNIX_TIMESTAMP(@end) - UNIX_TIMESTAMP(@start)) + ) AS DECIMAL(7, 4)) AS problem_in_downtime_percent, + @total_seconds := SUM(duration) AS total_time +FROM ( + -- ----------------------------------------------------------------- -- +-- SLA relevant events, re-modelled with duration -- +-- -- +-- This declares and clears the following variables: -- +-- * @last_state -- +-- * @add_duration -- +-- * @next_type -- +-- * @cnt_dt -- +-- * @cnt_tp -- +-- * @type_id -- +-- * @next_type -- +-- * @start (used) -- +-- -- +-- Columns: -- +-- *** -- +-- ----------------------------------------------------------------- -- + +SELECT + state_time, + UNIX_TIMESTAMP(state_time), + CAST(COALESCE(@last_ts, UNIX_TIMESTAMP(@start)) AS UNSIGNED), + CAST(UNIX_TIMESTAMP(state_time) + - CAST(COALESCE(@last_ts, UNIX_TIMESTAMP(@start)) AS UNSIGNED) + + CAST(COALESCE(@add_duration, 0) AS UNSIGNED) AS UNSIGNED) AS duration, + + -- @add_duration is used as long as we haven't seen a state + @add_duration AS add_duration, + + @next_type AS current_type, + @next_type := type AS next_type, + + -- current_state is the state from the last state change until now: + @last_state AS current_state, + + CASE WHEN @last_state IS NULL THEN NULL ELSE + CASE WHEN @type_id = 1 + THEN CASE WHEN @last_state > 0 THEN 1 ELSE 0 END + ELSE CASE WHEN @last_state > 1 THEN 1 ELSE 0 END + END + END AS is_problem, + + CASE WHEN COALESCE(@cnt_dt, 0) > 0 THEN 1 ELSE 0 END AS in_downtime, + CASE WHEN COALESCE(@cnt_tp, 0) > 0 THEN 1 ELSE 0 END AS out_of_slatime, + + COALESCE(@cnt_dt, 0) AS dt_depth, + COALESCE(@cnt_tp, 0) AS tp_depth, + + CASE type + WHEN 'dt_start' THEN @cnt_dt := COALESCE(@cnt_dt, 0) + 1 + WHEN 'dt_end' THEN @cnt_dt := GREATEST(@cnt_dt - 1, 0) + ELSE COALESCE(@cnt_dt, 0) + END AS next_dt_depth, + + CASE type + WHEN 'sla_end' THEN @cnt_tp := COALESCE(@cnt_tp, 0) + 1 + WHEN 'sla_start' THEN @cnt_tp := GREATEST(@cnt_tp - 1, 0) + ELSE COALESCE(@cnt_tp, 0) + END AS next_tp_depth, + + -- next_state is the state from now on, so it replaces @last_state: + CASE + -- Set our next @last_state if we have a hard state change + WHEN type IN ('hard_state', 'former_state', 'current_state') THEN @last_state := state + -- ...or if there is a soft_state and no @last_state has been seen before + WHEN type = 'soft_state' THEN + -- If we don't have a @last_state... + CASE WHEN @last_state IS NULL + -- ...use and set our own last_hard_state (last_state is the inner query alias)... + THEN @last_state := last_state + -- ...and return @last_state otherwise, as soft states shall have no + -- impact on availability + ELSE @last_state END + + WHEN type IN ('dt_start', 'sla_end') THEN @last_state + WHEN type IN ('dt_end', 'sla_start') THEN @last_state + END AS next_state, + + -- Our start_time is either the last end_time or @start... + @last_ts AS start_time, + + -- ...end when setting the new end_time we remember it in @last_ts: + CASE + WHEN type = 'fake_end' THEN state_time + ELSE @last_ts := UNIX_TIMESTAMP(state_time) + END AS end_time + +FROM ( +-- ----------------------------------------------------------------- -- +-- SLA relevant events -- +-- -- +-- Variables: -- +-- * @id The IDO object_id -- +-- * @start Start of the chosen time period. Currently DATE, should -- +-- be UNIX_TIMESTAMP -- +-- * @end Related end of the chosen time period -- +-- * @sla_timeperiod_object_id Time period object ID in case SLA -- +-- times should be respected -- +-- -- +-- Columns: -- +-- state_time, type, state, last_state -- +-- ----------------------------------------------------------------- -- + +-- START fetching statehistory events +SELECT + state_time, + CASE state_type WHEN 1 THEN 'hard_state' ELSE 'soft_state' END AS type, + state, + -- Workaround for a nasty Icinga issue. In case a hard state is reached + -- before max_check_attempts, the last_hard_state value is wrong. As of + -- this we are stepping through all single events, even soft ones. Of + -- course soft states do not have an influence on the availability: + CASE state_type WHEN 1 THEN last_state ELSE last_hard_state END AS last_state +FROM icinga_statehistory +WHERE object_id = @id + AND state_time >= @start + AND state_time <= @end +-- STOP fetching statehistory events + +-- START fetching last state BEFORE the given interval as an event +UNION SELECT * FROM ( + SELECT + @start AS state_time, + 'former_state' AS type, + CASE state_type WHEN 1 THEN state ELSE last_hard_state END AS state, + CASE state_type WHEN 1 THEN last_state ELSE last_hard_state END AS last_state + FROM icinga_statehistory h + WHERE object_id = @id + AND state_time < @start + ORDER BY h.state_time DESC + LIMIT 1 +) formerstate +-- END fetching last state BEFORE the given interval as an event + +-- START ADDING a fake end +UNION SELECT + @end AS state_time, + 'fake_end' AS type, + NULL AS state, + NULL AS last_state +FROM DUAL +-- END ADDING a fake end + +-- START fetching current host state as an event +-- TODO: This is not 100% correct. state should be fine, last_state sometimes isn't. +UNION SELECT + GREATEST( + @start, + CASE state_type WHEN 1 THEN last_state_change ELSE last_hard_state_change END + ) AS state_time, + 'current_state' AS type, + CASE state_type WHEN 1 THEN current_state ELSE last_hard_state END AS state, + last_hard_state AS last_state +FROM icinga_hoststatus +WHERE CASE state_type WHEN 1 THEN last_state_change ELSE last_hard_state_change END < @start + AND host_object_id = @id + AND CASE state_type WHEN 1 THEN last_state_change ELSE last_hard_state_change END <= @end + AND status_update_time > @start +-- END fetching current host state as an event + +-- START fetching current service state as an event +-- ++ , only if older than @start +UNION SELECT + GREATEST( + @start, + CASE state_type WHEN 1 THEN last_state_change ELSE last_hard_state_change END + ) AS state_time, + 'current_state' AS type, + CASE state_type WHEN 1 THEN current_state ELSE last_hard_state END AS state, + last_hard_state AS last_state +FROM icinga_servicestatus +WHERE CASE state_type WHEN 1 THEN last_state_change ELSE last_hard_state_change END < @start + AND service_object_id = @id + -- AND CASE state_type WHEN 1 THEN last_state_change ELSE last_hard_state_change END <= @end + AND status_update_time > @start +-- END fetching current service state as an event + +-- START adding add all related downtime start times +-- TODO: Handling downtimes still being active would be nice. +-- But pay attention: they could be completely outdated +UNION SELECT + GREATEST(actual_start_time, @start) AS state_time, + 'dt_start' AS type, + NULL AS state, + NULL AS last_state +FROM icinga_downtimehistory +WHERE object_id = @id + AND actual_start_time < @end + AND actual_end_time > @start +-- STOP adding add all related downtime start times + +-- START adding add all related downtime end times +UNION SELECT + LEAST(actual_end_time, @end) AS state_time, + 'dt_end' AS type, + NULL AS state, + NULL AS last_state +FROM icinga_downtimehistory +WHERE object_id = @id + AND actual_start_time < @end + AND actual_end_time > @start +-- STOP adding add all related downtime end times + +-- START fetching SLA time period start times --- +UNION ALL +SELECT + start_time AS state_time, + 'sla_start' AS type, + NULL AS state, + NULL AS last_state +FROM icinga_outofsla_periods +WHERE timeperiod_object_id = @sla_timeperiod_object_id + AND start_time >= @start + AND start_time <= @end +-- STOP fetching SLA time period start times --- + +-- START fetching SLA time period end times --- +UNION ALL SELECT + end_time AS state_time, + 'sla_end' AS type, + NULL AS state, + NULL AS last_state + FROM icinga_outofsla_periods +WHERE timeperiod_object_id = @sla_timeperiod_object_id + AND end_time >= @start + AND end_time <= @end +-- STOP fetching SLA time period end times --- + +ORDER BY state_time ASC, + CASE type + -- Order is important. current_state and former_state + -- are potential candidates for the initial state of the chosen period. + -- the last one wins, and preferably we have a state change before the + -- chosen period. Otherwise we assume that the first state change after + -- that period knows about the former state. Last fallback is the + WHEN 'current_state' THEN 0 + WHEN 'former_state' THEN 2 + WHEN 'soft_state' THEN 3 + WHEN 'hard_state' THEN 4 + WHEN 'sla_end' THEN 5 + WHEN 'sla_start' THEN 6 + WHEN 'dt_start' THEN 7 + WHEN 'dt_end' THEN 8 + ELSE 9 + END ASC + +) events + +) intervals + +) sladetails; + + RETURN result; +END// + +DELIMITER ; diff --git a/schema/mysql/slaperiods.sql b/schema/mysql/slaperiods.sql new file mode 100644 index 0000000..32bc55e --- /dev/null +++ b/schema/mysql/slaperiods.sql @@ -0,0 +1,17 @@ +DROP TABLE IF EXISTS icinga_sla_periods; +CREATE TABLE icinga_sla_periods ( + timeperiod_object_id BIGINT(20) UNSIGNED NOT NULL, + start_time timestamp NOT NULL, + end_time timestamp NULL DEFAULT NULL, + PRIMARY KEY tp_start (timeperiod_object_id, start_time), + UNIQUE KEY tp_end (timeperiod_object_id, end_time) +) ENGINE InnoDB; + +DROP TABLE IF EXISTS icinga_outofsla_periods; +CREATE TABLE icinga_outofsla_periods ( + timeperiod_object_id BIGINT(20) UNSIGNED NOT NULL, + start_time timestamp NOT NULL, + end_time timestamp NULL DEFAULT NULL, + PRIMARY KEY tp_start (timeperiod_object_id, start_time), + UNIQUE KEY tp_end (timeperiod_object_id, end_time) +) ENGINE InnoDB; diff --git a/schema/postgresql/get_sla_ok_percent.sql b/schema/postgresql/get_sla_ok_percent.sql new file mode 100644 index 0000000..03eee16 --- /dev/null +++ b/schema/postgresql/get_sla_ok_percent.sql @@ -0,0 +1,235 @@ +DROP FUNCTION IF EXISTS idoreports_get_sla_ok_percent(BIGINT, TIMESTAMPTZ, TIMESTAMPTZ, INT); + +CREATE OR REPLACE FUNCTION idoreports_get_sla_ok_percent( + id BIGINT, + starttime TIMESTAMP WITHOUT TIME ZONE, + endtime TIMESTAMP WITHOUT TIME ZONE, + sla_id INTEGER DEFAULT NULL +) RETURNS float + LANGUAGE SQL +AS +$$ + +WITH + crit AS ( + SELECT + CASE objecttype_id + WHEN 1 THEN 0 + WHEN 2 THEN 1 + END AS value + FROM + icinga_objects + WHERE + object_id = id + ), + before AS ( + -- low border, last event before the range we are looking for: + SELECT + down, + state_time_ AS state_time, + state + FROM + ( + ( + SELECT + 1 AS prio, + state > crit.value AS down, + GREATEST(state_time, starttime) AS state_time_, + state + FROM + icinga_statehistory, + crit + WHERE + object_id = id + AND state_time < starttime + AND state_type = 1 + ORDER BY + state_time DESC + LIMIT 1 + ) + UNION ALL + ( + SELECT + 2 AS prio, + state > crit.value AS down, + GREATEST(state_time, starttime) AS state_time_, + state + FROM + icinga_statehistory, + crit + WHERE + object_id = id + AND state_time < starttime + ORDER BY + state_time DESC + LIMIT 1 + ) + ) ranked + ORDER BY + prio + LIMIT 1 + ), + all_hard_events AS ( + -- the actual range we're looking for: + SELECT + state > crit.value AS down, + state_time, + state + FROM + icinga_statehistory, + crit + WHERE + object_id = id + AND state_time >= starttime + AND state_time <= endtime + AND state_type = 1 + ), + after AS ( + -- the "younger" of the current host/service state and the first recorded event + ( + SELECT + state > crit_value AS down, + LEAST(state_time, endtime) AS state_time, + state + + FROM + ( + ( + SELECT + state_time, + state, + crit.value AS crit_value + FROM + icinga_statehistory, + crit + WHERE + object_id = id + AND state_time > endtime + AND state_type = 1 + ORDER BY + state_time + LIMIT 1 + ) + UNION ALL + ( + SELECT + status_update_time, + current_state, + crit.value AS crit_value + FROM + icinga_hoststatus, + crit + WHERE + host_object_id = id + AND state_type = 1 + ) + UNION ALL + ( + SELECT + status_update_time, + current_state, + crit.value AS crit_value + FROM + icinga_servicestatus, + crit + WHERE + service_object_id = id + AND state_type = 1 + ) + ) AS after_searched_period + ORDER BY + state_time + LIMIT 1 + ) + ), + allevents AS ( + TABLE before + UNION ALL + TABLE all_hard_events + UNION ALL + TABLE after + ), + downtimes AS ( + ( + SELECT + tsrange(actual_start_time, actual_end_time) AS downtime + FROM + icinga_downtimehistory + WHERE + object_id = id + ) + UNION ALL + ( + SELECT + tsrange(start_time, end_time) AS downtime + FROM + icinga_outofsla_periods + WHERE + timeperiod_object_id = sla_id + ) + ), + enriched AS ( + SELECT + down, + tsrange(state_time, COALESCE(lead(state_time) OVER w, endtime), '(]') AS timeframe + --,lead(state_time) OVER w - state_time AS dauer + FROM + ( + SELECT + state > crit.value AS down, + lead(state, 1, state) OVER w > crit.value AS next_down, + lag(state, 1, state) OVER w > crit.value AS prev_down, + state_time, + state + FROM + allevents, + crit WINDOW w AS (ORDER BY state_time) + ) alle WINDOW w AS (ORDER BY state_time) + ), + relevant AS ( + SELECT + down, + timeframe * tsrange(starttime, endtime, '(]') AS timeframe + FROM + enriched + WHERE + timeframe && tsrange(starttime, endtime, '(]') + ), + covered AS ( + SELECT + upper(covered_by_downtime) - lower(covered_by_downtime) AS dauer + FROM ( + SELECT + timeframe * downtime AS covered_by_downtime + FROM + relevant + LEFT JOIN downtimes ON timeframe && downtime + WHERE + down + ) AS foo + ), + relevant_down AS ( + SELECT *, + upper(timeframe) - lower(timeframe) AS dauer + FROM + relevant + WHERE + down + ), + final_result AS ( + SELECT + sum(dauer) - ( + SELECT sum(dauer) FROM covered + ) AS total_downtime, + endtime - starttime AS considered, + COALESCE(extract('epoch' from sum(dauer)), 0) AS down_secs, + extract('epoch' from endtime - starttime) AS considered_secs + FROM + relevant_down + ) + +SELECT + 100.0 - down_secs / considered_secs * 100.0 AS availability +FROM + final_result ; +$$; diff --git a/schema/postgresql/slaperiods.sql b/schema/postgresql/slaperiods.sql new file mode 100644 index 0000000..013c4f7 --- /dev/null +++ b/schema/postgresql/slaperiods.sql @@ -0,0 +1,5 @@ +CREATE TABLE icinga_outofsla_periods ( + timeperiod_object_id numeric NOT NULL, + start_time timestamp without time zone NOT NULL, + end_time timestamp without time zone NOT NULL +); diff --git a/schema/postgresql/t/00-create-db.sql b/schema/postgresql/t/00-create-db.sql new file mode 100644 index 0000000..5e0e78a --- /dev/null +++ b/schema/postgresql/t/00-create-db.sql @@ -0,0 +1,4 @@ +--create database icinga2; +CREATE EXTENSION IF NOT EXISTS pgtap; +SELECT plan(1); +SELECT is(count(*) , 1::bigint,'Extension pg_tap installed') FROM pg_extension WHERE extname = 'pgtap'; diff --git a/schema/postgresql/t/01-install.t b/schema/postgresql/t/01-install.t new file mode 100644 index 0000000..03ae35d --- /dev/null +++ b/schema/postgresql/t/01-install.t @@ -0,0 +1,2 @@ +SELECT plan(1); +\i ../install_all.psql diff --git a/schema/postgresql/t/01-statehistory.sql b/schema/postgresql/t/01-statehistory.sql new file mode 100644 index 0000000..1a1a5e2 --- /dev/null +++ b/schema/postgresql/t/01-statehistory.sql @@ -0,0 +1,7 @@ +SELECT plan(0); +CREATE TABLE icinga_statehistory ( + state_time timestamp WITHOUT time zone, + object_id numeric DEFAULT '0'::numeric, + state smallint DEFAULT '0'::smallint, + state_type smallint DEFAULT '0'::smallint +); diff --git a/schema/postgresql/t/02-icinga_objects.sql b/schema/postgresql/t/02-icinga_objects.sql new file mode 100644 index 0000000..74d622b --- /dev/null +++ b/schema/postgresql/t/02-icinga_objects.sql @@ -0,0 +1,5 @@ +SELECT plan(0); +CREATE TABLE icinga_objects ( + object_id numeric DEFAULT '0'::numeric, + objecttype_id smallint DEFAULT '1'::smallint +); diff --git a/schema/postgresql/t/02-servicestatus.sql b/schema/postgresql/t/02-servicestatus.sql new file mode 100644 index 0000000..d94bc0c --- /dev/null +++ b/schema/postgresql/t/02-servicestatus.sql @@ -0,0 +1,7 @@ +SELECT plan(0); +CREATE TABLE icinga_servicestatus ( + service_object_id numeric DEFAULT '0'::numeric, + status_update_time timestamp WITHOUT time zone, + current_state smallint DEFAULT '0'::smallint, + state_type smallint DEFAULT '0'::smallint +); diff --git a/schema/postgresql/t/03-hoststatus.sql b/schema/postgresql/t/03-hoststatus.sql new file mode 100644 index 0000000..3f6b4e8 --- /dev/null +++ b/schema/postgresql/t/03-hoststatus.sql @@ -0,0 +1,7 @@ +SELECT plan(0); +CREATE TABLE icinga_hoststatus ( + host_object_id numeric DEFAULT '0'::numeric, + status_update_time timestamp WITHOUT time zone, + current_state smallint DEFAULT '0'::smallint, + state_type smallint DEFAULT '0'::smallint +); diff --git a/schema/postgresql/t/04-icinga_downtimehistory.sql b/schema/postgresql/t/04-icinga_downtimehistory.sql new file mode 100644 index 0000000..7a9b8b6 --- /dev/null +++ b/schema/postgresql/t/04-icinga_downtimehistory.sql @@ -0,0 +1,10 @@ +SELECT plan(0); +CREATE TABLE icinga_downtimehistory ( + object_id numeric DEFAULT '0'::numeric, + entry_time timestamp WITHOUT time zone, + scheduled_start_time timestamp WITHOUT time zone, + scheduled_end_time timestamp WITHOUT time zone, + was_started smallint DEFAULT '0'::smallint, + actual_start_time timestamp WITHOUT time zone, + actual_end_time timestamp WITHOUT time zone +); diff --git a/schema/postgresql/t/04-icinga_outofsla_periods.sql b/schema/postgresql/t/04-icinga_outofsla_periods.sql new file mode 100644 index 0000000..13bd13b --- /dev/null +++ b/schema/postgresql/t/04-icinga_outofsla_periods.sql @@ -0,0 +1,6 @@ +SELECT plan(0); +CREATE TABLE icinga_outofsla_periods ( + timeperiod_object_id numeric NOT NULL, + start_time timestamp WITHOUT time zone NOT NULL, + end_time timestamp WITHOUT time zone NOT NULL +); diff --git a/schema/postgresql/t/05-fill-downtimes.sql b/schema/postgresql/t/05-fill-downtimes.sql new file mode 100644 index 0000000..f0c77f4 --- /dev/null +++ b/schema/postgresql/t/05-fill-downtimes.sql @@ -0,0 +1,5 @@ +-- Objects get a number and a type (1=host, 2=server) +SELECT plan(1); +INSERT INTO icinga_downtimehistory (object_id,actual_start_time,actual_end_time) VALUES (7,'2019-04-15 11:45:00','2019-04-15 11:50:00'); +INSERT INTO icinga_downtimehistory (object_id,actual_start_time,actual_end_time) VALUES (7,'2019-04-15 12:00:00','2019-04-15 12:05:00'); +SELECT is(count(*), 2::bigint, 'icinga_downtimehistory has correct # of rows') FROM icinga_downtimehistory; diff --git a/schema/postgresql/t/05-fill-hoststatus.sql b/schema/postgresql/t/05-fill-hoststatus.sql new file mode 100644 index 0000000..caca8fb --- /dev/null +++ b/schema/postgresql/t/05-fill-hoststatus.sql @@ -0,0 +1,17 @@ +-- Rows are: hostid,timestamp, status (0 up, 1 down), state_type (always use 1) +SELECT plan(1); +COPY icinga_hoststatus FROM STDIN; +1 2019-02-10 12:00:00+01 0 1 +1 2019-03-10 15:00:00+01 1 1 +3 2019-03-10 16:15:00+01 1 1 +4 2020-03-01 00:00:00+01 1 1 +5 2020-04-01 14:00:00+01 0 1 +6 2019-04-01 13:51:17+01 0 1 +7 2019-04-14 11:00:00+01 0 1 +7 2019-04-15 11:00:00+01 1 1 +7 2019-04-15 12:52:00+01 0 1 +7 2019-04-15 12:55:00+01 1 1 +7 2019-04-15 12:57:00+01 0 1 +\. + +SELECT is(count(*), 11::bigint, 'icinga_hoststatus has correct # of rows') FROM icinga_hoststatus; diff --git a/schema/postgresql/t/05-fill-icinga_objects.sql b/schema/postgresql/t/05-fill-icinga_objects.sql new file mode 100644 index 0000000..1b11eef --- /dev/null +++ b/schema/postgresql/t/05-fill-icinga_objects.sql @@ -0,0 +1,10 @@ +-- Objects get a number and a type (1=host, 2=server) +SELECT plan(1); +INSERT INTO icinga_objects VALUES (1,1); +INSERT INTO icinga_objects VALUES (2,2); +INSERT INTO icinga_objects VALUES (3,1); +INSERT INTO icinga_objects VALUES (4,1); +INSERT INTO icinga_objects VALUES (5,1); +INSERT INTO icinga_objects VALUES (6,1); +INSERT INTO icinga_objects VALUES (7,1); +SELECT is(count(*), 7::bigint, 'icinga_objects has correct # of rows') FROM icinga_objects; diff --git a/schema/postgresql/t/05-fill-servicestatus.sql b/schema/postgresql/t/05-fill-servicestatus.sql new file mode 100644 index 0000000..5ae413f --- /dev/null +++ b/schema/postgresql/t/05-fill-servicestatus.sql @@ -0,0 +1,7 @@ +SELECT plan(1); +COPY icinga_servicestatus FROM STDIN; +2 2019-02-10 12:00:00+01 0 1 +2 2019-03-10 15:00:00+01 2 1 +\. +SELECT is(count(*), 2::bigint, 'icinga_servicestatus has 2 rows') FROM icinga_servicestatus; + diff --git a/schema/postgresql/t/05-fill-statehistory.sql b/schema/postgresql/t/05-fill-statehistory.sql new file mode 100644 index 0000000..f66afbd --- /dev/null +++ b/schema/postgresql/t/05-fill-statehistory.sql @@ -0,0 +1,28 @@ +-- Rows are: state_time, object_id, state, state_type +SELECT plan(1); +COPY icinga_statehistory FROM STDIN; +2019-02-01 00:00:00+01 1 0 1 +2019-02-05 11:00:00+01 1 3 0 +2019-02-05 12:00:00+01 1 3 1 +2019-02-05 13:00:00+01 1 0 0 +2019-02-05 14:00:00+01 1 0 1 +2019-03-01 00:00:00+01 1 0 1 +2019-03-05 11:00:00+01 1 3 0 +2019-03-05 12:00:00+01 1 3 1 +2019-02-01 00:00:00+01 2 0 1 +2019-02-05 11:00:00+01 2 3 0 +2019-02-05 12:00:00+01 2 3 1 +2019-02-05 13:00:00+01 2 0 0 +2019-02-05 14:00:00+01 2 0 1 +2019-03-01 00:00:00+01 2 0 1 +2019-03-05 11:00:00+01 2 3 0 +2019-03-05 12:00:00+01 2 3 1 +2019-03-10 16:05:00+01 3 1 0 +2019-03-10 16:10:00+01 3 1 1 +2020-03-01 14:00:00+01 4 0 1 +2020-04-01 00:00:00+01 5 1 1 +2019-03-31 21:50:48+01 6 1 1 +2019-03-27 15:15:42+01 6 0 1 +2019-03-27 01:00:00+01 7 0 1 +\. +SELECT is(count(*), 23::bigint, 'icinga_statehistory has correct # of rows rows') FROM icinga_statehistory; diff --git a/schema/postgresql/t/06-get_sla_ok_percent.sql b/schema/postgresql/t/06-get_sla_ok_percent.sql new file mode 100644 index 0000000..4ce9e39 --- /dev/null +++ b/schema/postgresql/t/06-get_sla_ok_percent.sql @@ -0,0 +1,3 @@ +SELECT plan(1); +\i get_sla_ok_percent.sql +SELECT is(COUNT(*),1::bigint) FROM pg_catalog.pg_proc WHERE proname = 'idoreports_get_sla_ok_percent'; diff --git a/schema/postgresql/t/07-test-func.sql b/schema/postgresql/t/07-test-func.sql new file mode 100644 index 0000000..5c370e3 --- /dev/null +++ b/schema/postgresql/t/07-test-func.sql @@ -0,0 +1,33 @@ +SELECT plan(27); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 12:00', '2019-02-05 14:00')::float , 0.0::float,'Host 1 was down 2 out of 2 hours'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 10:00', '2019-02-05 14:00')::float , 50.0::float,'Host 1 was down 2 out of 4 hours'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 10:00', '2019-02-05 18:00')::float , 75.0::float,'Host 1 was down 2 out of 8 hours'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-04 10:00', '2019-02-04 18:00')::float , 100.0::float,'Host 1 was not down before 02/05 12:00'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-06 10:00', '2019-02-08 18:00')::float , 100.0::float,'Host 1 was not down after 02/05 14:00'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-04 13:00', '2019-02-05 13:00')::float , 95.83333333333333::float,'Host 1 was down for the last hour of checked timeframe'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-02-05 13:00', '2019-02-06 13:00')::float , 95.83333333333333::float,'Host 1 was down for the first hour of checked timeframe'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-03-05 11:00', '2019-03-05 13:00')::float , 50.0::float,'Host 1 was down 1 out of 2 hours'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-03-05 12:00', '2019-03-05 13:00')::float , 0.0::float,'Host 1 was down during that period'); +SELECT is(idoreports_get_sla_ok_percent(1,'2019-03-05 13:00', '2019-03-05 14:00')::float , 0.0::float,'Host 1 was down during that period'); + +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 12:00', '2019-02-05 14:00')::float , 0.0::float,'Service 2 was down 2 out of 2 hours'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 10:00', '2019-02-05 14:00')::float , 50.0::float,'Service 2 was down 2 out of 4 hours'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 10:00', '2019-02-05 18:00')::float , 75.0::float,'Service 2 was down 2 out of 8 hours'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-04 10:00', '2019-02-04 18:00')::float , 100.0::float,'Service 2 was not down before 02/05 12:00'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-06 10:00', '2019-02-08 18:00')::float , 100.0::float,'Service 2 was not down after 02/05 14:00'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-04 13:00', '2019-02-05 13:00')::float , 95.83333333333333::float,'Service 2 was down for the last hour of checked timeframe'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-02-05 13:00', '2019-02-06 13:00')::float , 95.83333333333333::float,'Service 2 was down for the first hour of checked timeframe'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-03-05 11:00', '2019-03-05 13:00')::float , 50.0::float,'Service 2 was down 1 out of 2 hours'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-03-05 12:00', '2019-03-05 13:00')::float , 0.0::float,'Service 2 was down during that period'); +SELECT is(idoreports_get_sla_ok_percent(2,'2019-03-05 13:00', '2019-03-05 14:00')::float , 0.0::float,'Service 2 was down during that period'); + +SELECT is(idoreports_get_sla_ok_percent(3,'2019-03-10 17:00', '2019-03-11 00:00')::float , 0.0::float,'Host 3 was considered down for the rest of the day'); + +SELECT is(idoreports_get_sla_ok_percent(4,'2020-03-01 12:00', '2020-03-01 16:00')::float , 50.0::float,'Host 4 was considered down for 2 hours in a 4 hours time range starting with UP'); +SELECT is(idoreports_get_sla_ok_percent(5,'2020-04-01 12:00', '2020-04-01 16:00')::float , 50.0::float,'Host 5 was considered down for 2 hours in a 4 hours time range starting with DOWN'); + +SELECT is(idoreports_get_sla_ok_percent(6,'2019-04-01 11:43:16','2019-04-01 15:43:16')::float , 46.65972222222222::float,'Host 6 was down until recently'); + +SELECT is(idoreports_get_sla_ok_percent(7,'2019-04-15 10:00:00','2019-04-15 15:00:00')::float , 95.0::float,'Host 7 had a planned downtime but went down before that started'); +SELECT is(idoreports_get_sla_ok_percent(7,'2019-04-15 11:45:01','2019-04-15 11:49:59')::float , 100.0::float,'Host 7 had a planned downtime'); +SELECT is(idoreports_get_sla_ok_percent(7,'2019-04-15 00:00:00','2019-04-16 00:00:00')::float , 80.0::float,'Host 7 had two planned downtimes which were correctly assessed'); diff --git a/schema/postgresql/t/README.md b/schema/postgresql/t/README.md new file mode 100644 index 0000000..5467be9 --- /dev/null +++ b/schema/postgresql/t/README.md @@ -0,0 +1,18 @@ +Tests for the idoreports_get_sla_ok_percent() function +====================================================== + +These are pg_tap tests. You need pg_tap installed for the PG version you want to test on, e.g. "postgresql-12-pgtap" for Debian/Ubuntu. + +I used these to find the cause for some seamingly strange NULL results. +Which were basically due to a badly chosen ALIAS ("state_time" on line 20, which is now "state_time_"). + +I run these tests on an Ubuntu/Debian system with "pg_virtualenv" like this: + +``` +pg_virtualenv -s pg_prove t/0*.sql +``` +or simply +``` +t/testme.sh +``` + diff --git a/schema/postgresql/t/get_sla_ok_percent.sql b/schema/postgresql/t/get_sla_ok_percent.sql new file mode 120000 index 0000000..69aa266 --- /dev/null +++ b/schema/postgresql/t/get_sla_ok_percent.sql @@ -0,0 +1 @@ +../get_sla_ok_percent.sql
\ No newline at end of file diff --git a/schema/postgresql/t/plain_sql_zum_debuggen.sql b/schema/postgresql/t/plain_sql_zum_debuggen.sql new file mode 100644 index 0000000..7adf80e --- /dev/null +++ b/schema/postgresql/t/plain_sql_zum_debuggen.sql @@ -0,0 +1,185 @@ +--SELECT is(idoreports_get_sla_ok_percent(4,'2020-03-01 12:00', '2020-03-01 16:00')::float , 50.0::float,'Host 4 was considered down for 2 hours in a 4 hours time range starting with UP'); +--SELECT is(idoreports_get_sla_ok_percent(5,'2020-04-01 12:00', '2020-04-01 16:00')::float , 50.0::float,'Host 5 was considered down for 2 hours in a 4 hours time range starting with DOWN'); +\set id 5 +\set start '2020-04-01 12:00' +\set end '2020-04-01 16:00' +\set sla_id null + +--'2019-02-19 00:00:00','2019-02-20 10:00:00' +--12347 + +WITH crit AS ( + SELECT CASE objecttype_id + WHEN 1 THEN 0 + WHEN 2 THEN 1 + END + AS value + FROM icinga_objects + WHERE object_id = :id +), +before AS ( + -- low border, last event before the range we are looking for: + SELECT down, state_time_ AS state_time,state FROM ( + (SELECT 1 AS prio + ,state > crit.value AS down + ,GREATEST(state_time,:'start') AS state_time_ + ,state + FROM icinga_statehistory,crit + WHERE + object_id = :id + AND state_time < :'start' + AND state_type = 1 + ORDER BY state_time DESC + LIMIT 1) + UNION ALL + (SELECT 2 AS prio + ,state > crit.value AS down + ,GREATEST(state_time,:'start') AS state_time_ + ,state + FROM icinga_statehistory,crit + WHERE + object_id = :id + AND state_time < :'start' + ORDER BY state_time DESC + LIMIT 1) + + ) ranked ORDER BY prio + LIMIT 1 +) SELECT * FROM before; +,all_hard_events AS ( + -- the actual range we're looking for: + SELECT state > crit.value AS down + ,state_time + ,state + FROM icinga_statehistory,crit + WHERE + object_id = :id + AND state_time >= :'start' + AND state_time <= :'end' + AND state_type = 1 +), + +after AS ( + -- the "younger" of the current host/service state and the first recorded event + (SELECT state > crit_value AS down + ,LEAST(state_time,:'end') AS state_time + ,state + + FROM ( + (SELECT state_time + ,state + ,crit.value crit_value + FROM icinga_statehistory,crit + WHERE + object_id = :id + AND state_time > :'end' + AND state_type = 1 + ORDER BY state_time ASC + LIMIT 1) + + UNION ALL + + SELECT status_update_time + ,current_state + ,crit.value crit_value + FROM icinga_hoststatus,crit + WHERE host_object_id = :id + AND state_type = 1 + + UNION ALL + + SELECT status_update_time + ,current_state + ,crit.value crit_value + FROM icinga_servicestatus,crit + WHERE service_object_id = :id + AND state_type = 1 + ) AS after_searched_period + ORDER BY state_time ASC LIMIT 1) +) +, allevents AS ( + TABLE before + UNION ALL + TABLE all_hard_events + UNION ALL + TABLE after +) --SELECT * FROM allevents; +, downtimes AS ( + SELECT tsrange( + --GREATEST(actual_start_time, :'start') + --, LEAST(actual_end_time, :'end') + actual_start_time + , actual_end_time + ) AS downtime + FROM icinga_downtimehistory + WHERE object_id = :id +-- AND actual_start_time <= :'end' +-- AND COALESCE(actual_end_time,:'start') >= :'start' + + UNION ALL + + SELECT tsrange( + --GREATEST(start_time, :'start') + --, LEAST(end_time, :'end') + start_time + , end_time + ) AS downtime + FROM icinga_outofsla_periods + WHERE timeperiod_object_id = :sla_id + +) --SELECT * FROM allevents; +, enriched AS ( + SELECT down + ,tsrange(state_time, COALESCE(lead(state_time) OVER w, :'end'),'(]') AS timeframe + --,lead(state_time) OVER w - state_time AS dauer + FROM ( + SELECT state > crit.value AS down + , lead(state,1,state) OVER w > crit.value AS next_down + , lag(state,1,state) OVER w > crit.value AS prev_down + , state_time + , state + FROM allevents,crit + WINDOW w AS (ORDER BY state_time) + ) alle + --WHERE down != next_down OR down != prev_down + WINDOW w AS (ORDER BY state_time) +) +, relevant AS ( + SELECT down + ,timeframe * tsrange(:'start',:'end','(]') AS timeframe + FROM enriched + WHERE timeframe && tsrange(:'start',:'end','(]') +) SELECT * FROM relevant; + +, relevant_down AS ( + SELECT timeframe + ,down + ,timeframe * downtime AS covered + ,COALESCE( + timeframe - downtime + ,timeframe + ) AS not_covered + FROM relevant + LEFT JOIN downtimes + ON timeframe && downtime + WHERE down +) -- SELECT * FROM relevant_down; + +, effective_downtimes AS ( + SELECT not_covered + , upper(not_covered) - lower(not_covered) AS dauer + FROM relevant_down +) --SELECT * FROM effective_downtimes; + +, final_result AS ( + SELECT sum(dauer) AS total_downtime + , timestamp :'end' - timestamp :'start' AS considered + , COALESCE(extract ('epoch' from sum(dauer)),0) AS down_secs + , extract ('epoch' from timestamp :'end' - timestamp :'start' ) AS considered_secs + FROM effective_downtimes +) --SELECT * FROM final_result; + +SELECT :'start' AS starttime, :'end' AS endtime,* +, 100.0 - down_secs / considered_secs * 100.0 AS availability +FROM final_result +; diff --git a/schema/postgresql/t/testme.sh b/schema/postgresql/t/testme.sh new file mode 100755 index 0000000..ce61abc --- /dev/null +++ b/schema/postgresql/t/testme.sh @@ -0,0 +1,2 @@ +#!/bin/sh +pg_virtualenv -s pg_prove $(dirname $0)/0*.sql |
