March 28, 2011 at 9:13 pm
Hey All,
I am no DBA by any means, I just do adhoc basic queries.
Today I was given a task to pull a report for the following
"All computers that begin with ARO and include the following in the same report. Domain, User, IP, Office Version, and Lotus Notes Version."
I can't seem to be able to get all the info in a single query.
Here are the 2 tables I have to get information from.
Table 1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Inv_Computer](
[Guid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NULL,
[Domain] [nvarchar](64) NOT NULL,
[User] [nvarchar](64) NOT NULL,
127.0.0.1 [nvarchar](16) NOT NULL
) ON [PRIMARY]
GO
Insert into inv_computer values ('9EAC8860-3965-4B87-8884-597449333963','ARODUS1XP1RF1','mydomain','AROWHudson','10.249.138.95')
Insert into inv_computer values ('A6077480-8C50-4E8E-B0F1-606F93A4728E','ARODUS2VRG331','mydomain','AROKNox','10.48.40.198')
Insert into inv_computer values ('6A2F6CBC-168E-452D-8F3C-D9E454E3DA0E','ARODUS3V6SKC1','mydomain','AROTMason','10.249.138.121')
Insert into inv_computer values ('A0A142F9-F585-4356-BFFF-1967B957339F','ARODUS46BFK71','mydomain','ARODRodzen','10.224.149.191')
Insert into inv_computer values ('E2733611-154A-41CA-98F5-527FD081D1CE','ARODUS5CKKG81','mydomain','AROSacUPS','10.224.173.16')
Insert into inv_computer values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A8','ARODUS706VK81','mydomain','ARORaLopez','10.224.171.29')
Insert into inv_computer values ('E2733611-154A-41CA-98F5-527FD081D1Cf','HOLDUS5CKKG82','mydomain','HOLLust1','10.224.173.17')
Insert into inv_computer values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A9','HOLDUS706VK83','mydomain','HOLJuLopez','10.224.171.30')
Table 2
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Inv_Add_Remove_Programs](
[_ResourceGuid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Version] [nvarchar](64) NOT NULL
) ON [PRIMARY]
GO
Insert into Inv_Add_Remove_Programs values ('9EAC8860-3965-4B87-8884-597449333963','Lotus Notes','8.5.1')
Insert into Inv_Add_Remove_Programs values ('A6077480-8C50-4E8E-B0F1-606F93A4728E','Microsoft Office Standard 2003','11.0.8173.0')
Insert into Inv_Add_Remove_Programs values ('A6077480-8C50-4E8E-B0F1-606F93A4728E','Lotus Notes','8.5.1')
Insert into Inv_Add_Remove_Programs values ('6A2F6CBC-168E-452D-8F3C-D9E454E3DA0E','Microsoft Office Progessional 2007','12.0.4518.1014')
Insert into Inv_Add_Remove_Programs values ('A0A142F9-F585-4356-BFFF-1967B957339F','Microsoft Office Standard 2007','12.0.4518.1014')
Insert into Inv_Add_Remove_Programs values ('E2733611-154A-41CA-98F5-527FD081D1CE','Lotus Notes','7.0.2')
Insert into Inv_Add_Remove_Programs values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A8','Microsoft Office Standard 2003','11.0.8173.0')
Insert into Inv_Add_Remove_Programs values ('E2733611-154A-41CA-98F5-527FD081D1Cf','Microsoft Office Professional 2007','12.0.4518.1014')
Insert into Inv_Add_Remove_Programs values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A9','Microsoft Office Professional 2010','14.0.4763.1000')
Insert into Inv_Add_Remove_Programs values ('3BA60404-ED1E-4480-AB65-0AFCAF7CB8A9','Lotus Notes','7.0.2')
Insert into Inv_Add_Remove_Programs values ('A0A142F9-F585-4356-BFFF-1967B957339F','Lotus Notes','7.0.2')
I have tried queries like this to start with and all variations I can think of but I can't seem to get ALL 'ARO' computers along with the correct office and lotus versions.
Select
inv.Name,
inv.Domain,
inv.[User],
inv.127.0.0.1,
arp.Name,
arp.Version
From inv_computer inv
left outer join inv_add_remove_programs arp on inv.Guid = arp._ResourceGuid
Where
inv.Name LIKE 'ARO%' AND
(arp.Name LIKE 'Microsoft Office Standard%' OR inv.Name LIKE 'Microsoft Office Professional%')
Order by inv.Name ASC
Ultimately I need something that will give me this type of outcome if possible:
NameDomainUserIP AddressOffice NameOffice VersionLotus NameLotus Version
ARODUS1XP1RF1mydomainAROWHudson10.249.138.95Lotus Notes8.5.1
ARODUS2VRG331mydomainAROKNox10.48.40.198Microsoft Office Standard 200311.0.8173.0Lotus Notes8.5.1
ARODUS3V6SKC1mydomainAROTMason10.249.138.121Microsoft Office Progessional 200712.0.4518.1014
ARODUS46BFK71mydomainARODRodzen10.224.149.191Microsoft Office Standard 200712.0.4518.1014Lotus Notes7.0.2
ARODUS5CKKG81mydomainAROSacUPS10.224.173.16Lotus Notes7.0.2
ARODUS706VK81mydomainARORaLopez10.224.171.29Microsoft Office Standard 200311.0.8173.0
I can't think of any way to get both applications name and versions that apply to the computers that have both as well as return all ARO computers whether they have either installed or not.
Any help/direction would be great.
Thanks,
Clay
March 28, 2011 at 9:47 pm
How about this?
SELECT
inv.Name,
inv.Domain,
inv.[User],
inv.127.0.0.1,
MAX( CASE WHEN arp.Name LIKE '%Office%' THEN arp.Name END) AS OfficeName ,
MAX( CASE WHEN arp.Name LIKE '%Office%' THEN arp.Version END ) AS OfficeVersion ,
MAX( CASE WHEN arp.Name LIKE '%Lotus%' THEN arp.Name END ) AS LotusNotesName ,
MAX( CASE WHEN arp.Name LIKE '%Lotus%' THEN arp.Version END ) AS LotusNotesVersion
FROM inv_computer inv
INNER JOIN inv_add_remove_programs arp
ON inv.Guid = arp._ResourceGuid
WHERE
inv.Name LIKE 'ARO%'
GROUP BY
inv.Name,
inv.Domain,
inv.[User],
inv.127.0.0.1
ORDER BY inv.Name ASC
March 28, 2011 at 10:12 pm
This should do it for you
with msoffice as (
Select
Inv.Guid,
inv.Name,
inv.Domain,
inv.[User],
inv.127.0.0.1,
case when arp.Name LIKE 'Microsoft Office%'
Then arp.Name
Else ''
End as Office_Name,
case when arp.name like 'Microsoft Office%'
Then arp.Version
Else ''
End as OfficeVersion
From inv_computer inv
left outer join inv_add_remove_programs arp
on inv.Guid = arp.ResourceGuid
Where inv.Name LIKE 'ARO%'
AND (arp.Name LIKE 'Microsoft Office%' or arp.Name is null)
), lotus as (
Select
Inv.Guid,
inv.Name,
inv.Domain,
inv.[User],
inv.127.0.0.1,
case when arp.Name LIKE 'Lotus%'
Then arp.Name
Else ''
End As Lotus_Name,
Case when arp.Name like 'Lotus%'
Then arp.Version
Else ''
End as Lotus_Version
From inv_computer inv
left outer join inv_add_remove_programs arp
on inv.Guid = arp.ResourceGuid
Where inv.Name LIKE 'ARO%'
AND (arp.Name LIKE 'Lotus%' or arp.Name is null)
)
Select coalesce(L.name,m.Name) as CompName,coalesce(L.domain,m.Domain) as Domain
,coalesce(L.[User],m.[User]) as CompUser,coalesce(L.[Ip Address],m.127.0.0.1) as IPAddress
,m.Office_Name,m.OfficeVersion,L.Lotus_Name,L.Lotus_Version
From msoffice m
Full Outer Join lotus L
on m.Guid = L.Guid
Order by coalesce(l.name,m.Name) ASC
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2011 at 10:15 pm
Or you could do as ColdCoffee has written.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2011 at 11:34 pm
CirquedeSQLeil (3/28/2011)
Or you could do as ColdCoffee has written.
March 29, 2011 at 7:59 am
I want to thank both Coffee and CirquedeSQLeil for the quick feedback.
I would not have been able to come up with either of those queries.
Great job.
thanks again,
Clay
March 29, 2011 at 9:12 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy