<html><body><div style="font-family: Arial; font-size: 10pt; color: #000000"><div>Are all your servers sync to the same ntp and in UTM time?</div><div>Can you attach logs? </div><div><br></div><div><br></div><div>Yaniv</div><div><br></div><hr id="zwchr"><blockquote style="border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;" data-mce-style="border-left: 2px solid #1010FF; margin-left: 5px; padding-left: 5px; color: #000; font-weight: normal; font-style: normal; text-decoration: none; font-family: Helvetica,Arial,sans-serif; font-size: 12pt;"><b>From: </b>"张亚琪" <zhangyingyun001@gmail.com><br><b>To: </b>Devel@ovirt.org<br><b>Sent: </b>Friday, November 14, 2014 11:22:38 AM<br><b>Subject: </b>[ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem.<br><div><br></div><div dir="ltr"><div><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;">hi everybody,</span></div><div><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> </span></div><div><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> Recently, I have tested oVirt Reports. And I encountered a problem about some reports that cannot show data. And then I setup the DWH development environment. I found maybe this reason for missing data is the AggregationToHourly3.5. When data from datacenter_samples_history were inserted to datacenter_hourly_history, u will find nothing happened. Because the select sql before inserting data reads：</span></div><div><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"><br></span></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;">"SELECT history_id,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"><span class="" style="white-space:pre" data-mce-style="white-space: pre;"><span style="white-space:normal" data-mce-style="white-space: normal;"> </span></span>history_datetime,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> <span class="" style="white-space:pre" data-mce-style="white-space: pre;"><span style="white-space:normal" data-mce-style="white-space: normal;"> </span></span>datacenter_id,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> <span class="" style="white-space:pre" data-mce-style="white-space: pre;"><span style="white-space:normal" data-mce-style="white-space: normal;"> </span></span>datacenter_status,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> <span class="" style="white-space:pre" data-mce-style="white-space: pre;"><span style="white-space:normal" data-mce-style="white-space: normal;"> </span></span>minutes_in_status,</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"><span class="" style="white-space:pre" data-mce-style="white-space: pre;"><span style="white-space:normal" data-mce-style="white-space: normal;"> </span></span>datacenter_configuration_version</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;">FROM datacenter_samples_history</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;">WHERE <b>history_datetime >= '"+context.lastHourAggr+</b>"'</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;">AND history_datetime < '"+TalendDate.addDate(context.lastHourAggr, 1,"HH")+"'</span></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;">ORDER BY history_datetime, </span></blockquote></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> datacenter_id, </span></blockquote></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"> <span class="" style="white-space:pre" data-mce-style="white-space: pre;"><span style="white-space:normal" data-mce-style="white-space: normal;"> datacenter_status"</span></span></span></blockquote></blockquote><div><br></div><div><br></div><div>And then I queried the table of datacenter_samples_history & history_configuration (has the field of lastHourAggr) in the db of ovirt_engine_history. The results are as follows:</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">ovirt_engine_history=# select * from history_configuration;</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> var_name | var_value | var_datetime </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">-------------------+-----------+------------------------</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> MinimalETLVersion | 3.5.0 | </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> default_language | en_US | </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> firstSync | false | 2014-10-13 19:42:00+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> lastDayAggr | | 2014-11-14 00:00:00+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><b> lastHourAggr | | </b>2014-<span color="#ff0000" data-mce-style="color: #ff0000;" style="color: #ff0000;"><b>11-15</b></span> 06:00:00+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> HourlyAggFailed | false | </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">(6 rows)</blockquote><div><br></div></blockquote><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">ovirt_engine_history=# select <b>history_datetime</b> from datacenter_samples_history;</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> history_datetime </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;">----------------------------</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-<b><span color="#ff0000" data-mce-style="color: #ff0000;" style="color: #ff0000;">11-13</span></b> 03:07:00.23+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:08:00.238+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:09:00.229+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:10:00.221+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:11:00.229+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:12:00.221+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:13:00.237+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:14:00.22+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:15:00.221+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:16:00.238+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> 2014-11-13 03:17:00.238+08</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"><br></blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex" data-mce-style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: #cccccc; border-left-style: solid; padding-left: 1ex;"> </blockquote><div><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"><span color="#000000" data-mce-style="color: #000000;" style="color: #000000;"><span style="line-height:24px" data-mce-style="line-height: 24px;"><br></span></span></span></div><div><span face="comic sans ms, sans-serif" data-mce-style="font-family: 'comic sans ms', sans-serif;" style="font-family: 'comic sans ms', sans-serif;"><span color="#000000" data-mce-style="color: #000000;" style="color: #000000;"><span style="line-height:24px" data-mce-style="line-height: 24px;">Obviously， history_datetime < lastHourAggr , the data will never be inserted to the </span>datacenter_hourly_history. And the place where I bold <span style="line-height:24px" data-mce-style="line-height: 24px;">is the </span><span style="line-height:24px" data-mce-style="line-height: 24px;">root cause of the error. Then , I try to update the </span></span><b>lastHourAggr </b>in the table of history_configuration. Reports works successfully. However, the lastHourAggr will change to 2014-11-15 afterwards. But u know Today is 2014-11-14 ! I have no idea about why the value of lastHourAggr is 2014-11-15. Would u help me solve this problem. Thanks a lot ! </span></div></div><br>_______________________________________________<br>Devel mailing list<br>Devel@ovirt.org<br>http://lists.ovirt.org/mailman/listinfo/devel</blockquote><div><br></div></div></body></html>