List of PCs with and without software

[BSONO]

I need a list of all the PCs in an IP range. Looking to see if they have Microsoft Office, but I also want to list the rest of the PCs without Office as well. Here is what the report looked like before v5. I get an error on the upgraded reports. Thank you for any help.


Select Distinct Top 1000000 m.Computer, tblComputersystem.Model As ComputerModel, tblOperatingsystem.Caption, tblMonitor.MonitorModel, n.softwareName As Software, tblPrinters.Caption As Printers, tblComputers.Lastseen From tblComputers m Left Join (Select Distinct c.Computername, s.softwareName From tblComputers c Inner Join tblSoftware s On c.Computername = s.ComputerName Where s.softwareName Like '%SPC Express%' And c.LastknownIP Like '10.17.30.%' Or s.softwareName Like '%Microsoft%Office%Professional%''' Or s.softwareName Like '%Microsoft%Office%Standard%') As n On m.Computername = n.Computername Inner Join tblComputersystem On m.Computername = tblComputersystem.Computername Inner Join tblMonitor On m.Computername = tblMonitor.Computername Inner Join tblPrinters On m.Computername = tblPrinters.Computername Inner Join tblComputers On tblComputers.Computername = tblComputersystem.Computername And tblComputers.Computername = tblMonitor.Computername And tblComputers.Computername = tblPrinters.Computername Inner Join tblOperatingsystem On m.Computername = tblOperatingsystem.Computername And tblComputers.Computername = tblOperatingsystem.Computername Where m.LastknownIP Like '10.17.30.%' Order By m.Computer

[mwisniewski9]

This is exactly what I was looking for. Would it be possible to extend this table to generate a report that could list all of your PCs but then show multiple pieces of software that are installed/not installed? For instance, a column for computers and then a separate column for for software#1, software#2,etc... Pardon my poor SQL skills

[Susan_A]

mwisniewski9 wrote:
This is exactly what I was looking for. Would it be possible to extend this table to generate a report that could list all of your PCs but then show multiple pieces of software that are installed/not installed? For instance, a column for computers and then a separate column for for software#1, software#2,etc... Pardon my poor SQL skills

You would have to add more subqueries to the initial report we posted, i.e. you would have to repeat the snippet below and specify a unique name for each subquery. The subquery fields will be selectable like any other table fields in the upper section of the report builder.

Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%name of the software package%') subquery
On subquery.AssetID = tblAssets.AssetID

[Hemoco]

Sample report:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%YourSoftware%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

[abustraan]

I have a report for listing a particular peice of software installed on each system It works great. How can I change it to show the systems that are missing this software?

Select Top 1000000 upgrade_tblCompCustom.Department,
upgrade_tblComputers.ComputerUnique,
upgrade_tblSoftware.softwareName As Software
From upgrade_tblSoftware
Inner Join upgrade_tblComputers On upgrade_tblSoftware.ComputerName =
upgrade_tblComputers.Computername
Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
upgrade_web40ActiveComputers.Computername
Inner Join upgrade_tblCompCustom On upgrade_tblComputers.Computername =
upgrade_tblCompCustom.Computername
Group By upgrade_tblCompCustom.Department,
upgrade_tblComputers.ComputerUnique,
upgrade_tblSoftware.softwareName,
upgrade_tblSoftware.softwareVersion,
upgrade_tblSoftware.SoftwarePublisher
Having upgrade_tblSoftware.softwareName Like 'Malwarebytes%'
Order By upgrade_tblSoftware.softwareName,
upgrade_tblCompCustom.Department,
Count(upgrade_tblSoftware.ComputerName) Desc

I tried changing the "Like 'MalwareBytes%'" to "Not Like 'MalwareBytes%'" and I get all the results of all of other software for all the systems.

TIA

[harringg]

As a side topic, but since this report generates blank values, is it possible when the report has been run to filter in the web interface for fields that are blank?

I tried "IS NULL" in the web interface of a resulting report.

Is such a filter possible on a report that has been generated, or can it only be done by coding it in the first place?

[Hemoco]

harringg wrote:
Is such a filter possible on a report that has been generated, or can it only be done by coding it in the first place?

You cannot add filters like this within the report results. You'll need to edit the SQL query. It is expected behavior for the report posted in this thread to return blank values, as it includes both computers with and without a particular software package. Computers without the software package installed will have no values for the softwareName, softwareVersion and softwarePublisher fields.

[Hemoco · Accepted solution]

A sample 5.0 report can be seen below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
subquery.softwareName,
subquery.softwareVersion,
subquery.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft office%') subquery
On subquery.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename = 'windows' And tblAssets.IPAddress
Like '10.17.30.%' And tblAssetCustom.State = 1

[BSONO]

Lansweeper wrote:
A sample 5.0 report can be seen below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
subquery.softwareName,
subquery.softwareVersion,
subquery.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft office%') subquery
On subquery.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename = 'windows' And tblAssets.IPAddress
Like '10.17.30.%' And tblAssetCustom.State = 1

Works great. Thank you,