cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
chrisjones
Engaged Sweeper II
Hi all, im trying to find the OS field so that i can run a report for OS, which returns the OS's including the not scanned.

Any ideas

Thanks
1 ACCEPTED SOLUTION
marshall-o
Engaged Sweeper II
I'm starting to see what you're trying to do now. Try this:

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.

View solution in original post

9 REPLIES 9
chrisjones
Engaged Sweeper II
Perfect thanks for your help
marshall-o
Engaged Sweeper II
I'm starting to see what you're trying to do now. Try this:

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.
chrisjones
Engaged Sweeper II
Hi thanks for your reply, unfortunately the report has returned the same results.

For some reason it still excludes those assets that havent been scanned
marshall-o
Engaged Sweeper II
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,
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
chrisjones
Engaged Sweeper II
Hi Thanks for your reply, im am trying to incorporate it into this query.

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
marshall-o
Engaged Sweeper II
Does this do what you're looking for?

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
chrisjones
Engaged Sweeper II
Hi i would like to include the not scanned OS's.

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
marshall-o
Engaged Sweeper II
I'm not sure I understand your question. Are you just trying to return every possible OS? Or are you trying to run a report that shows the OS of every computer you have? Also, are all of your computers Windows-based? Or are you trying to return non-Windows based OS names?

tblOperatingsystem.Caption is the plain text name of the OS version on a Windows based computer (Ex. Windows 7 Professional SP1).
chrisjones
Engaged Sweeper II
Hi any response to this question please

New to Lansweeper?

Try Lansweeper For Free

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

Try Now