In Jasper Group Availability Report Some Devices are missing

book

Article ID: 132469

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

In Jasper Report -> Group:Availability Device 
some of the devices are missing while those devices are present in Spectrum Global Collection (GC).

Generated missing device Availability:Single Device report for the same time period, it has shown the device availability correctly.

Cause

There should be a default 0 outage entry in the table else the device will not be shown in availability report.

Environment

Spectrum 10.2.x and 10.3.x
Jasper 6.3.x 6.4.x

Resolution

Please follow below steps 

In this KB 
Device Missing in report is PORTAL
Device is part of Servers Global Collection

Login to oneclick server
Start -> Run -> cmd
bash -l
cd mysql/bin
./mysql -uroot -proot reporting;

select * from globalcollection where gc_name="<GC name>"
Example: select * from globalcollection where gc_name="Servers"; (Servers is the Global Collection Name)

Output: 
| gc_rec_ID | gc_name | gc_unique_key | 
| 42 | SERVERS | 5abc9008-0241-1000-1fbe-0a4c07240000 |

note down the gc_rec_id value which is 42 in the above query output

select * from gcmodel where gc_rec_ID=<GC Rec ID>; 

Example:
select * from gcmodel where gc_rec_ID=42; 

output:
| gc_rec_ID | model_key | 
| 42 | 2739 | 
| 42 | 2740 | 
| 42 | 2749 | 
| 42 | 2758 | 
| 42 | 2763 | 
| 42 | 2768 | 
| 42 | 2778 | 
| 42 | 2783 | 
| 42 | 2790 | 
| 42 | 126662 | 
| 42 | 126891 | 
| 42 | 127002 | 
| 42 | 127062 | 
| 42 | 127063 | 
| 42 | 127064 | 
| 42 | 148919 | 
| 42 | 149081 | 
| 42 | 149082 | 
| 42 | 149083 | 
| 42 | 149831 | 
| 42 | 149832 | 
| 42 | 149880 | 


Now note down each model key and run below query

select * from model where model_key in ("model_key_1","model_key_2", .... "model_key_n");

Example
select * from model where model_key in
('2739','2740','2749','2758','2763','2768','2778','2783','2790','126662','126891','127002','127062','127063','127064','148919','149081','149082','149083','149831','149832','149880'); 

Output:
| model_key | model_h | model_name | mtype_h | model_class | network_address | first_event_time | landscape_h | destroy_time | security_string | isClusterEntity | 
| 2739 | 4194304930 | BKPD2| 18219145 | 9 | 192.168.1.20 | 2017-10-06 19:10:54 | 4194304000 | NULL | | 2 | 
| 2740 | 4194304929 | BKPD1| 18219145 | 9 | 192.168.1.19 | 2017-10-06 19:10:54 | 4194304000 | NULL | | 2 | 
| 2749 | 4194304928 | BKPSRV| 18219145 | 9 | 192.168.1.18 | 2017-10-06 19:10:54 | 4194304000 | NULL | | 2 | 
| 2758 | 4194304665 | HYP4 | 18219145 | 9 | 192.168.1.14 | 2017-10-06 17:45:11 | 4194304000 | NULL | | 2 | 
| 2763 | 4194304664 | HYP5| 18219145 | 9 | 192.168.1.15 | 2017-10-06 17:45:11 | 4194304000 | NULL | | 2 | 
| 2768 | 4194304663 | HYP3 | 18219145 | 9 | 192.168.1.13 | 2017-10-06 17:45:11 | 4194304000 | NULL | | 2 | 
| 2778 | 4194304613 | HYP6 | 18219145 | 9 | 192.168.1.16 | 2017-10-06 17:28:06 | 4194304000 | NULL | | 2 | 
| 2783 | 4194304557 | HYP2 | 18219145 | 9 | 192.168.1.12 | 2017-10-06 17:16:56 | 4194304000 | NULL | | 2 | 
| 2790 | 4194304293 | HYP1 | 18219145 | 9 | 192.168.1.11 | 2017-10-06 15:51:29 | 4194304000 | NULL | | 2 | 
| 126662 | 4194432632 | PORTAL | 18219145 | 9 | 192.168.1.13 | 2018-04-09 17:41:13 | 4194304000 | NULL | | 2 | 
| 126891 | 4194433384 | HYP-07 | 18219145 | 9 | 192.168.1.16 | 2018-04-10 17:53:47 | 4194304000 | NULL | | 2 | 
| 127002 | 4194436360 | syslog-srv | 18219145 | 9 | 192.168.1.17 | 2018-04-14 11:41:59 | 4194304000 | NULL | | 2 | 
| 127062 | 4194435410 | idns2 | 18219145 | 9 | 192.168.1.13 | 2018-04-13 09:53:58 | 4194304000 | NULL | | 2 | 
| 127063 | 4194435353 | edns2 | 18219145 | 9 | 192.168.1.11 | 2018-04-12 21:39:18 | 4194304000 | NULL | | 2 | 
| 127064 | 4194435349 | edns1 | 18219145 | 9 | 192.168.1.10 | 2018-04-12 21:27:00 | 4194304000 | NULL | | 2 | 
| 148919 | 4194460086 | AD2 | 18219145 | 9 | 192.168.1.32 | 2018-06-15 18:21:56 | 4194304000 | NULL | | 2 | 
| 149081 | 4194460203 | MAIL3.| 18219145 | 9 | 192.168.1.14 | 2018-06-16 21:03:31 | 4194304000 | NULL | | 2 | 
| 149082 | 4194460204 | MAIL4. | 18219145 | 9 | 192.168.1.15 | 2018-06-16 21:03:31 | 4194304000 | NULL | | 2 | 
| 149083 | 4194460205 | AD1 | 18219145 | 9 | 192.168.1.31 31 | 2018-06-16 21:03:31 | 4194304000 | NULL | | 2 | 
| 149831 | 4194459951 | VOIP1 | 18219145 | 9 | 192.168.1.135 | 2018-06-15 12:58:34 | 4194304000 | NULL | | 2 |
| 149832 | 4194459950 | VOIP2 | 18219145 | 9 | 192.168.1.136 | 2018-06-15 12:58:34 | 4194304000 | NULL | | 0 | 
| 149880 | 4194458935 | idns-01 | 18219145 | 9 | 192.168.1.12 | 2018-06-12 16:59:22 | 4194304000 | NULL | | 0 | 


Select * from modeloutage where model_key=<model_key_value of the missing device>

Example 
Select * from modeloutage where model_key=126662;
mysql> select * from modeloutage where model_key=126662;
| model_outage_ID | model_key | landscape_h | start_time | end_time | outage_type | zero_length_outage | start_event_key | end_event_key | legacy_outage_id | legacy_outage_source |
|          614721 |    126662 |  4194304000 | 2017-03-14 12:31:28 | 2017-03-14 12:31:51 |           1 |                  0 |         4386948 |       4388014 |             NULL | NULL                 |

There is no outage_type = 0 entry hence this device is not showing when generating report

Run below insert statement to add new row with outage_type = 0

insert into modeloutage
          (model_key, landscape_h, start_time, end_time, outage_type, zero_length_outage, start_event_key, end_event_key, legacy_outage_id, legacy_outage_source)
values( 126662 , 4194304000 ,'2017-03-14 12:31:28', '2017-03-14 12:31:28', 0, 0, null, null,null,null );

NOTE: while inserting the record ensure that you have the model_key, landscape_h values with you and date compare with a device which is showing and enter that date.

check modeloutage table
mysql> select * from modeloutage where model_key=126662;
| model_outage_ID | model_key | landscape_h | start_time | end_time | outage_type | zero_length_outage | start_event_key | end_event_key | legacy_outage_id | legacy_outage_source |
|          614721 |    126662 |  4194304000 | 2017-03-14 12:31:28 | 2017-03-14 12:31:51 |           1 |                  0 |         4386948 |       4388014 |             NULL | NULL                 |
|         1320419 |    126662 |  4194304000 | 2017-03-14 12:31:28 | 2017-03-14 12:31:28 |           0 |                  0 |            NULL |          NULL |             NULL | NULL                 |

New record is inserted

Now try to generate group availability report.  Missing device will be listed in the report.

NOTE:
Please open a support case if still issue persists.