Report Help - Joining two reports

[mike]

Hello all,

I have been trying to join to reports of computer information from v_R_system, v_RA_System_IPAddress, v_RA_System_MACAddress and v_GS_PC_BIOS. Along with user information from v_R_User. My SQL query kind of works but it shows many duplicates of users and computer names. Here is the SQL query:

SELECT TOP (100) PERCENT dbo.v_R_System.Netbios_Name0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_R_User.Full_User_Name0,

dbo.v_R_User.physicalDeliveryOfficeNam0, dbo.v_R_User.telephoneNumber0, dbo.v_R_System.User_Name0, dbo.v_RA_System_IPAddresses.IP_Addresses0,

dbo.v_RA_System_MACAddresses.MAC_Addresses0, dbo.v_GS_PC_BIOS.SerialNumber0

FROM dbo.v_RA_System_MACAddresses INNER JOIN

dbo.v_RA_System_IPAddresses ON dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_RA_System_IPAddresses.ResourceID INNER JOIN

dbo.v_GS_PC_BIOS ON dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_GS_PC_BIOS.ResourceID CROSS JOIN

dbo.v_R_System INNER JOIN

dbo.v_R_User ON dbo.v_R_System.User_Name0 = dbo.v_R_User.User_Name0

 

Here are my SCCM queries that I would like to combine:

 

SELECT

A.Netbios_Name0,

B.SerialNumber0,

A.User_Name0 AS 'Last user logged in'

A.Operating_System_Name_and0,

C.IP_Addresses0,

D.MAC_Addresses0

FROM

v_R_System A,

v_GS_PC_BIOS B,

v_RA_System_IPAddresses C,

v_RA_System_MACAddresses D

WHERE

A.ResourceID=B.ResourceID AND

B.ResourceID=C.ResourceID AND

C.ResourceID=D.ResourceID

*************************************************

SELECT

Full_User_Name0,physicalDeliveryOfficeNam0,telephoneNumber0

FROM

v_R_User

Can someone please send me in the right direction or assist with joining these two queries.

 

Thanks,

Mike

[mike]

I got it! Just in case any one else was wondering.

 

SELECT DISTINCT

v_R_System.Netbios_Name0 'Computer Name',

v_R_System.User_Name0 AS 'Last user logged in',

v_R_User.Full_User_Name0,

v_R_User.physicalDeliveryOfficeNam0 'Location',

v_R_User.telephoneNumber0 'Telephone',

v_RA_System_IPAddresses.IP_Addresses0 'IP Address',

v_GS_PC_BIOS.SerialNumber0 'Serial Number',

v_RA_System_MACAddresses.MAC_Addresses0,

v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 'Assest Tag',

v_R_System.Operating_System_Name_and0,

v_GS_COMPUTER_SYSTEM.TimeStamp

FROM

v_R_System

INNER JOIN v_R_User

ON v_R_System.User_Name0=v_R_User.User_Name0

INNER JOIN v_RA_System_IPAddresses

ON v_R_System.ResourceID=v_RA_System_IPAddresses.ResourceID

INNER JOIN v_GS_PC_BIOS

ON v_R_System.ResourceID=v_GS_PC_BIOS.ResourceID

INNER JOIN v_RA_System_MACAddresses

ON v_R_System.ResourceID=v_RA_System_MACAddresses.ResourceID

INNER JOIN v_GS_SYSTEM_ENCLOSURE

ON v_R_System.ResourceID= v_GS_SYSTEM_ENCLOSURE.ResourceID

INNER JOIN v_GS_COMPUTER_SYSTEM

ON v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID

WHERE

IP_Addresses0 NOT LIKE 'Fe80%'