cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
asldesktop
Engaged Sweeper
Hello

I am trying to write a report but I am getting stuck. I would like a report to show computer information as well as the Microsoft Office version as well.

I have written the below report which works fine, but the report does not show the computers that do not have Microsoft Office installed at all. Is there a way to change this report so that it shows all computers, and lists the MS Office version for the PC's that have it installed?


Select Top 100000 tblcomputers.Computername, tblcomputers.ComputerUnique As [Computer Name], tblcomputers.Username As Username, tblComputerSystemProduct.Vendor As [Computer Manufacturer], tblComputerSystemProduct.Name As [Computer Model], tblComputerSystemProduct.IdentifyingNumber As [Service Tag], tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As [Warranty Expiration], tblSoftware.softwareName, tblcomputers.LastknownIP As [Last Known IP Address] From tblSoftware Inner Join tblcomputers On tblcomputers.Computername = tblSoftware.ComputerName Inner Join tblComputerSystemProduct On tblcomputers.Computername = tblComputerSystemProduct.Computername Inner Join tblCompCustom On tblcomputers.Computername = tblCompCustom.Computername Where tblSoftware.softwareName Like '%Microsoft Office%' Or tblSoftware.softwareName Like '%Visio%' Or tblSoftware.softwareName Like '%Project%' Order By tblcomputers.ComputerUnique


Many thanks in advance for any assistance.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please try the report below. (Our apologies, we misunderstood your question before.)

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique As
[Computer Name], tblComputers.Username As Username,
tblComputerSystemProduct.Vendor As [Computer Manufacturer],
tblComputerSystemProduct.Name As [Computer Model],
tblComputerSystemProduct.IdentifyingNumber As [Service Tag],
tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As
[Warranty Expiration], office.softwareName, tblComputers.LastknownIP
As [Last Known IP Address]
From tblComputers Left Join
(Select tblSoftware.softwareName, tblSoftware.ComputerName
From tblSoftware
Where (tblSoftware.softwareName Like '%Microsoft Office%') Or
(tblSoftware.softwareName Like '%Visio%') Or
(tblSoftware.softwareName Like '%Project%')) office On office.ComputerName
= tblComputers.Computername Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Order By tblComputers.ComputerUnique

View solution in original post

3 REPLIES 3
Hemoco
Lansweeper Alumni
Please try the report below. (Our apologies, we misunderstood your question before.)

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique As
[Computer Name], tblComputers.Username As Username,
tblComputerSystemProduct.Vendor As [Computer Manufacturer],
tblComputerSystemProduct.Name As [Computer Model],
tblComputerSystemProduct.IdentifyingNumber As [Service Tag],
tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As
[Warranty Expiration], office.softwareName, tblComputers.LastknownIP
As [Last Known IP Address]
From tblComputers Left Join
(Select tblSoftware.softwareName, tblSoftware.ComputerName
From tblSoftware
Where (tblSoftware.softwareName Like '%Microsoft Office%') Or
(tblSoftware.softwareName Like '%Visio%') Or
(tblSoftware.softwareName Like '%Project%')) office On office.ComputerName
= tblComputers.Computername Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Order By tblComputers.ComputerUnique
asldesktop
Engaged Sweeper
Hello

Many thanks for the report but unfortunatley it is still disaplying the same results.

I have some computers on the network that do not have MS Office installed. I would like to see all the computers in this report, and if they have MS Office installed then have the version displayed. If they dont have it installed it can be blank.

thanks.
Hemoco
Lansweeper Alumni
Please try the report below instead.

Select Top 100000 tblComputers.Computername, tblComputers.ComputerUnique As
[Computer Name], tblComputers.Username As Username,
tblComputerSystemProduct.Vendor As [Computer
Manufacturer],
tblComputerSystemProduct.Name As [Computer Model],
tblComputerSystemProduct.IdentifyingNumber As [Service Tag],
tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As
[Warranty Expiration], tblSoftware.softwareName, tblComputers.LastknownIP As
[Last Known IP Address]
From tblSoftware Right Join
tblComputers On tblComputers.Computername = tblSoftware.ComputerName Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Where (tblSoftware.softwareName Like '%Microsoft Office%') Or
(tblSoftware.softwareName Like '%Visio%') Or
(tblSoftware.softwareName Like '%Project%')
Order By tblComputers.ComputerUnique