→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
RDizzle
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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.

View solution in original post

7 REPLIES 7
RDizzle
Engaged Sweeper II
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
Lansweeper Alumni
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
Lansweeper Alumni
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
Engaged Sweeper II
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
Lansweeper Alumni
Please update your installation first using this installer. There were bugs in previous versions of the report builder.
RDizzle
Engaged Sweeper II
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
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now