2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2016 Victor Kirhenshtein
5 ** This program is free software; you can redistribute it and/or modify
6 ** it under the terms of the GNU General Public License as published by
7 ** the Free Software Foundation; either version 2 of the License, or
8 ** (at your option) any later version.
10 ** This program is distributed in the hope that it will be useful,
11 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
12 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 ** GNU General Public License for more details.
15 ** You should have received a copy of the GNU General Public License
16 ** along with this program; if not, write to the Free Software
17 ** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
30 static int m_iNumErrors
= 0;
31 static int m_iNumFixes
= 0;
32 static int m_iStageErrors
;
33 static int s_stageErrorsUpdate
;
34 static int m_iStageFixes
;
35 static TCHAR
*m_pszStageMsg
= NULL
;
36 static int s_stageWorkTotal
= 0;
37 static int s_stageWorkDone
= 0;
42 static void StartStage(const TCHAR
*pszMsg
, int workTotal
= 1)
47 m_pszStageMsg
= _tcsdup(pszMsg
);
48 m_iStageErrors
= m_iNumErrors
;
49 s_stageErrorsUpdate
= m_iNumErrors
;
50 m_iStageFixes
= m_iNumFixes
;
51 s_stageWorkTotal
= workTotal
;
54 WriteToTerminalEx(_T("\x1b[1m*\x1b[0m %-67s \x1b[37;1m[\x1b[0m 0%% \x1b[37;1m]\x1b[0m\b\b\b"), m_pszStageMsg
);
58 SetOperationInProgress(true);
62 * Set total work for stage
64 static void SetStageWorkTotal(int workTotal
)
66 s_stageWorkTotal
= workTotal
;
67 if (s_stageWorkDone
> s_stageWorkTotal
)
68 s_stageWorkDone
= s_stageWorkTotal
;
72 * Update stage progress
74 static void UpdateStageProgress(int installment
)
76 if (m_iNumErrors
- s_stageErrorsUpdate
> 0)
78 StartStage(NULL
); // redisplay stage message
79 s_stageErrorsUpdate
= m_iNumErrors
;
82 s_stageWorkDone
+= installment
;
83 if (s_stageWorkDone
> s_stageWorkTotal
)
84 s_stageWorkDone
= s_stageWorkTotal
;
85 WriteToTerminalEx(_T("\b\b\b%3d"), s_stageWorkDone
* 100 / s_stageWorkTotal
);
94 static void EndStage()
96 static const TCHAR
*pszStatus
[] = { _T("PASSED"), _T("FIXED "), _T("ERROR ") };
97 static int nColor
[] = { 32, 33, 31 };
100 nErrors
= m_iNumErrors
- m_iStageErrors
;
103 nCode
= (m_iNumFixes
- m_iStageFixes
== nErrors
) ? 1 : 2;
104 if (m_iNumErrors
- s_stageErrorsUpdate
> 0)
105 StartStage(NULL
); // redisplay stage message
111 WriteToTerminalEx(_T("\b\b\b\b\b\x1b[37;1m[\x1b[%d;1m%s\x1b[37;1m]\x1b[0m\n"), nColor
[nCode
], pszStatus
[nCode
]);
112 SetOperationInProgress(false);
116 * Get object name from object_properties table
118 static TCHAR
*GetObjectName(DWORD dwId
, TCHAR
*pszBuffer
)
123 _sntprintf(szQuery
, 256, _T("SELECT name FROM object_properties WHERE object_id=%d"), dwId
);
124 hResult
= SQLSelect(szQuery
);
127 if (DBGetNumRows(hResult
) > 0)
129 DBGetField(hResult
, 0, 0, pszBuffer
, MAX_OBJECT_NAME
);
133 _tcscpy(pszBuffer
, _T("<unknown>"));
138 _tcscpy(pszBuffer
, _T("<unknown>"));
144 * Check that given node is inside at least one container or cluster
146 static bool NodeInContainer(DWORD id
)
152 _sntprintf(query
, 256, _T("SELECT container_id FROM container_members WHERE object_id=%d"), id
);
153 hResult
= SQLSelect(query
);
156 result
= (DBGetNumRows(hResult
) > 0);
157 DBFreeResult(hResult
);
162 _sntprintf(query
, 256, _T("SELECT cluster_id FROM cluster_members WHERE node_id=%d"), id
);
163 hResult
= SQLSelect(query
);
166 result
= (DBGetNumRows(hResult
) > 0);
167 DBFreeResult(hResult
);
175 * Find subnet for unlinked node
177 static BOOL
FindSubnetForNode(DWORD id
, const TCHAR
*name
)
179 DB_RESULT hResult
, hResult2
;
180 TCHAR query
[256], buffer
[32];
182 BOOL success
= FALSE
;
184 // Read list of interfaces of given node
185 _sntprintf(query
, 256, _T("SELECT l.ip_addr,l.ip_netmask FROM interfaces i INNER JOIN interface_address_list l ON l.iface_id = i.id WHERE node_id=%d"), id
);
186 hResult
= SQLSelect(query
);
189 count
= DBGetNumRows(hResult
);
190 for(i
= 0; i
< count
; i
++)
192 InetAddress addr
= DBGetFieldInetAddr(hResult
, i
, 0);
193 addr
.setMaskBits(DBGetFieldLong(hResult
, i
, 1));
194 InetAddress subnet
= addr
.getSubnetAddress();
196 _sntprintf(query
, 256, _T("SELECT id FROM subnets WHERE ip_addr='%s'"), subnet
.toString(buffer
));
197 hResult2
= SQLSelect(query
);
198 if (hResult2
!= NULL
)
200 if (DBGetNumRows(hResult2
) > 0)
202 UINT32 subnetId
= DBGetFieldULong(hResult2
, 0, 0);
204 if (GetYesNo(_T("Unlinked node object %d (\"%s\") can be linked to subnet %d (%s). Link?"), id
, name
, subnetId
, buffer
))
206 _sntprintf(query
, 256, _T("INSERT INTO nsmap (subnet_id,node_id) VALUES (%d,%d)"), subnetId
, id
);
215 // Node remains unlinked, so error count will be
216 // incremented again by node deletion code or next iteration
222 // Node remains unlinked, so error count will be
223 // incremented again by node deletion code
227 DBFreeResult(hResult2
);
230 DBFreeResult(hResult
);
238 static void CheckZones()
240 DB_RESULT hResult
, hResult2
;
241 DWORD i
, dwNumObjects
, dwId
;
244 StartStage(_T("Checking zone objects"));
245 hResult
= SQLSelect(_T("SELECT id FROM zones"));
248 dwNumObjects
= DBGetNumRows(hResult
);
249 for(i
= 0; i
< dwNumObjects
; i
++)
251 dwId
= DBGetFieldULong(hResult
, i
, 0);
253 // Check appropriate record in object_properties table
254 _sntprintf(szQuery
, 256, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), (int)dwId
);
255 hResult2
= SQLSelect(szQuery
);
256 if (hResult2
!= NULL
)
258 if ((DBGetNumRows(hResult2
) == 0) && (dwId
!= 4)) // Properties for built-in zone can be missing
261 if (GetYesNo(_T("Missing zone object %d properties. Create?"), dwId
))
266 _uuid_generate(guid
);
267 _sntprintf(szQuery
, 1024,
268 _T("INSERT INTO object_properties (object_id,guid,name,")
269 _T("status,is_deleted,is_system,inherit_access_rights,")
270 _T("last_modified,status_calc_alg,status_prop_alg,")
271 _T("status_fixed_val,status_shift,status_translation,")
272 _T("status_single_threshold,status_thresholds,location_type,")
273 _T("latitude,longitude,location_accuracy,location_timestamp,image,submap_id,maint_mode,maint_event_id) VALUES ")
274 _T("(%d,'%s','lost_zone_%d',5,0,0,1,") TIME_T_FMT
_T(",0,0,0,0,0,0,'00000000',0,")
275 _T("'0.000000','0.000000',0,0,'00000000-0000-0000-0000-000000000000',0,'0',0)"),
276 (int)dwId
, _uuid_to_string(guid
, guidText
), (int)dwId
, TIME_T_FCAST(time(NULL
)));
277 if (SQLQuery(szQuery
))
281 DBFreeResult(hResult2
);
284 DBFreeResult(hResult
);
292 static void CheckNodes()
294 DB_RESULT hResult
, hResult2
;
295 DWORD i
, dwNumObjects
, dwId
;
296 TCHAR szQuery
[1024], szName
[MAX_OBJECT_NAME
];
297 BOOL bResult
, bIsDeleted
= FALSE
;
299 StartStage(_T("Checking node objects"));
300 hResult
= SQLSelect(_T("SELECT id,primary_ip FROM nodes"));
303 dwNumObjects
= DBGetNumRows(hResult
);
304 SetStageWorkTotal(dwNumObjects
);
305 for(i
= 0; i
< dwNumObjects
; i
++)
307 dwId
= DBGetFieldULong(hResult
, i
, 0);
309 // Check appropriate record in object_properties table
310 _sntprintf(szQuery
, 256, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), dwId
);
311 hResult2
= SQLSelect(szQuery
);
312 if (hResult2
!= NULL
)
314 if (DBGetNumRows(hResult2
) == 0)
317 if (GetYesNo(_T("Missing node object %d properties. Create?"), dwId
))
322 _uuid_generate(guid
);
323 _sntprintf(szQuery
, 1024,
324 _T("INSERT INTO object_properties (object_id,guid,name,")
325 _T("status,is_deleted,is_system,inherit_access_rights,")
326 _T("last_modified,status_calc_alg,status_prop_alg,")
327 _T("status_fixed_val,status_shift,status_translation,")
328 _T("status_single_threshold,status_thresholds,location_type,")
329 _T("latitude,longitude,location_accuracy,location_timestamp,")
330 _T("image,submap_id,maint_mode,maint_event_id) VALUES ")
331 _T("(%d,'%s','lost_node_%d',5,0,0,1,") TIME_T_FMT
_T(",0,0,0,0,0,0,'00000000',0,")
332 _T("'0.000000','0.000000',0,0,'00000000-0000-0000-0000-000000000000',0,'0',0)"),
333 (int)dwId
, _uuid_to_string(guid
, guidText
), (int)dwId
, TIME_T_FCAST(time(NULL
)));
334 if (SQLQuery(szQuery
))
340 DBGetField(hResult2
, 0, 0, szName
, MAX_OBJECT_NAME
);
341 bIsDeleted
= DBGetFieldLong(hResult2
, 0, 1) ? TRUE
: FALSE
;
343 DBFreeResult(hResult2
);
348 _sntprintf(szQuery
, 1024, _T("SELECT subnet_id FROM nsmap WHERE node_id=%d"), dwId
);
349 hResult2
= SQLSelect(szQuery
);
350 if (hResult2
!= NULL
)
352 if ((DBGetNumRows(hResult2
) == 0) && (!NodeInContainer(dwId
)))
354 if ((DBGetFieldIPAddr(hResult
, i
, 1) == 0) || (!FindSubnetForNode(dwId
, szName
)))
357 if (GetYesNo(_T("Unlinked node object %d (\"%s\"). Delete it?"), dwId
, szName
))
359 _sntprintf(szQuery
, 1024, _T("DELETE FROM nodes WHERE id=%d"), dwId
);
360 bResult
= SQLQuery(szQuery
);
361 _sntprintf(szQuery
, 1024, _T("DELETE FROM acl WHERE object_id=%d"), dwId
);
362 bResult
= bResult
&& SQLQuery(szQuery
);
363 _sntprintf(szQuery
, 1024, _T("DELETE FROM object_properties WHERE object_id=%d"), dwId
);
364 if (SQLQuery(szQuery
) && bResult
)
369 DBFreeResult(hResult2
);
372 UpdateStageProgress(1);
374 DBFreeResult(hResult
);
380 * Check node component objects
382 static void CheckComponents(const TCHAR
*pszDisplayName
, const TCHAR
*pszTable
)
384 DB_RESULT hResult
, hResult2
;
385 DWORD i
, dwNumObjects
, dwId
;
386 TCHAR szQuery
[1024], szName
[MAX_OBJECT_NAME
];
388 _sntprintf(szQuery
, 1024, _T("Checking %s objects"), pszDisplayName
);
391 _sntprintf(szQuery
, 1024, _T("SELECT id,node_id FROM %s"), pszTable
);
392 hResult
= SQLSelect(szQuery
);
395 dwNumObjects
= DBGetNumRows(hResult
);
396 for(i
= 0; i
< dwNumObjects
; i
++)
398 dwId
= DBGetFieldULong(hResult
, i
, 0);
400 // Check appropriate record in object_properties table
401 _sntprintf(szQuery
, 1024, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), dwId
);
402 hResult2
= SQLSelect(szQuery
);
403 if (hResult2
!= NULL
)
405 if (DBGetNumRows(hResult2
) == 0)
408 if (GetYesNo(_T("Missing %s object %d properties. Create?"), pszDisplayName
, dwId
))
413 _uuid_generate(guid
);
414 _sntprintf(szQuery
, 1024,
415 _T("INSERT INTO object_properties (object_id,guid,name,")
416 _T("status,is_deleted,is_system,inherit_access_rights,")
417 _T("last_modified,status_calc_alg,status_prop_alg,")
418 _T("status_fixed_val,status_shift,status_translation,")
419 _T("status_single_threshold,status_thresholds,location_type,")
420 _T("latitude,longitude,location_accuracy,location_timestamp,submap_id,image,maint_mode,maint_event_id) VALUES ")
421 _T("(%d,'%s','lost_%s_%d',5,0,0,1,") TIME_T_FMT
_T(",0,0,0,0,0,0,'00000000',0,")
422 _T("'0.000000','0.000000',0,0,0,'00000000-0000-0000-0000-000000000000','0',0)"),
423 (int)dwId
, _uuid_to_string(guid
, guidText
), pszDisplayName
, (int)dwId
, TIME_T_FCAST(time(NULL
)));
424 if (SQLQuery(szQuery
))
431 DBGetField(hResult2
, 0, 0, szName
, MAX_OBJECT_NAME
);
433 DBFreeResult(hResult2
);
440 // Check if referred node exists
441 _sntprintf(szQuery
, 256, _T("SELECT name FROM object_properties WHERE object_id=%d AND is_deleted=0"),
442 DBGetFieldULong(hResult
, i
, 1));
443 hResult2
= SQLSelect(szQuery
);
444 if (hResult2
!= NULL
)
446 if (DBGetNumRows(hResult2
) == 0)
449 dwId
= DBGetFieldULong(hResult
, i
, 0);
450 if (GetYesNo(_T("Unlinked %s object %d (\"%s\"). Delete it?"), pszDisplayName
, dwId
, szName
))
452 _sntprintf(szQuery
, 256, _T("DELETE FROM %s WHERE id=%d"), pszTable
, dwId
);
453 if (SQLQuery(szQuery
))
455 _sntprintf(szQuery
, 256, _T("DELETE FROM object_properties WHERE object_id=%d"), dwId
);
461 DBFreeResult(hResult2
);
464 DBFreeResult(hResult
);
470 * Check common object properties
472 static void CheckObjectProperties()
476 DWORD i
, dwNumRows
, dwObjectId
;
478 StartStage(_T("Checking object properties"));
479 hResult
= SQLSelect(_T("SELECT object_id,name,last_modified FROM object_properties"));
482 dwNumRows
= DBGetNumRows(hResult
);
483 for(i
= 0; i
< dwNumRows
; i
++)
485 dwObjectId
= DBGetFieldULong(hResult
, i
, 0);
487 // Check last change time
488 if (DBGetFieldULong(hResult
, i
, 2) == 0)
491 if (GetYesNo(_T("Object %d [%s] has invalid timestamp. Fix it?"),
492 dwObjectId
, DBGetField(hResult
, i
, 1, szQuery
, 1024)))
494 _sntprintf(szQuery
, 1024, _T("UPDATE object_properties SET last_modified=") TIME_T_FMT
_T(" WHERE object_id=%d"),
495 TIME_T_FCAST(time(NULL
)), (int)dwObjectId
);
496 if (SQLQuery(szQuery
))
501 DBFreeResult(hResult
);
506 static void CheckContainerMembership()
508 StartStage(_T("Checking container membership"));
509 DB_RESULT containerList
= SQLSelect(_T("SELECT object_id,container_id FROM container_members"));
510 DB_RESULT objectList
= SQLSelect(_T("SELECT object_id FROM object_properties"));
511 if (containerList
!= NULL
&& objectList
!= NULL
)
513 int numContainers
= DBGetNumRows(containerList
);
514 int numObjects
= DBGetNumRows(objectList
);
518 SetStageWorkTotal(numContainers
);
519 for(int i
= 0; i
< numContainers
; i
++)
521 for(int n
= 0; n
< numObjects
; n
++)
523 if (DBGetFieldULong(containerList
, i
, 0) == DBGetFieldULong(objectList
, n
, 0))
532 if (GetYesNo(_T("Container %d contains non-existing child %d. Fix it?"),
533 DBGetFieldULong(containerList
, i
, 1), DBGetFieldULong(containerList
, i
, 0)))
535 _sntprintf(szQuery
, 1024, _T("DELETE FROM container_members WHERE object_id=%d AND container_id=%d"),
536 DBGetFieldULong(containerList
, i
, 0), DBGetFieldULong(containerList
, i
, 1));
537 if (SQLQuery(szQuery
))
542 UpdateStageProgress(1);
544 DBFreeResult(containerList
);
545 DBFreeResult(objectList
);
551 * Check cluster objects
553 static void CheckClusters()
556 TCHAR szQuery
[256], szName
[MAX_OBJECT_NAME
];
557 DWORD i
, dwNumRows
, dwObjectId
, dwId
;
559 StartStage(_T("Checking cluster objects"));
560 hResult
= SQLSelect(_T("SELECT cluster_id,node_id FROM cluster_members"));
563 dwNumRows
= DBGetNumRows(hResult
);
564 for(i
= 0; i
< dwNumRows
; i
++)
566 dwObjectId
= DBGetFieldULong(hResult
, i
, 1);
567 if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwObjectId
))
570 dwId
= DBGetFieldULong(hResult
, i
, 0);
571 if (GetYesNo(_T("Cluster object %s [%d] refers to non-existing node %d. Dereference?"),
572 GetObjectName(dwId
, szName
), dwId
, dwObjectId
))
574 _sntprintf(szQuery
, 256, _T("DELETE FROM cluster_members WHERE cluster_id=%d AND node_id=%d"),dwId
, dwObjectId
);
575 if (SQLQuery(szQuery
))
582 DBFreeResult(hResult
);
588 * Returns TRUE if SELECT returns non-empty set
590 static BOOL
CheckResultSet(TCHAR
*pszQuery
)
593 BOOL bResult
= FALSE
;
595 hResult
= SQLSelect(pszQuery
);
598 bResult
= (DBGetNumRows(hResult
) > 0);
599 DBFreeResult(hResult
);
605 * Check event processing policy
607 static void CheckEPP()
614 StartStage(_T("Checking event processing policy"));
616 // Check source object ID's
617 hResult
= SQLSelect(_T("SELECT object_id FROM policy_source_list"));
620 iNumRows
= DBGetNumRows(hResult
);
621 for(i
= 0; i
< iNumRows
; i
++)
623 dwId
= DBGetFieldULong(hResult
, i
, 0);
624 _sntprintf(szQuery
, 1024, _T("SELECT object_id FROM object_properties WHERE object_id=%d"), dwId
);
625 if (!CheckResultSet(szQuery
))
628 if (GetYesNo(_T("Invalid object ID %d used in policy. Delete it from policy?"), dwId
))
630 _sntprintf(szQuery
, 1024, _T("DELETE FROM policy_source_list WHERE object_id=%d"), dwId
);
631 if (SQLQuery(szQuery
))
636 DBFreeResult(hResult
);
640 hResult
= SQLSelect(_T("SELECT event_code FROM policy_event_list"));
643 iNumRows
= DBGetNumRows(hResult
);
644 for(i
= 0; i
< iNumRows
; i
++)
646 dwId
= DBGetFieldULong(hResult
, i
, 0);
647 if (dwId
& GROUP_FLAG
)
648 _sntprintf(szQuery
, 1024, _T("SELECT id FROM event_groups WHERE id=%d"), dwId
);
650 _sntprintf(szQuery
, 1024, _T("SELECT event_code FROM event_cfg WHERE event_code=%d"), dwId
);
651 if (!CheckResultSet(szQuery
))
654 if (GetYesNo(_T("Invalid event%s ID 0x%08X referenced in policy. Delete this reference?"), (dwId
& GROUP_FLAG
) ? _T(" group") : _T(""), dwId
))
656 _sntprintf(szQuery
, 1024, _T("DELETE FROM policy_event_list WHERE event_code=%d"), dwId
);
657 if (SQLQuery(szQuery
))
662 DBFreeResult(hResult
);
666 hResult
= SQLSelect(_T("SELECT action_id FROM policy_action_list"));
669 iNumRows
= DBGetNumRows(hResult
);
670 for(i
= 0; i
< iNumRows
; i
++)
672 dwId
= DBGetFieldULong(hResult
, i
, 0);
673 _sntprintf(szQuery
, 1024, _T("SELECT action_id FROM actions WHERE action_id=%d"), dwId
);
674 if (!CheckResultSet(szQuery
))
677 if (GetYesNo(_T("Invalid action ID %d referenced in policy. Delete this reference?"), dwId
))
679 _sntprintf(szQuery
, 1024, _T("DELETE FROM policy_action_list WHERE action_id=%d"), dwId
);
680 if (SQLQuery(szQuery
))
685 DBFreeResult(hResult
);
692 * Check data tables for given object class
694 static void CollectObjectIdentifiers(const TCHAR
*className
, IntegerArray
<UINT32
> *list
)
697 _sntprintf(query
, 256, _T("SELECT id FROM %s"), className
);
698 DB_RESULT hResult
= SQLSelect(query
);
701 int count
= DBGetNumRows(hResult
);
702 for(int i
= 0; i
< count
; i
++)
704 list
->add(DBGetFieldULong(hResult
, i
, 0));
706 DBFreeResult(hResult
);
711 * Get all data collection targets
713 IntegerArray
<UINT32
> *GetDataCollectionTargets()
715 IntegerArray
<UINT32
> *list
= new IntegerArray
<UINT32
>(128, 128);
716 CollectObjectIdentifiers(_T("nodes"), list
);
717 CollectObjectIdentifiers(_T("clusters"), list
);
718 CollectObjectIdentifiers(_T("mobile_devices"), list
);
719 CollectObjectIdentifiers(_T("access_points"), list
);
720 CollectObjectIdentifiers(_T("chassis"), list
);
721 CollectObjectIdentifiers(_T("sensors"), list
);
726 * Create idata_xx table
728 BOOL
CreateIDataTable(DWORD nodeId
)
730 TCHAR szQuery
[256], szQueryTemplate
[256];
733 MetaDataReadStr(_T("IDataTableCreationCommand"), szQueryTemplate
, 255, _T(""));
734 _sntprintf(szQuery
, 256, szQueryTemplate
, nodeId
);
735 if (!SQLQuery(szQuery
))
738 for(i
= 0; i
< 10; i
++)
740 _sntprintf(szQuery
, 256, _T("IDataIndexCreationCommand_%d"), i
);
741 MetaDataReadStr(szQuery
, szQueryTemplate
, 255, _T(""));
742 if (szQueryTemplate
[0] != 0)
744 _sntprintf(szQuery
, 256, szQueryTemplate
, nodeId
, nodeId
);
745 if (!SQLQuery(szQuery
))
754 * Create tdata_xx table - pre V281 version
756 BOOL
CreateTDataTable_preV281(DWORD nodeId
)
758 TCHAR szQuery
[256], szQueryTemplate
[256];
761 MetaDataReadStr(_T("TDataTableCreationCommand"), szQueryTemplate
, 255, _T(""));
762 _sntprintf(szQuery
, 256, szQueryTemplate
, nodeId
);
763 if (!SQLQuery(szQuery
))
766 for(i
= 0; i
< 10; i
++)
768 _sntprintf(szQuery
, 256, _T("TDataIndexCreationCommand_%d"), i
);
769 MetaDataReadStr(szQuery
, szQueryTemplate
, 255, _T(""));
770 if (szQueryTemplate
[0] != 0)
772 _sntprintf(szQuery
, 256, szQueryTemplate
, nodeId
, nodeId
);
773 if (!SQLQuery(szQuery
))
782 * Create tdata_xx table
784 BOOL
CreateTDataTable(DWORD nodeId
)
786 TCHAR szQuery
[256], szQueryTemplate
[256];
789 for(i
= 0; i
< 10; i
++)
791 _sntprintf(szQuery
, 256, _T("TDataTableCreationCommand_%d"), i
);
792 MetaDataReadStr(szQuery
, szQueryTemplate
, 255, _T(""));
793 if (szQueryTemplate
[0] != 0)
795 _sntprintf(szQuery
, 256, szQueryTemplate
, nodeId
, nodeId
);
796 if (!SQLQuery(szQuery
))
801 for(i
= 0; i
< 10; i
++)
803 _sntprintf(szQuery
, 256, _T("TDataIndexCreationCommand_%d"), i
);
804 MetaDataReadStr(szQuery
, szQueryTemplate
, 255, _T(""));
805 if (szQueryTemplate
[0] != 0)
807 _sntprintf(szQuery
, 256, szQueryTemplate
, nodeId
, nodeId
);
808 if (!SQLQuery(szQuery
))
817 * Check if DCI exists
819 static bool IsDciExists(UINT32 dciId
, UINT32 nodeId
, bool isTable
)
823 _sntprintf(query
, 256, _T("SELECT count(*) FROM %s WHERE item_id=%d AND node_id=%d"), isTable
? _T("dc_tables") : _T("items"), dciId
, nodeId
);
825 _sntprintf(query
, 256, _T("SELECT count(*) FROM %s WHERE item_id=%d"), isTable
? _T("dc_tables") : _T("items"), dciId
);
826 DB_RESULT hResult
= SQLSelect(query
);
830 int count
= DBGetFieldLong(hResult
, 0, 0);
831 DBFreeResult(hResult
);
836 * Check collected data
838 static void CheckCollectedData(bool isTable
)
840 StartStage(isTable
? _T("Checking table DCI history records") : _T("Checking DCI history records"));
842 time_t now
= time(NULL
);
843 IntegerArray
<UINT32
> *targets
= GetDataCollectionTargets();
844 SetStageWorkTotal(targets
->size());
845 for(int i
= 0; i
< targets
->size(); i
++)
847 UINT32 objectId
= targets
->get(i
);
849 _sntprintf(query
, 1024, _T("SELECT count(*) FROM %s_%d WHERE %s_timestamp>") TIME_T_FMT
,
850 isTable
? _T("tdata") : _T("idata"), objectId
, isTable
? _T("tdata") : _T("idata"), TIME_T_FCAST(now
));
851 DB_RESULT hResult
= SQLSelect(query
);
854 if (DBGetFieldLong(hResult
, 0, 0) > 0)
857 if (GetYesNo(_T("Found collected data for node [%d] with timestamp in the future. Delete invalid records?"), objectId
))
859 _sntprintf(query
, 1024, _T("DELETE FROM %s_%d WHERE %s_timestamp>") TIME_T_FMT
,
860 isTable
? _T("tdata") : _T("idata"), objectId
, isTable
? _T("tdata") : _T("idata"), TIME_T_FCAST(now
));
865 DBFreeResult(hResult
);
868 _sntprintf(query
, 1024, _T("SELECT distinct(item_id) FROM %s_%d"), isTable
? _T("tdata") : _T("idata"), objectId
);
869 hResult
= SQLSelect(query
);
872 int count
= DBGetNumRows(hResult
);
873 for(int i
= 0; i
< count
; i
++)
875 UINT32 id
= DBGetFieldLong(hResult
, i
, 0);
876 if (!IsDciExists(id
, objectId
, isTable
))
879 if (GetYesNo(_T("Found collected data for non-existing DCI [%d] on node [%d]. Delete invalid records?"), id
, objectId
))
881 _sntprintf(query
, 1024, _T("DELETE FROM %s_%d WHERE item_id=%d"), isTable
? _T("tdata") : _T("idata"), objectId
, id
);
887 DBFreeResult(hResult
);
890 UpdateStageProgress(1);
898 * Check raw DCI values
900 static void CheckRawDciValues()
902 StartStage(_T("Checking raw DCI values table"));
904 time_t now
= time(NULL
);
906 DB_RESULT hResult
= SQLSelect(_T("SELECT item_id FROM raw_dci_values"));
909 int count
= DBGetNumRows(hResult
);
910 SetStageWorkTotal(count
+ 1);
911 for(int i
= 0; i
< count
; i
++)
913 UINT32 id
= DBGetFieldLong(hResult
, i
, 0);
914 if (!IsDciExists(id
, 0, false))
917 if (GetYesNo(_T("Found raw value record for non-existing DCI [%d]. Delete it?"), id
))
920 _sntprintf(query
, 256, _T("DELETE FROM raw_dci_values WHERE item_id=%d"), id
);
925 UpdateStageProgress(1);
927 DBFreeResult(hResult
);
931 _sntprintf(query
, 1024, _T("SELECT count(*) FROM raw_dci_values WHERE last_poll_time>") TIME_T_FMT
, TIME_T_FCAST(now
));
932 hResult
= SQLSelect(query
);
935 if (DBGetFieldLong(hResult
, 0, 0) > 0)
938 if (GetYesNo(_T("Found DCIs with last poll timestamp in the future. Fix it?")))
940 _sntprintf(query
, 1024, _T("UPDATE raw_dci_values SET last_poll_time=") TIME_T_FMT
_T(" WHERE last_poll_time>") TIME_T_FMT
, TIME_T_FCAST(now
), TIME_T_FCAST(now
));
945 DBFreeResult(hResult
);
947 UpdateStageProgress(1);
955 static void CheckThresholds()
957 StartStage(_T("Checking DCI thresholds"));
959 DB_RESULT hResult
= SQLSelect(_T("SELECT threshold_id,item_id FROM thresholds"));
962 int count
= DBGetNumRows(hResult
);
963 SetStageWorkTotal(count
);
964 for(int i
= 0; i
< count
; i
++)
966 UINT32 dciId
= DBGetFieldULong(hResult
, i
, 1);
967 if (!IsDciExists(dciId
, 0, false))
970 if (GetYesNo(_T("Found threshold configuration for non-existing DCI [%d]. Delete?"), dciId
))
973 _sntprintf(query
, 256, _T("DELETE FROM thresholds WHERE threshold_id=%d AND item_id=%d"), DBGetFieldLong(hResult
, i
, 0), dciId
);
978 UpdateStageProgress(1);
980 DBFreeResult(hResult
);
989 static void CheckTableThresholds()
991 StartStage(_T("Checking table DCI thresholds"));
993 DB_RESULT hResult
= SQLSelect(_T("SELECT id,table_id FROM dct_thresholds"));
996 int count
= DBGetNumRows(hResult
);
997 SetStageWorkTotal(count
);
998 for(int i
= 0; i
< count
; i
++)
1000 UINT32 dciId
= DBGetFieldULong(hResult
, i
, 1);
1001 if (!IsDciExists(dciId
, 0, true))
1004 if (GetYesNo(_T("Found threshold configuration for non-existing table DCI [%d]. Delete?"), dciId
))
1006 UINT32 id
= DBGetFieldLong(hResult
, i
, 0);
1009 _sntprintf(query
, 256, _T("DELETE FROM dct_threshold_instances WHERE threshold_id=%d"), id
);
1010 if (SQLQuery(query
))
1012 _sntprintf(query
, 256, _T("DELETE FROM dct_threshold_conditions WHERE threshold_id=%d"), id
);
1013 if (SQLQuery(query
))
1015 _sntprintf(query
, 256, _T("DELETE FROM dct_thresholds WHERE id=%d"), id
);
1016 if (SQLQuery(query
))
1022 UpdateStageProgress(1);
1024 DBFreeResult(hResult
);
1031 * Check if given data table exist
1033 bool IsDataTableExist(const TCHAR
*format
, UINT32 id
)
1036 _sntprintf(table
, 256, format
, id
);
1037 int rc
= DBIsTableExist(g_hCoreDB
, table
);
1038 if (rc
== DBIsTableExist_Failure
)
1040 _tprintf(_T("WARNING: call to DBIsTableExist(\"%s\") failed\n"), table
);
1042 return rc
!= DBIsTableExist_NotFound
;
1048 static void CheckDataTables()
1050 StartStage(_T("Checking data tables"));
1052 IntegerArray
<UINT32
> *targets
= GetDataCollectionTargets();
1053 SetStageWorkTotal(targets
->size());
1054 for(int i
= 0; i
< targets
->size(); i
++)
1056 UINT32 objectId
= targets
->get(i
);
1059 if (!IsDataTableExist(_T("idata_%d"), objectId
))
1063 TCHAR objectName
[MAX_OBJECT_NAME
];
1064 GetObjectName(objectId
, objectName
);
1065 if (GetYesNo(_T("Data collection table (IDATA) for object %s [%d] not found. Create? (Y/N) "), objectName
, objectId
))
1067 if (CreateIDataTable(objectId
))
1073 if (!IsDataTableExist(_T("tdata_%d"), objectId
))
1077 TCHAR objectName
[MAX_OBJECT_NAME
];
1078 GetObjectName(objectId
, objectName
);
1079 if (GetYesNo(_T("Data collection table (TDATA) for %s [%d] not found. Create? (Y/N) "), objectName
, objectId
))
1081 if (CreateTDataTable(objectId
))
1086 UpdateStageProgress(1);
1094 * Check template to node mapping
1096 static void CheckTemplateNodeMapping()
1099 TCHAR name
[256], query
[256];
1100 DWORD i
, dwNumRows
, dwTemplateId
, dwNodeId
;
1102 StartStage(_T("Checking template to node mapping"));
1103 hResult
= SQLSelect(_T("SELECT template_id,node_id FROM dct_node_map ORDER BY template_id"));
1104 if (hResult
!= NULL
)
1106 dwNumRows
= DBGetNumRows(hResult
);
1107 SetStageWorkTotal(dwNumRows
);
1108 for(i
= 0; i
< dwNumRows
; i
++)
1110 dwTemplateId
= DBGetFieldULong(hResult
, i
, 0);
1111 dwNodeId
= DBGetFieldULong(hResult
, i
, 1);
1113 // Check node existence
1114 if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwNodeId
) &&
1115 !IsDatabaseRecordExist(_T("clusters"), _T("id"), dwNodeId
) &&
1116 !IsDatabaseRecordExist(_T("mobile_devices"), _T("id"), dwNodeId
))
1119 GetObjectName(dwTemplateId
, name
);
1120 if (GetYesNo(_T("Template %d [%s] mapped to non-existent node %d. Delete this mapping?"), dwTemplateId
, name
, dwNodeId
))
1122 _sntprintf(query
, 256, _T("DELETE FROM dct_node_map WHERE template_id=%d AND node_id=%d"),
1123 dwTemplateId
, dwNodeId
);
1124 if (SQLQuery(query
))
1128 UpdateStageProgress(1);
1130 DBFreeResult(hResult
);
1136 * Check network map links
1138 static void CheckMapLinks()
1140 StartStage(_T("Checking network map links"));
1142 for(int pass
= 1; pass
<= 2; pass
++)
1145 _sntprintf(query
, 1024,
1146 _T("SELECT network_map_links.map_id,network_map_links.element1,network_map_links.element2 ")
1147 _T("FROM network_map_links ")
1148 _T("LEFT OUTER JOIN network_map_elements ON ")
1149 _T(" network_map_links.map_id = network_map_elements.map_id AND ")
1150 _T(" network_map_links.element%d = network_map_elements.element_id ")
1151 _T("WHERE network_map_elements.element_id IS NULL"), pass
);
1153 DB_RESULT hResult
= SQLSelect(query
);
1154 if (hResult
!= NULL
)
1156 int count
= DBGetNumRows(hResult
);
1157 for(int i
= 0; i
< count
; i
++)
1160 DWORD mapId
= DBGetFieldULong(hResult
, i
, 0);
1161 TCHAR name
[MAX_OBJECT_NAME
];
1162 GetObjectName(mapId
, name
);
1163 if (GetYesNo(_T("Invalid link on network map %s [%d]. Delete?"), name
, mapId
))
1165 _sntprintf(query
, 256, _T("DELETE FROM network_map_links WHERE map_id=%d AND element1=%d AND element2=%d"),
1166 mapId
, DBGetFieldLong(hResult
, i
, 1), DBGetFieldLong(hResult
, i
, 2));
1167 if (SQLQuery(query
))
1171 DBFreeResult(hResult
);
1178 * Check database for errors
1180 void CheckDatabase()
1184 BOOL bCompleted
= FALSE
;
1186 if (g_checkDataTablesOnly
)
1187 _tprintf(_T("Checking database (data tables only):\n"));
1189 _tprintf(_T("Checking database (%s collected data):\n"), g_checkData
? _T("including") : _T("excluding"));
1191 // Get database format version
1192 iVersion
= DBGetSchemaVersion(g_hCoreDB
);
1193 if ((iVersion
< DB_FORMAT_VERSION
) && !g_checkDataTablesOnly
)
1195 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
1196 iVersion
, DB_FORMAT_VERSION
);
1198 else if (iVersion
> DB_FORMAT_VERSION
)
1200 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n")
1201 _T("You need to upgrade your server before using this database.\n"),
1202 iVersion
, DB_FORMAT_VERSION
);
1207 TCHAR szLockStatus
[MAX_DB_STRING
], szLockInfo
[MAX_DB_STRING
];
1208 BOOL bLocked
= FALSE
;
1210 // Check if database is locked
1211 hResult
= DBSelect(g_hCoreDB
, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
1212 if (hResult
!= NULL
)
1214 if (DBGetNumRows(hResult
) > 0)
1216 DBGetField(hResult
, 0, 0, szLockStatus
, MAX_DB_STRING
);
1217 DecodeSQLString(szLockStatus
);
1218 bLocked
= _tcscmp(szLockStatus
, _T("UNLOCKED"));
1220 DBFreeResult(hResult
);
1224 hResult
= DBSelect(g_hCoreDB
, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
1225 if (hResult
!= NULL
)
1227 if (DBGetNumRows(hResult
) > 0)
1229 DBGetField(hResult
, 0, 0, szLockInfo
, MAX_DB_STRING
);
1230 DecodeSQLString(szLockInfo
);
1232 DBFreeResult(hResult
);
1238 if (GetYesNo(_T("Database is locked by server %s [%s]\nDo you wish to force database unlock?"), szLockStatus
, szLockInfo
))
1240 if (SQLQuery(_T("UPDATE config SET var_value='UNLOCKED' where var_name='DBLockStatus'")))
1243 _tprintf(_T("Database lock removed\n"));
1252 if (g_checkDataTablesOnly
)
1260 CheckComponents(_T("interface"), _T("interfaces"));
1261 CheckComponents(_T("network service"), _T("network_services"));
1263 CheckTemplateNodeMapping();
1264 CheckObjectProperties();
1265 CheckContainerMembership();
1269 CheckRawDciValues();
1271 CheckTableThresholds();
1274 CheckCollectedData(false);
1275 CheckCollectedData(true);
1279 if (m_iNumErrors
== 0)
1281 _tprintf(_T("Database doesn't contain any errors\n"));
1282 DBCommit(g_hCoreDB
);
1286 _tprintf(_T("%d errors was found, %d errors was corrected\n"), m_iNumErrors
, m_iNumFixes
);
1287 if (m_iNumFixes
== m_iNumErrors
)
1288 _tprintf(_T("All errors in database was fixed\n"));
1290 _tprintf(_T("Database still contain errors\n"));
1291 if (m_iNumFixes
> 0)
1293 if (GetYesNo(_T("Commit changes?")))
1295 _tprintf(_T("Committing changes...\n"));
1296 if (DBCommit(g_hCoreDB
))
1297 _tprintf(_T("Changes was successfully committed to database\n"));
1301 _tprintf(_T("Rolling back changes...\n"));
1302 if (DBRollback(g_hCoreDB
))
1303 _tprintf(_T("All changes made to database was cancelled\n"));
1308 DBRollback(g_hCoreDB
);
1316 _tprintf(_T("Unable to get database lock status\n"));
1320 _tprintf(_T("Database check %s\n"), bCompleted
? _T("completed") : _T("aborted"));