
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-05-2013 06:05 PM
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
Solved! Go to Solution.
- Labels:
-
Report Center

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-13-2013 02:27 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-13-2013 01:07 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-13-2013 02:27 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-13-2013 10:05 AM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-12-2013 03:00 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-11-2013 06:30 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-11-2013 06:24 PM
"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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2013 03:58 PM
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
