June 15, 2016 at 6:05 am
Good day experts,
I have two sql quiries that i want to combine so that they can bring one result.Please help how can i do that.The quiries are as follows
A) select
a.SiteName,
a.Hostname,
a.FreeSpace,
a.FreeSpacePerc,
a.Color,
b.TotalMemory,
'ProLiant DL585 G7' as Model
from
(
SELECT
[SiteName],
[Hostname],
[SystemDriveName] as DriveName,
cast([SystemFreeSpace] as nvarchar(10)) + ' GB (' + [SystemDriveName] + ':)' as FreeSpace,
cast(([SystemFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [SystemDriveName] + ':)' as FreeSpacePerc,
case when [SystemFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
union
select * from
(
SELECT
[SiteName],
[Hostname],
[DataDriveName],
cast([DataFreeSpace] as nvarchar(10)) + ' GB (' + [DataDriveName] + ':)' as FreeSpace,
cast(([DataFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [DataDriveName] + ':)' as FreeSpacePerc,
case when [DataFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
union
SELECT
[SiteName],
[Hostname],
[BackupDriveName],
cast([BackupFreeSpace] as nvarchar(10)) + ' GB (' + [BackupDriveName] + ':)' as FreeSpace,
cast(([BackupFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [BackupDriveName] + ':)' as FreeSpacePerc,
case when [BackupFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
Union
SELECT
[SiteName],
[Hostname],
[Backup2DriveName],
cast([Backup2FreeSpace] as nvarchar(10)) + ' GB (' + [Backup2DriveName] + ':)' as FreeSpace,
cast(([Backup2FreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [Backup2DriveName] + ':)' as FreeSpacePerc,
case when [BackupFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color
FROM [FednaDBA].[dbo].[HostDriveStatus]
where
active = 1 and
Hostname like 'AC%'
) as a
) as a
join
(
SELECT
[SiteName],
[Hostname],
[TotalMemory] + ' GB' as TotalMemory
FROM [FednaDBA].[dbo].[HostMemoryCPUStatus]
where
active = 1 and
Hostname like 'AC%'
) as b on a.SiteName = a.SiteName and a.Hostname = b.Hostname
order by
a.[SiteName], DriveName
B) SELECT
[SiteName],
[Hostname],
[SystemDriveName] as DriveName,
cast([SystemFreeSpace] as nvarchar(10)) + ' GB (' + [SystemDriveName] + ':)' as FreeSpace,
cast(([SystemFreeSpacePercentage]*100) as nvarchar(10)) + '% (' + [SystemDriveName] + ':)' as FreeSpacePerc,
cast([Memory] as nvarchar(10)) + ' GB ' as Memory,
case when [SystemFreeSpace] <= 20 then 'RED' else 'BLACK' end as Color,
Createdon
FROM [FednaDBA].[dbo].[DriveStatus_Solarwinds]
where
--active = 1 and
Hostname like 'AC%'and Createdon >= DATEADD(day, DATEDIFF(day,0,GETDATE()),0)
Thank you in advance
June 15, 2016 at 7:23 am
They return the following infomation:
SiteName HostName Freespace FreeSpacePerc Color TotalMemory Model
Kriel ACZADRF 62.34 GB(D) 39%(D:) Black 64.GB Proliant DL585 G7
Isibonelo ACZALCD 4902.19 GB (F) 89% (F:)BLACK 160 GB ProLiant DL585 G7
Mafube ACZAHEN 36.53 GB (C:) 27.00% (C:)BLACK 160.00 GB ProLiant DL585 G7
I hope its clear enough to understand
June 15, 2016 at 8:08 am
tmmutsetse (6/15/2016)
They return the following infomation:SiteName HostName Freespace FreeSpacePerc Color TotalMemory Model
Kriel ACZADRF 62.34 GB(D) 39%(D:) Black 64.GB Proliant DL585 G7
Isibonelo ACZALCD 4902.19 GB (F) 89% (F:)BLACK 160 GB ProLiant DL585 G7
Mafube ACZAHEN 36.53 GB (C:) 27.00% (C:)BLACK 160.00 GB ProLiant DL585 G7
I hope its clear enough to understand
Was my first post unclear in some way?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 15, 2016 at 8:16 am
i am sure you wanted to see the results being returned by each query.I posted the results for one query and for the second query they are also returned in same format .
I appreciate your help man
June 15, 2016 at 8:24 am
tmmutsetse (6/15/2016)
i am sure you wanted to see the results being returned by each query.I posted the results for one query and for the second query they are also returned in same format .I appreciate your help man
OK, I guess you did not spot that the word 'here' in my initial post is a hyperlink to your other identical thread.
To keep things in one place, can I suggest that you post your additional info in that other thread rather than in this one?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply