
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-09-2016 04:27 PM
Any ideas
Thanks
Solved! Go to Solution.
- Labels:
-
Report Center

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 09:54 PM
Select Top 1000000 tblAssets.AssetName,
tblAssetCustom.AssetID As LanSweeperID,
tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
Case When tsysOS.OSname Is Not Null Then tsysOS.OSname Else 'Not Scanned' End As OS,
tblAssets.Domain,
tblAssets.FQDN,
tblAssetCustom.Manufacturer As Manufacturer,
tblAssetCustom.Model,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Username,
tblADusers.Name,
tblADusers.Displayname,
tblAssetCustom.Location,
tblADusers.Office,
tblAssetCustom.Serialnumber,
tblAssetCustom.SystemSKU,
tblOperatingsystem.InstallDate,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Scanserver,
tblAssets.IPAddress
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Outer Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Username = tblADusers.Username And tblAssets.Userdomain =
tblADusers.Userdomain
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName
This query is assuming you only want to return Windows assets. If this is not the case, let me know.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 10:13 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 09:54 PM
Select Top 1000000 tblAssets.AssetName,
tblAssetCustom.AssetID As LanSweeperID,
tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
Case When tsysOS.OSname Is Not Null Then tsysOS.OSname Else 'Not Scanned' End As OS,
tblAssets.Domain,
tblAssets.FQDN,
tblAssetCustom.Manufacturer As Manufacturer,
tblAssetCustom.Model,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Username,
tblADusers.Name,
tblADusers.Displayname,
tblAssetCustom.Location,
tblADusers.Office,
tblAssetCustom.Serialnumber,
tblAssetCustom.SystemSKU,
tblOperatingsystem.InstallDate,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Scanserver,
tblAssets.IPAddress
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Outer Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Username = tblADusers.Username And tblAssets.Userdomain =
tblADusers.Userdomain
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName
This query is assuming you only want to return Windows assets. If this is not the case, let me know.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 09:24 PM
For some reason it still excludes those assets that havent been scanned

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 09:16 PM
tblAssetCustom.AssetID As LanSweeperID,
tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblOperatingsystem.Caption As [OS Name],
tblOperatingsystem.OtherTypeDescription,
tblAssets.Domain,
tblAssets.FQDN,
tblAssetCustom.Manufacturer As Manufacturer,
tblAssetCustom.Model,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Username,
tblADusers.Name,
tblADusers.Displayname,
tblAssetCustom.Location,
tblADusers.Office,
tblAssetCustom.Serialnumber,
tblAssetCustom.SystemSKU,
tblOperatingsystem.InstallDate,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Scanserver,
tblAssets.IPAddress,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Username = tblADusers.Username And tblAssets.Userdomain =
tblADusers.Userdomain
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Order By tblAssets.AssetName

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 09:08 PM
Where would it be inserted.
Appreciate your assistance
Select Top 1000000 tblAssets.AssetName,
tblAssetCustom.AssetID As LanSweeperID,
tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblOperatingsystem.Caption As [OS Name],
tblOperatingsystem.OtherTypeDescription,
tblAssets.Domain,
tblAssets.FQDN,
tblAssetCustom.Manufacturer As Manufacturer,
tblAssetCustom.Model,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Username,
tblADusers.Name,
tblADusers.Displayname,
tblAssetCustom.Location,
tblADusers.Office,
tblAssetCustom.Serialnumber,
tblAssetCustom.SystemSKU,
tblOperatingsystem.InstallDate,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Scanserver,
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Username = tblADusers.Username And tblAssets.Userdomain =
tblADusers.Userdomain
Order By tblAssets.AssetName

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 08:36 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.LastActiveScan,
tsysOS.OSname
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 08:18 PM
Currently this is what it displays, but when i create a report it only shows those which are either members of a domain or workgroup
AssetName IP Address Description OS SP Memory Proc Processor
WIN-1U38R 10.67.4.206 Not scanned

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 07:55 PM
tblOperatingsystem.Caption is the plain text name of the OS version on a Windows based computer (Ex. Windows 7 Professional SP1).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-01-2016 05:27 PM
