finished configuration API for tabular DCI; added "licenses" table
[public/netxms.git] / src / agent / subagents / oracle / main.cpp
1 /*
2 ** NetXMS subagent for Oracle monitoring
3 ** Copyright (C) 2009-2012 Raden Solutions
4 **/
5
6 #include "oracle_subagent.h"
7
8 CONDITION g_shutdownCondition;
9 MUTEX g_paramAccessMutex;
10 int g_dbCount;
11 DB_DRIVER g_driverHandle = NULL;
12 DatabaseInfo g_dbInfo[MAX_DATABASES];
13 DatabaseData g_dbData[MAX_DATABASES];
14
15 THREAD_RESULT THREAD_CALL queryThread(void *arg);
16
17 DBParameterGroup g_paramGroup[] = {
18 {
19 700, _T("Oracle.Sessions."),
20 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, count(*) Count from v$session"),
21 2, { NULL }, 0
22 },
23 {
24 700, _T("Oracle.Cursors."),
25 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, sum(a.value) Count from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'"),
26 2, { NULL }, 0
27 },
28 {
29 700, _T("Oracle.Objects."),
30 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, count(*) InvalidCount from dba_objects where status!='VALID'"),
31 2, { NULL }, 0
32 },
33 {
34 700, _T("Oracle.DBInfo."),
35 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, name Name, to_char(created) CreateDate, log_mode LogMode, open_mode OpenMode from v$database"),
36 5, { NULL }, 0
37 },
38 {
39 700, _T("Oracle.Instance."),
40 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, version Version, status Status, archiver ArchiverStatus, shutdown_pending ShutdownPending from v$instance"),
41 5, { NULL }, 0
42 },
43 {
44 1000, _T("Oracle.TableSpaces."),
45 _T("select d.tablespace_name ValueName, d.status Status, d.contents Type, to_char(round(used_percent,2)) UsedPct from dba_tablespaces d, dba_tablespace_usage_metrics m where d.tablespace_name=m.tablespace_name"),
46 3, { NULL }, 0
47 },
48 {
49 700, _T("Oracle.Dual."),
50 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, decode(count(*),1,0,1) ExcessRows from dual"),
51 1, { NULL }, 0
52 },
53 {
54 700, _T("Oracle.Performance."),
55 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, (select s.value PhysReads from v$sysstat s, v$statname n where n.name='physical reads' and n.statistic#=s.statistic#) PhysReads, ")
56 _T("(select s.value LogicReads from v$sysstat s, v$statname n where n.name='session logical reads' and n.statistic#=s.statistic#) LogicReads, ")
57 _T("(select round((sum(decode(name,'consistent gets',value,0))+sum(decode(name,'db block gets',value,0))-sum(decode(name,'physical reads',value, 0)))/(sum(decode(name,'consistent gets',value,0))+sum(decode(name,'db block gets',value,0)))*100,2) from v$sysstat) CacheHitRatio, ")
58 _T("(select round(sum(waits)*100/sum(gets),2) from v$rollstat) RollbackWaitRatio, ")
59 _T("(select round((1-(sum(getmisses)/sum(gets)))*100,2) from v$rowcache) DictCacheHitRatio, ")
60 _T("(select round(sum(pins)/(sum(pins)+sum(reloads))*100,2) from v$librarycache) LibCacheHitRatio ")
61 _T("from DUAL "),
62 3, { NULL }, 0
63 },
64 {
65 700, _T("Oracle.CriticalStats."),
66 _T("select ") DB_NULLARG_MAGIC _T(" ValueName, (select count(*) TSOFF from DBA_TABLESPACES where status <> 'ONLINE') TSOffCount, ")
67 _T("(select count(*) DFOFF from V$DATAFILE where status not in ('ONLINE','SYSTEM')) DFOffCount, ")
68 _T("(select count(*) from DBA_SEGMENTS where max_extents = extents) FullSegmentsCount, ")
69 _T("(select count(*) from DBA_ROLLBACK_SEGS where status <> 'ONLINE') RBSegsNotOnlineCount, ")
70 _T("decode(sign(decode((select upper(log_mode) from v$database),'ARCHIVELOG',1,0)-")
71 _T("decode((select upper(value) from v$parameter where upper(name)='LOG_ARCHIVE_START'),'TRUE',1,0)),1, 1, 0) AutoArchivingOff, ")
72 _T("(SELECT count(file#) from v$datafile_header where recover ='YES') DatafilesNeedMediaRecovery, ")
73 _T("(SELECT count(*) FROM dba_jobs where NVL(failures,0) <> 0) FailedJobs ")
74 _T("from DUAL"),
75 5, { NULL }, 0
76 },
77 0
78 };
79
80 //
81 // Handler functions
82 //
83
84 LONG getParameters(const TCHAR *parameter, const TCHAR *argument, TCHAR *value)
85 {
86 LONG ret = SYSINFO_RC_UNSUPPORTED;
87 TCHAR dbId[MAX_STR];
88 TCHAR entity[MAX_STR];
89
90 // Get id of the database requested
91 if (!AgentGetParameterArg(parameter, 1, dbId, MAX_STR))
92 return ret;
93 if (!AgentGetParameterArg(parameter, 2, entity, MAX_STR) || entity[0] == _T('\0'))
94 nx_strncpy(entity, DB_NULLARG_MAGIC, MAX_STR);
95
96 AgentWriteDebugLog(7, _T("%s: got request for params: dbid='%s', param='%s'"), MYNAMESTR, dbId, parameter);
97
98 // Loop through databases and find an entry in g_dbInfo[] for this id
99 for (int i = 0; i <= g_dbCount; i++)
100 {
101 if (!_tcsnicmp(g_dbInfo[i].id, dbId, MAX_STR)) // found DB
102 {
103 if (argument[0] == _T('R'))
104 {
105 ret_string(value, g_dbInfo[i].connected ? _T("YES") : _T("NO"));
106 ret = SYSINFO_RC_SUCCESS;
107 }
108 // Loop through parameter groups and check whose prefix matches the parameter requested
109 for (int k = 0; argument[0] == _T('X') && g_paramGroup[k].prefix; k++)
110 {
111 if (!_tcsnicmp(g_paramGroup[k].prefix, parameter, _tcslen(g_paramGroup[k].prefix))) // found prefix
112 {
113 MutexLock(g_dbInfo[i].accessMutex);
114 // Loop through the values
115 AgentWriteDebugLog(9, _T("%s: valuecount %d"), MYNAMESTR, g_paramGroup[k].valueCount[i]);
116 for (int j = 0; j < g_paramGroup[k].valueCount[i]; j++)
117 {
118 StringMap* map = (g_paramGroup[k].values[i])[j].attrs;
119 TCHAR* name = (g_paramGroup[k].values[i])[j].name;
120 if (!_tcsnicmp(name, entity, MAX_STR)) // found value which matches the parameters argument
121 {
122 TCHAR key[MAX_STR];
123 nx_strncpy(key, parameter + _tcslen(g_paramGroup[k].prefix), MAX_STR);
124 TCHAR* place = _tcschr(key, _T('('));
125 if (place != NULL)
126 {
127 *place = _T('\0');
128 const TCHAR* dbval = map->get(key);
129 ret_string(value, dbval);
130 ret = SYSINFO_RC_SUCCESS;
131 }
132 break;
133 }
134 }
135 MutexUnlock(g_dbInfo[i].accessMutex);
136
137 break;
138 }
139 }
140 break;
141 }
142 }
143
144 return ret;
145 }
146
147
148 //
149 // Subagent initialization
150 //
151
152 static BOOL SubAgentInit(Config *config)
153 {
154 BOOL result = TRUE;
155 static DatabaseInfo info;
156 int i;
157 static NX_CFG_TEMPLATE configTemplate[] =
158 {
159 { _T("Id"), CT_STRING, 0, 0, MAX_STR, 0, info.id },
160 { _T("Name"), CT_STRING, 0, 0, MAX_STR, 0, info.name },
161 { _T("TnsName"), CT_STRING, 0, 0, MAX_STR, 0, info.name },
162 { _T("UserName"), CT_STRING, 0, 0, MAX_USERNAME, 0, info.username },
163 { _T("Password"), CT_STRING, 0, 0, MAX_PASSWORD, 0, info.password },
164 { _T(""), CT_END_OF_LIST, 0, 0, 0, 0, NULL }
165 };
166
167 // Init db driver
168 #ifdef _WIN32
169 g_driverHandle = DBLoadDriver(_T("oracle.ddr"), NULL, TRUE, NULL, NULL);
170 #else
171 g_driverHandle = DBLoadDriver(LIBDIR "/libnxddr_oracle.so", NULL, TRUE, NULL, NULL);
172 #endif
173 if (g_driverHandle == NULL)
174 {
175 AgentWriteLog(EVENTLOG_ERROR_TYPE, _T("%s: failed to load db driver"), MYNAMESTR);
176 result = FALSE;
177 }
178
179 if (result)
180 {
181 g_shutdownCondition = ConditionCreate(TRUE);
182 }
183
184 // Load configuration from "oracle" section to allow simple configuration
185 // of one database without XML includes
186 memset(&info, 0, sizeof(info));
187 g_dbCount = -1;
188 if (config->parseTemplate(_T("ORACLE"), configTemplate))
189 {
190 if (info.name[0] != 0)
191 {
192 if (info.id[0] == 0)
193 _tcscpy(info.id, info.name);
194 memcpy(&g_dbInfo[++g_dbCount], &info, sizeof(DatabaseInfo));
195 g_dbInfo[g_dbCount].accessMutex = MutexCreate();
196 }
197 }
198
199 // Load full-featured XML configuration
200 if (g_dbCount == -1) // Didn't load anything from the .conf file
201 {
202 for (i = 1; result && i <= MAX_DATABASES; i++)
203 {
204 TCHAR section[MAX_STR];
205 memset((void*)&info, 0, sizeof(info));
206 _sntprintf(section, MAX_STR, _T("oracle/databases/database#%d"), i);
207 if ((result = config->parseTemplate(section, configTemplate)) != TRUE)
208 {
209 AgentWriteLog(EVENTLOG_ERROR_TYPE, _T("%s: error parsing configuration template"), MYNAMESTR);
210 return FALSE;
211 }
212 if (info.name[0] != _T('\0'))
213 memcpy((void*)&g_dbInfo[++g_dbCount], (void*)&info, sizeof(info));
214 else
215 continue;
216 if (info.username[0] == '\0' || info.password[0] == '\0')
217 {
218 AgentWriteLog(EVENTLOG_ERROR_TYPE, _T("%s: error getting username and/or password for "), MYNAMESTR);
219 result = FALSE;
220 }
221 if (result && (g_dbInfo[g_dbCount].accessMutex = MutexCreate()) == NULL)
222 {
223 AgentWriteLog(EVENTLOG_ERROR_TYPE, _T("%s: failed to create mutex (%d)"), MYNAMESTR, i);
224 result = FALSE;
225 }
226 }
227 }
228
229 // Exit if no usable configuration found
230 if (result && g_dbCount < 0)
231 {
232 AgentWriteLog(EVENTLOG_ERROR_TYPE, _T("%s: no databases to monitor"), MYNAMESTR);
233 result = FALSE;
234 }
235
236 // Run query thread for each database configured
237 for (i = 0; result && i <= g_dbCount; i++)
238 {
239 g_dbInfo[i].queryThreadHandle = ThreadCreateEx(queryThread, 0, CAST_TO_POINTER(i, void *));
240 }
241
242 return result;
243 }
244
245
246 //
247 // Shutdown handler
248 //
249
250 static void SubAgentShutdown(void)
251 {
252 AgentWriteLog(EVENTLOG_INFORMATION_TYPE, _T("%s: shutting down"), MYNAMESTR);
253 ConditionSet(g_shutdownCondition);
254 for (int i = 0; i <= g_dbCount; i++)
255 {
256 ThreadJoin(g_dbInfo[i].queryThreadHandle);
257 MutexDestroy(g_dbInfo[i].accessMutex);
258 }
259 ConditionDestroy(g_shutdownCondition);
260 }
261
262 //
263 // Figure out Oracle DBMS version
264 //
265
266 static int getOracleVersion(DB_HANDLE handle)
267 {
268 TCHAR versionString[32];
269
270 DB_RESULT result = DBSelect(handle,_T("select version from v$instance"));
271 if (result == NULL)
272 {
273 AgentWriteLog(EVENTLOG_WARNING_TYPE, _T("%s: query from v$instance failed"), MYNAMESTR);
274 return 700; // assume Oracle 7.0 by default
275 }
276
277 DBGetField(result, 0, 0, versionString, 32);
278 int major = 0, minor = 0;
279 _stscanf(versionString, _T("%d.%d"), &major, &minor);
280 DBFreeResult(result);
281
282 return major * 100 + minor * 10;
283 }
284
285 //
286 // Thread for SQL queries
287 //
288
289 THREAD_RESULT THREAD_CALL queryThread(void* arg)
290 {
291 int dbIndex = CAST_FROM_POINTER(arg, int);
292 DatabaseInfo& db = g_dbInfo[dbIndex];
293 const DWORD pollInterval = 60 * 1000L; // 1 minute
294 int waitTimeout;
295 QWORD startTimeMs;
296 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
297
298 while (true)
299 {
300 db.handle = DBConnect(g_driverHandle, db.name, NULL /* db.server */, db.username, db.password, NULL, errorText);
301 DBEnableReconnect(db.handle, false);
302 if (db.handle != NULL)
303 {
304 AgentWriteLog(EVENTLOG_INFORMATION_TYPE, _T("%s: connected to DB '%s'"), MYNAMESTR, db.name);
305 db.connected = true;
306 db.version = getOracleVersion(db.handle);
307 }
308
309 while (db.connected)
310 {
311 startTimeMs = GetCurrentTimeMs();
312
313 // Do queries
314 if (!(db.connected = getParametersFromDB(dbIndex)))
315 {
316 break;
317 }
318
319 waitTimeout = pollInterval - DWORD(GetCurrentTimeMs() - startTimeMs);
320 if (ConditionWait(g_shutdownCondition, waitTimeout < 0 ? 1 : waitTimeout))
321 goto finish;
322 }
323
324 // Try to reconnect every 30 secs
325 if (ConditionWait(g_shutdownCondition, DWORD(30 * 1000)))
326 break;
327 }
328
329 finish:
330 if (db.connected && db.handle != NULL)
331 {
332 DBDisconnect(db.handle);
333 }
334
335 return THREAD_OK;
336 }
337
338
339 bool getParametersFromDB( int dbIndex )
340 {
341 bool ret = true;
342 DatabaseInfo& info = g_dbInfo[dbIndex];
343
344 if (!info.connected)
345 {
346 return false;
347 }
348
349 MutexLock(info.accessMutex);
350
351 for (int i = 0; g_paramGroup[i].prefix; i++)
352 {
353 AgentWriteDebugLog(7, _T("%s: got entry for '%s'"), MYNAMESTR, g_paramGroup[i].prefix);
354
355 if (g_paramGroup[i].version > info.version) // this parameter group is not supported for this DB
356 continue;
357
358 // Release previously allocated array of values for this group
359 for (int j = 0; g_paramGroup[i].values[dbIndex] && j < g_paramGroup[i].valueCount[dbIndex]; j++)
360 delete (g_paramGroup[i].values[dbIndex])[j].attrs;
361 safe_free_and_null(g_paramGroup[i].values[dbIndex]);
362
363 DB_RESULT queryResult = DBSelect(info.handle, g_paramGroup[i].query);
364 if (queryResult == NULL)
365 {
366 ret = false;
367 break;
368 }
369
370 int rows = DBGetNumRows(queryResult);
371 g_paramGroup[i].values[dbIndex] = (DBParameter*)malloc(sizeof(DBParameter) * rows);
372 g_paramGroup[i].valueCount[dbIndex] = rows;
373 for (int j = 0; j < rows; j++)
374 {
375 TCHAR colname[MAX_STR];
376 DBGetField(queryResult, j, 0, (g_paramGroup[i].values[dbIndex])[j].name, MAX_STR);
377 (g_paramGroup[i].values[dbIndex])[j].attrs = new StringMap;
378 for (int k = 1; DBGetColumnName(queryResult, k, colname, MAX_STR); k++)
379 {
380 TCHAR colval[MAX_STR];
381 DBGetField(queryResult, j, k, colval, MAX_STR);
382 // AgentWriteDebugLog(9, _T("%s: getParamsFromDB: colname '%s' ::: colval '%s'"), MYNAMESTR, colname, colval);
383 (g_paramGroup[i].values[dbIndex])[j].attrs->set(colname, colval);
384 }
385 }
386
387 DBFreeResult(queryResult);
388 }
389
390 MutexUnlock(info.accessMutex);
391
392 return ret;
393 }
394
395 //
396 // Subagent information
397 //
398
399
400 static NETXMS_SUBAGENT_PARAM m_parameters[] =
401 {
402 { _T("Oracle.Sessions.Count(*)"), getParameters, "X", DCI_DT_INT, _T("Oracle/Sessions: Number of sessions opened") },
403 { _T("Oracle.Cursors.Count(*)"), getParameters, "X", DCI_DT_INT, _T("Oracle/Cursors: Current number of opened cursors systemwide") },
404 { _T("Oracle.DBInfo.IsReachable(*)"), getParameters, "R", DCI_DT_STRING, _T("Oracle/Info: Database is reachable") },
405 { _T("Oracle.DBInfo.Name(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Info: Database name") },
406 { _T("Oracle.DBInfo.CreateDate(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Info: Database creation date") },
407 { _T("Oracle.DBInfo.LogMode(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Info: Database log mode") },
408 { _T("Oracle.DBInfo.OpenMode(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Info: Database open mode") },
409 { _T("Oracle.TableSpaces.Status(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Tablespaces: Status") },
410 { _T("Oracle.TableSpaces.Type(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Tablespaces: Type") },
411 { _T("Oracle.TableSpaces.UsedPct(*)"), getParameters, "X", DCI_DT_INT, _T("Oracle/Tablespaces: Percentage used") },
412 { _T("Oracle.Instance.Version(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Instance: DBMS Version") },
413 { _T("Oracle.Instance.Status(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Instance: Status") },
414 { _T("Oracle.Instance.ArchiverStatus(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Instance: Archiver status") },
415 { _T("Oracle.Instance.ShutdownPending(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/Instance: Is shutdown pending") },
416 { _T("Oracle.CriticalStats.TSOffCount(*)"), getParameters, "X", DCI_DT_INT, _T("Oracle/CriticalStats: Number of offline tablespaces") },
417 { _T("Oracle.CriticalStats.DFOffCount(*)"), getParameters, "X", DCI_DT_INT, _T("Oracle/CriticalStats: Number of offline datafiles") },
418 { _T("Oracle.CriticalStats.FullSegmentsCount(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/CriticalStats: Number of segments that cannot extend") },
419 { _T("Oracle.CriticalStats.RBSegsNotOnlineCount(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/CriticalStats: Number of rollback segments not online") },
420 { _T("Oracle.CriticalStats.AutoArchivingOff(*)"), getParameters, "X", DCI_DT_STRING, _T("Oracle/CriticalStats: Archive logs enabled but auto archiving off ") },
421 { _T("Oracle.CriticalStats.DatafilesNeedMediaRecovery(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/CriticalStats: Number of datafiles that need media recovery") },
422 { _T("Oracle.CriticalStats.FailedJobs(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/CriticalStats: Number of failed jobs") },
423 { _T("Oracle.Dual.ExcessRows(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Dual: Excessive rows") },
424 { _T("Oracle.Performance.PhysReads(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Performance: Number of physical reads") },
425 { _T("Oracle.Performance.LogicReads(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Performance: Number of logical reads") },
426 { _T("Oracle.Performance.CacheHitRatio(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Performance: Data buffer cache hit ratio") },
427 { _T("Oracle.Performance.LibCacheHitRatio(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Performance: Library cache hit ratio") },
428 { _T("Oracle.Performance.DictCacheHitRatio(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Performance: Dictionary cache hit ratio") },
429 { _T("Oracle.Performance.RollbackWaitRatio(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Performance: Ratio of waits for requests to rollback segments") },
430 { _T("Oracle.Objects.InvalidCount(*)"), getParameters, "X", DCI_DT_INT64, _T("Oracle/Objects: Number of invalid objects in DB") }
431 };
432
433 /*
434 static NETXMS_SUBAGENT_ENUM m_enums[] =
435 {
436 };
437 */
438
439 static NETXMS_SUBAGENT_INFO m_info =
440 {
441 NETXMS_SUBAGENT_INFO_MAGIC,
442 _T("ORACLE"), NETXMS_VERSION_STRING,
443 SubAgentInit, SubAgentShutdown, NULL,
444 sizeof(m_parameters) / sizeof(NETXMS_SUBAGENT_PARAM), m_parameters,
445 0, NULL,
446 /*sizeof(m_parameters) / sizeof(NETXMS_SUBAGENT_PARAM),
447 m_parameters,
448 sizeof(m_enums) / sizeof(NETXMS_SUBAGENT_ENUM),
449 m_enums,*/
450 0, NULL
451 };
452
453
454 //
455 // Entry point for NetXMS agent
456 //
457
458 DECLARE_SUBAGENT_ENTRY_POINT(ORACLE)
459 {
460 *ppInfo = &m_info;
461 return TRUE;
462 }
463
464
465 //
466 // DLL entry point
467 //
468
469 #ifdef _WIN32
470
471 BOOL WINAPI DllMain(HINSTANCE hInstance, DWORD dwReason, LPVOID lpReserved)
472 {
473 if (dwReason == DLL_PROCESS_ATTACH)
474 DisableThreadLibraryCalls(hInstance);
475 return TRUE;
476 }
477
478 #endif