January 10, 2017 at 9:25 am
Hi All,
I've inherited a database that contains VMWare server information such as CPU, RAM, Disks, etc.
And I need to run a monthly report that shows the server spec for each VM.
VCenter is polled for data every hour and the info on the VMs is recorded in a SQL Server database.
So at the end of each month i've got approx. 700 rows of data - CPU, RAM, etc - for each VM and I need to report on the largest value for each column.
This is because, If for example a department increases RAM from 32Gb to 64Gb they will be charged for the larger value for the entire month.
Simple enough, where it gets tricky is for the storage info which is kept in a separate table.
Each VM has an ID so you can identify which disks are associated to it, however the data for each is disk is recorded in separate rows.
So for example, a VM could have 6 disks associated with it. So every hour 6 rows of data are inserted into the db for that VM showing the disk size & storage tier.
I want the report to show the max values for all data items on a single line (rather than have 6 lines for each server where the only data changing is the disk size).
This is not a problem for CPU, RAM, etc but it order to get the disk info on a single line, i'm guessing the simplest way is to use PIVOT ?
Where i'm getting confused is how I can join my server and disk info tables together when one needs pivoting.
So if I query table A for server info i get back
VM_IDServerNameCPURAMPLATFORMReplicated
1 MyServer 448WindowsY
If I query TABLE B I get
VM_IDDISK_IDSizeTier
1 1234/15002
1 1234/210242
1 1234/34002
The end result should look like:
VM_IDServerNameCPURAMPLATFORMReplicatedDisk 1 Size Disk 1 TierDisk 2 Size Disk 2 TierDisk 3 SizeDisk 3 Tier
1 MyServer1 448WindowsY500 2 1024 2 4002
So I need a report that shows 1 line for each server with the max value for each item displayed for that month (except tier which will be min value).
Also, as disks can be added, it needs to be dynamic to pick up additional disk ID's as they appear.
Any suggestions on the best way to achieve this?
Many thanks!
January 10, 2017 at 9:39 am
Just pivot your table inside a CTE or subquery (derived table) before joining to the other table.
January 11, 2017 at 8:08 am
Thanks for the reply.
However the requirement has now changed...
Rather than having the server and disk data displayed together they now need to be on separate lines.
Just to re-iterate...
I have 2 tables, 1 for server info and 1 for disk info.
Server info has columns like Server Name, CPU, RAM, etc and disk info has columns like Size & Tier.
The server info is all in 1 row but the disk info will have a row for each disk.
There is an ID column I can join the 2 tables with.
I need to write a query which displays all the server info for each server on one line with the associated disk info below it for all the servers in the table.
Eg:
ServerCPURAMO/S
MyServer1 432Windows
DiskIDSizeTier
1234/15002
1234/22502
1234/310242
ServerCPURAMO/S
MyServer2 14Linux
DiskIDSizeTier
5678/11002
5678/22502
etc, etc...
I'm just having a brain fart today and can't get my head around it.
Any suggestions on how best to do this?
January 11, 2017 at 8:20 am
Given that a query returns a result set, and you're looking for two result sets per server, this isn't really possible. Certainly it isn't easy - you're trying to use a query as a solution to a presentational problem. You'd be much better off using a tool such as Reporting Services or Excel to pivot your data for you.
John
January 11, 2017 at 8:20 am
Stueyd (1/11/2017)
Thanks for the reply.However the requirement has now changed...
Rather than having the server and disk data displayed together they now need to be on separate lines.
Just to re-iterate...
I have 2 tables, 1 for server info and 1 for disk info.
Server info has columns like Server Name, CPU, RAM, etc and disk info has columns like Size & Tier.
The server info is all in 1 row but the disk info will have a row for each disk.
There is an ID column I can join the 2 tables with.
I need to write a query which displays all the server info for each server on one line with the associated disk info below it for all the servers in the table.
Eg:
ServerCPURAMO/S
MyServer1 432Windows
DiskIDSizeTier
1234/15002
1234/22502
1234/310242
ServerCPURAMO/S
MyServer2 14Linux
DiskIDSizeTier
5678/11002
5678/22502
etc, etc...
I'm just having a brain fart today and can't get my head around it.
Any suggestions on how best to do this?
This sounds more like a "presentation" thing. I would suggest you just write a normal query that will have all the data for the server in every record, and each record will only differ by virtue of which disk data it has. Then you can use SSRS to do the prettying up it needs to look that way. Look at either a tablix or matrix to do the job within SSRS, and you may not even need either one if you do the grouping on the server level.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 11, 2017 at 8:50 am
sgmunson (1/11/2017)
This sounds more like a "presentation" thing. I would suggest you just write a normal query that will have all the data for the server in every record, and each record will only differ by virtue of which disk data it has. Then you can use SSRS to do the prettying up it needs to look that way. Look at either a tablix or matrix to do the job within SSRS, and you may not even need either one if you do the grouping on the server level.
I was just going to post something very similar. So this is another vote for this option.
January 11, 2017 at 8:55 am
Thanks guys, that sounds like a plan. I'll try it !
January 19, 2017 at 8:57 am
Quick update in case anyone was wondering.
The last 3 comments were spot on.
SSRS came to the rescue and I was able to grab all the data with a query and then chop it up and display it as required.
Thanks for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply