Enhancement of query to include addtional fields

[RDizzle]

Looking to enance this following report, to include
ADDisplay Name of the user
IP Location
Software Name
Software Publisher Name



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

[RDizzle]

Hi The aim would be to change it for any Software name as and when required.

What would be nice is for some sort of selection interface that would allow you to pick from the Items scanned by LAN Sweeper and produce the report using this or any other query.

[Hemoco · Accepted solution]

RDizzle wrote:
Hi The aim would be to change it for any Software name as and when required.

What would be nice is for some sort of selection interface that would allow you to pick from the Items scanned by LAN Sweeper and produce the report using this or any other query.

Please note that you can filter from within report results. You could open the built-in report "Software: List of software by computer" for instance and perform a search on the Software column.

[Hemoco]

This is expected behavior. Your original report listed machines with Microsoft Access installed without listing the actual software package. We then added the fields you requested. The report now lists machines with Microsoft Access installed and all software packages installed on those machines.

If your goal is to list only Microsoft Access installations, you need the report below.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Microsoft Access%' And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

[RDizzle]

The upgrade seems to have fixed the editor issue.

However, and it may be in the way that the query is written but when I try to specify the product name as per below, the query returns back all Software packages.

I was expecting to see all software name "Microsoft Access%" i.e any versions and publishers, can you spot something obvious here.


Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Microsoft Access%' And
tblSoftwareUni.SoftwarePublisher Like '%') And tblAssets.Lastseen <> '' And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

[Hemoco]

Please update your installation first using this installer. There were bugs in previous versions of the report builder.

[RDizzle]

Hi, when I come to paste this query into the editor it comes up like this and I get and error

"Invalid SELECT statement. Unexpected token "Inner" at line 23, pos 8: Unexpected token "Inner" at line 23, column 8"

I don't know where the extra '.' is coming from in line 23 something peculiar seems to be happening and the tblSoftware seems to disappear and . If I go into edit the line to be the same as the one you supplied, more '.' seem to appear.

This is how I am expecting the line to appear

"From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID" but it is presenting itself as

"From Inner Join On .tblSoftwareUni.SoftID = .tblSoftware.softID"


Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID In (Select .tblSoftware.AssetID
From Inner Join On .tblSoftwareUni.SoftID = .tblSoftware.softID
Where .tblSoftwareUni.softwareName Like '%Microsoft Access%' And
.tblSoftwareUni.SoftwarePublisher Like '%') And tblAssets.Lastseen <> '' And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

[Hemoco]

Please use the report below.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Microsoft Access%' And
tblSoftwareUni.SoftwarePublisher Like '%') And tblAssets.Lastseen <> '' And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique