OS field report

[chrisjones]

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

[chrisjones]

Perfect thanks for your help

[marshall-o · Accepted solution]

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]

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]

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]

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]

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]

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]

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]

Hi any response to this question please