[inside the SQL db]Custom reports
I'm trying to configure monitoring for our render farm. Render farm is a set of servers that runs exactly the same set of software. I need to obtain current TOTAL utilization of render farm (sum of all current CPUs utilization for all the servers / number of servers), total utilization of specified app on the farm (sum of process CPU utilization for every server / number of servers) and statistics for specified server: history of CPU utilization for specified period of time and history of CPU utilization for every process for the same period of time.
All this statistics must be a kind of widget/dashboard - first "page" current values for total utilization, CPU utilization for every server and CPU utilization by every app. Clicking on application we get the page with history graph for this app (both TOTAL and FOR EVERY server), clicking on server CPU utilization - graph of CPU utilization and app CPU utilization for every server for specified period of time. Clicking on TOTAL CPU - graph of CPU utilization for all the servers for period of time.
There is no way to make:
a) Many parameters on one graph
b) widget with process CPU utilization graph
so i did an external "reporter". A read this topic:
http://forums.manageengine.com/topic/crystal-reports-with-opmanager
and still have no idea where CURRENT CPU utilization is stored: it is not in STATSDATA_HOURLY obtained from MetaTable where ENDTIME=-1 (values from this table are updated once an hour), nor in STATSDATAmonth_day_year table - data for CPU utilization is also updated once an hour.
What exactly i do is:
a) select NAME, ID, AGENT from polleddata where AGENT like "%serversname%" and NAME like "%CPU%" ORDER BY AGENT
so i get ID of all the render farm servers
b) then for every ID i do
select POLLID, INSTANCE, TTIME, VAL from THE_TABLE_WITH_DATA where POLLID="ID" and TTIME=(select MAX(TTIME) from THE_TABLE_WITH_DATA where POLLID="ID")
so a get all the CPU usage data (for multiprocessor systems i count average cpu utilization)
But data obtained this way updates only once an our (i see this by TTIME changes).
Where is table with current data?
I also tried "hardcore" way to obtain data:
a) download HTML from (example to get CPU utilization for 30 days for one server)
http://testopman2:8080/devices/dynamicGraph.do?name=render01&graphName=WMI-CPUUtilization&snapshot=&requestid=DynamicGraph&Instance=&oid=CPU+Utilization&chartType=AREA&pollId=&id=&isOpm7PPMApplied=&isVmware=&index=&instanceSpecific=&vmName=&column=&networkId=&storageId=&displayName=Render01&domainRange=&timePeriod=Last_30_Days&showReport=&reqUserName=admin&reqPassword=adminpassword
b) parse HTML and get the table and get data
but this way makes VERY high load on server
All this statistics must be a kind of widget/dashboard - first "page" current values for total utilization, CPU utilization for every server and CPU utilization by every app. Clicking on application we get the page with history graph for this app (both TOTAL and FOR EVERY server), clicking on server CPU utilization - graph of CPU utilization and app CPU utilization for every server for specified period of time. Clicking on TOTAL CPU - graph of CPU utilization for all the servers for period of time.
There is no way to make:
a) Many parameters on one graph
b) widget with process CPU utilization graph
so i did an external "reporter". A read this topic:
http://forums.manageengine.com/topic/crystal-reports-with-opmanager
and still have no idea where CURRENT CPU utilization is stored: it is not in STATSDATA_HOURLY obtained from MetaTable where ENDTIME=-1 (values from this table are updated once an hour), nor in STATSDATAmonth_day_year table - data for CPU utilization is also updated once an hour.
What exactly i do is:
a) select NAME, ID, AGENT from polleddata where AGENT like "%serversname%" and NAME like "%CPU%" ORDER BY AGENT
so i get ID of all the render farm servers
b) then for every ID i do
select POLLID, INSTANCE, TTIME, VAL from THE_TABLE_WITH_DATA where POLLID="ID" and TTIME=(select MAX(TTIME) from THE_TABLE_WITH_DATA where POLLID="ID")
so a get all the CPU usage data (for multiprocessor systems i count average cpu utilization)
But data obtained this way updates only once an our (i see this by TTIME changes).
Where is table with current data?
I also tried "hardcore" way to obtain data:
a) download HTML from (example to get CPU utilization for 30 days for one server)
http://testopman2:8080/devices/dynamicGraph.do?name=render01&graphName=WMI-CPUUtilization&snapshot=&requestid=DynamicGraph&Instance=&oid=CPU+Utilization&chartType=AREA&pollId=&id=&isOpm7PPMApplied=&isVmware=&index=&instanceSpecific=&vmName=&column=&networkId=&storageId=&displayName=Render01&domainRange=&timePeriod=Last_30_Days&showReport=&reqUserName=admin&reqPassword=adminpassword
b) parse HTML and get the table and get data
but this way makes VERY high load on server