August 16, 2003 at 10:11 am
In a db that stores disk information, is it possible to return the machine name once and all the logical drives instead of the first field being repeated for every logical drive?
Below is the query I use where I would like to return the computer name once and all the logical drives associated with it!
Thanks in advance
Select Distinct
Sys.Name0 As [Machine Name],
Ldisk.DeviceID0 As [Drive],
Case
When Ldisk.VolumeName0 Is Null
Then ''
Else Ldisk.VolumeName0
End As [Vol Name],
Ldisk.FileSYStem0 As [File System],
Ldisk.Size0 As [Drive Size],
Ldisk.FreeSpace0 As [Free Space]
From
v_R_System Sys,
v_GS_Logical_Disk Ldisk
Right Outer Join v_HS_Logical_Disk Lhdisk On Ldisk.ResourceID = Lhdisk.ResourceID,
Where
Sys.Resourceid = Ldisk.ResourceID
And Sys.ResourceID = Opsys.ResourceID
And Sys.Operating_System_Name_And0 Like '%server%'
And Ldisk.DriveType0 = '3'
Order By
Sys.Name0, Ldisk.DeviceID0
August 16, 2003 at 3:43 pm
Can you supply a short example of what the output looks like now, and how you'd prefer it?
Cheers,
- Mark
Cheers,
- Mark
August 17, 2003 at 7:23 am
Thanks Mark,
Here is the output:
Name Drive Vol Name FS Disk Size Part Size Free Time Taken
SRV1C:NTFS1735114362143852003-07-15 20:15:53.000
SRV1C:NTFS1735114362143642003-07-31 05:18:48.000
SRV2C:NTFS2502100110692003-07-14 22:50:59.000
SRV2C:NTFS250210019992003-07-30 18:52:23.000
SRV2E:DataNTFS1592145415002003-07-14 22:50:59.000
SRV2E:DataNTFS1592145414792003-07-30 18:52:23.000
What I'd like is:
Name Disk DiskSize Drive Vol Name FS PartSize Free Time Taken
SRV1 0 17351 C: OS NTFS 14362 14385 2003-07-15 20:15:53.000
14364 2003-07-31 05:18:48.000
SRV2 0 2502 C: OS NTFS 1001 1069 2003-07-14 22:50:59.000
999 2003-07-30 18:52:23.000
D: LOG NTFS 1400 1368 2003-07-14 22:50:59.000
1200 2003-07-30 18:52:23.000
1 1592 E: DAT NTFS 1454 1500 2003-07-14 22:50:59.000
1479 2003-07-30 18:52:23.000
Does it make sense? and is it possible?
Regards
August 17, 2003 at 7:45 am
Sorry about the formatting!
NameDriveVolFSSizePsizeFreeDate
SRV1D:AppsNTFS1735817246172412003-07-15 20:15:53.000
F:LogsNTFS9999990398962003-07-15 20:15:53.000
98932003-07-31 05:18:48.000
SRV2E:DataNTFS1592145415002003-07-14 22:50:59.000
14792003-07-30 18:52:23.000
F:AppsNTFS2046129813382003-07-14 22:50:59.000
13172003-07-30 18:52:23.000
August 17, 2003 at 10:42 am
Here's a script that formats as you request
/** SCRIPT **/
USE TEMPDB
GO
CREATE TABLE TESTFORMAT (FA VARCHAR(10), FB VARCHAR(10), FC VARCHAR(10), FD VARCHAR(10))
INSERT TESTFORMAT (FA, FB, FC, FD) VALUES ('A', 'A1', 'A2', 'AA')
INSERT TESTFORMAT (FA, FB, FC, FD) VALUES ('A', 'A1', 'A3', 'AC')
INSERT TESTFORMAT (FA, FB, FC, FD) VALUES ('A', 'A1', 'A3', 'AB')
INSERT TESTFORMAT (FA, FB, FC, FD) VALUES ('A', 'A1', 'A7', 'AD')
INSERT TESTFORMAT (FA, FB, FC, FD) VALUES ('B', 'B1', 'B2', 'AE')
INSERT TESTFORMAT (FA, FB, FC, FD) VALUES ('B', 'B1', 'B4', 'BX')
SELECT * FROM TESTFORMAT ORDER BY FA ASC, FB ASC, FC ASC, FD ASC
SELECT
FA =
(CASE A.FA WHEN
(SELECT TOP 1 FA
FROM TESTFORMAT
WHERE FA = A.FA AND FB <= A.FB AND FC < A.FC AND FD < A.FD
ORDER BY FA DESC, FB DESC, FC DESC, FD DESC)
THEN ''
ELSE A.FA END),
FB =
(CASE A.FB WHEN
(SELECT TOP 1 FB
FROM TESTFORMAT
WHERE FA = A.FA AND FB <= A.FB AND FC < A.FC AND FD < A.FD
ORDER BY FA DESC, FB DESC, FC DESC, FD ASC)
THEN ''
ELSE A.FB END),
FC =
(CASE A.FC WHEN
(SELECT TOP 1 FC
FROM TESTFORMAT
WHERE FA = A.FA AND FB = A.FB AND FC <= A.FC AND FD < A.FD
ORDER BY FA DESC, FB DESC, FC DESC, FD DESC)
THEN ''
ELSE A.FC END),
FD
FROM (SELECT TOP 100 PERCENT FA, FB, FC, FD FROM TESTFORMAT ORDER BY FA ASC, FB ASC, FC ASC, FD ASC) AS A
DROP TABLE TESTFORMAT
/** END OF SCRIPT **/
The logic is to sort the data ascending then for each field find the value of the previous record and compare. You will notice the condition is for each previous field I test for equality. For the current field I test for it being less than or equal and for subsequent fields I test for less than.
HTH
Navin
Edited by - nparray on 08/17/2003 10:51:36 AM
Navin Parray
August 17, 2003 at 10:53 am
Navin,
Excuse my ignorance! I am new to all of the SQL stuff. Should I assume that the values used in the temporary table come from my sql statement?
Thanks
August 17, 2003 at 11:04 am
The script is simply an example of how this could be done. It creates a temporary table in the tempdb database and populates it with some test data. The select statement displays how to acheive you results
ALL DATA IN TABLE
FA FB FC FD
--------------------------
AA1A2AA
AA1A3AB
AA1A3AC
AA1A7AD
BB1B2AE
BB1B4BX
DATA AS REQUIRED
FA FB FC FD
--------------------------
AA1A2AA
A3AB
AC
A7AD
BB1B2AE
B4BX
Navin Parray
Navin Parray
August 18, 2003 at 3:04 am
A bit off-topic, but this kind of thing is more of a representation issue that is best solved using the client application. I know this is fairly easy done in .NET for example using a ListView.
As you see from the solution, it is possible to build it inside T-SQL, but it will be a solution with a relatively low performance.
August 18, 2003 at 6:03 pm
If I get this right, that works great for numbers that are either greater or smaller but what about fluctuating numbers?
Sometime the drive space could be less sometimes more!
Here is what I get:
MAILC:173511436214364Jul 31 2003 5:18AM
MAILC:173511436214385Jul 15 2003 8:15PM
D:173581724617241Jul 15 2003 8:15PM
E:173581725617256Jul 15 2003 8:15PM
F:999999039893Jul 31 2003 5:18AM
F:999999039896Jul 15 2003 8:15PM
G:735871737173Jul 15 2003 8:15PM
SMSC:250210011069Jul 14 2003 10:50PM
SMSC:25021001999Jul 30 2003 6:52PM
E:159214541479Jul 30 2003 6:52PM
E:159214541500Jul 14 2003 10:50PM
F:204612981317Jul 30 2003 6:52PM
F:204612981338Jul 14 2003 10:50PM
ZENDC01C:799342924334Jul 15 2003 8:47PM
ZENDC01D:999992089224Jul 15 2003 8:47PM
ZENDC01E:167011174611748Jul 15 2003 8:47PM
ZENDC01F:499949224922Jul 15 2003 8:47PM
ZENDC01G:297251208812049Jul 15 2003 8:47PM
ZENDC01W:999992089224Jul 15 2003 8:47PM
from this query:
If Exists(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '#server_disk_stats')
DROP TABLE #server_disk_stats
GO
Use DISK_DATA
Create Table #server_disk_stats
(data_sourcevarchar(20),
sys_namevarchar(20),
disk_idvarchar(4),
disk_sizevarchar(20),
disk_freevarchar(20),
hdisk_freevarchar(20),
hdisk_timevarchar(30)
)
Insert #server_disk_stats
Select Distinct 'Select',
Sys.Name0 As sys_name,
Ldisk.DeviceID0 As [Drive],
Ldisk.Size0 As [Drive Size],
Ldisk.FreeSpace0 As [Free Space],
Lhdisk.FreeSpace0 As [History],
Lhdisk.TimeStamp As [Time]
From
v_R_System Sys,
v_GS_Logical_Disk Ldisk,
v_HS_Logical_Disk Lhdisk,
v_GS_Operating_System Opsys
Where
Sys.ResourceID = Ldisk.ResourceID
And Sys.ResourceID = Opsys.ResourceID
And Sys.Operating_System_Name_And0 Like '%server%'
And Ldisk.DriveType0 = '3'
And Ldisk.ResourceID = Lhdisk.ResourceID
And Ldisk.DeviceID0 = Lhdisk.DeviceID0
Order By
Sys.Name0, Ldisk.DeviceID0, Lhdisk.TimeStamp
Select * from #server_disk_stats
Select
sys_name =(Case A.sys_name When
(Select Top 1 sys_name
From #server_disk_stats
Where sys_name = A.sys_name And disk_id <= A.disk_id And disk_size <> A.disk_size And disk_free <> A.disk_free And hdisk_free <> A.hdisk_free And hdisk_time <> A.hdisk_time
Order By sys_name Desc, disk_id Desc, disk_size Desc, disk_free Desc, hdisk_free Desc, hdisk_time Desc)
Then ''
Else A.sys_name
End),
disk_id = (Case A.disk_id When
(Select Top 1 disk_id
From #server_disk_stats
Where sys_name = A.sys_name And disk_id <= A.disk_id And disk_size <> A.disk_size And disk_free <> A.disk_free And hdisk_free <> A.hdisk_free And hdisk_time <> A.hdisk_time
Order By sys_name Desc, disk_id Desc, disk_size Desc, disk_free Desc, hdisk_free Desc, hdisk_time Desc)
Then ''
Else A.disk_id
End),
disk_size = (Case A.disk_size When
(Select Top 1 disk_size
From #server_disk_stats
Where sys_name = A.sys_name And disk_id <= A.disk_id And disk_size <> A.disk_size And disk_free <> A.disk_free And hdisk_free <> A.hdisk_free And hdisk_time <> A.hdisk_time
Order By sys_name Desc, disk_id Desc, disk_size Desc, disk_free Desc, hdisk_free Desc, hdisk_time Desc)
Then ''
Else A.disk_size
End),
disk_free = (Case A.disk_free When
(Select Top 1 disk_free
From #server_disk_stats
Where sys_name = A.sys_name And disk_id <= A.disk_id And disk_size <> A.disk_size And disk_free <> A.disk_free And hdisk_free <> A.hdisk_free And hdisk_time <> A.hdisk_time
Order By sys_name Desc, disk_id Desc, disk_size Desc, disk_free Desc, hdisk_free Desc, hdisk_time Desc)
Then ''
Else A.disk_free
End),
hdisk_free = (Case A.hdisk_free When
(Select Top 1 hdisk_free
From #server_disk_stats
Where sys_name = A.sys_name And disk_id <= A.disk_id And disk_size <> A.disk_size And disk_free <> A.disk_free And hdisk_free <> A.hdisk_free And hdisk_time <> A.hdisk_time
Order By sys_name Desc, disk_id Desc, disk_size Desc, disk_free Desc, hdisk_free Desc, hdisk_time Desc)
Then ''
Else A.hdisk_free
End),
hdisk_time = (Case A.hdisk_time When
(Select Top 1 hdisk_time
From #server_disk_stats
Where sys_name = A.sys_name And disk_id <= A.disk_id And disk_size <> A.disk_size And disk_free <> A.disk_free And hdisk_free <> A.hdisk_free And hdisk_time <> A.hdisk_time
Order By sys_name Desc, disk_id Desc, disk_size Desc, disk_free Desc, hdisk_free Desc, hdisk_time Desc)
Then ''
Else A.hdisk_time
End)
From
(Select Top 100 Percent sys_name, disk_id, disk_size, disk_free, hdisk_free, hdisk_time
From #server_disk_stats
Order By sys_name Asc, disk_id Asc, disk_size Asc, disk_free Asc, hdisk_free Asc, hdisk_time Asc) As A
Drop Table #server_disk_stats
Thanks for all your help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply