creating advanced SQL to generate report application

zazaz

hi all,

I want to ask about sql advanced reporting. I want to create a report with a format like this :

 

Node_ID -- node_name --- Department  --- CPU -- Memory -- HDD --- App1 -- App2 -- App3

1342            myserver                 IT              40%      20%       40%     100%    0%     90%

 

Information :

CPU                   : avg of cpu used on 1 day

Memory              : avg of memory used on 1 day

Disk                    : avg of disk used until today

Application 1      : avg availability of application ( ex : Windows 2003-2008 and the server is never down)

Application 2      : avg availability of application ( ex : Oracle service with never goes up)

Application 3      : avg availability of application ( ex : notepad monitor with sometimes up)

etc

 

i already tried to create using orion report writer, but it's to simple to create report like this.

all report are generate everyday ( 1 day duration), but I'm not very proficient in the database, so i confused when creating it. my friend told me to used PIVOT to create it, but i don't understand how to do it.

 

thanks

qle

To be clear, CPU, memory and disk refer to information about the node and not the application, right?

I'm able to create a report that looks something like you've requested. However, since the applications are associated with the node, they won't appear as columns in the report but instead as rows. Therefore, it'll look more like

Node ID	Node Name	CPU	Memory	Disk	Application	App Availability
1234	Server	40%	20%	40%	Windows Server	100%
1234	Server	40%	20%	40%	Oracle	0%

As you can see, the first five columns are repeated. You could group by NodeID and Node Name to try it clean it up a bit but you'll still be repeating the CPU, Memory and Disk statistics. Are you ok with a report with this layout?