Show full OS build

[Ismail]

I have a report that has been created by my team which can be found below. It is great but I would like for the report to show full OS build. Currently it is only showing the OS version but not Edition. Ie Windows 2012 R2, where as I would like the report to show the edition as well. "Windows 2012 R2 DC or Datacenter,Standard" Any help would be appreciated.

Select Distinct Top 1000000 a.AssetID,
  a.AssetUnique,
  d.Image As icon,
  c.Manufacturer,
  c.Model As Model,
  SubString(c.Serialnumber, 1, 15) As Serial#,
  a.Processor As CPU,
  f.NumberOfProcessors As Sockets,
  f.NumberOfLogicalProcessors As LCP,
  Ceiling(a.Memory / 1024) As RAM,
  Sum(Cast(Cast(b.Size As bigint) / 1024 / 1024 / 1024 As numeric)) As
  [HDD Size],
  d.OSname As OS,
  a.IPAddress As IP,
  Replace(Replace(Replace(Replace(e.OU, 'OU=Computers,', ''), ',DC=amgdom,',
  ''), 'DC=com', ''), 'OU=', '') As Department,
  e.Description As Rack,
  a.Lastseen
From tblAssets a,
  tblADComputers e,
  tblDiskdrives b,
  tblAssetCustom c,
  tsysOS d,
  tblComputersystem f
Where a.AssetID = b.AssetID And b.AssetID = c.AssetID And a.OScode = d.OScode
  And a.AssetID = e.AssetID And b.AssetID = e.AssetID And a.AssetID = f.AssetID
  And (a.IPAddress Like '192.168.100.%' Or a.IPAddress Like '192.168.101.%' Or
    a.IPAddress Like '10.100.3.%' Or a.IPAddress Like '10.100.4.%' Or
    a.IPAddress Like '192.168.115.%' Or a.IPAddress Like '172.17.1.%' Or
    a.IPAddress Like '172.17.2.%' Or a.IPAddress Like '172.17.3.%' Or
    a.IPAddress Like '172.17.4. %') And f.Domainrole > 1 And c.State = 1
Group By a.AssetID,
  a.AssetUnique,
  d.Image,
  c.Manufacturer,
  c.Model,
  a.Processor,
  f.NumberOfProcessors,
  f.NumberOfLogicalProcessors,
  d.OSname,
  a.IPAddress,
  e.Description,
  a.Lastseen,
  a.Memory,
  c.Serialnumber,
  e.OU
Order By Rack,
  Model,
  IP

[Nick_VDB · Accepted solution]

The full OS name is stored in the tblOperatingsystem table. We have modified your report to give back the OS stored in tblOperatingsystem.

Select Distinct Top 1000000 a.AssetID,
  a.AssetUnique,
  d.Image As icon,
  c.Manufacturer,
  c.Model As Model,
  SubString(c.Serialnumber, 1, 15) As Serial#,
  a.Processor As CPU,
  f.NumberOfProcessors As Sockets,
  f.NumberOfLogicalProcessors As LCP,
  Ceiling(a.Memory / 1024) As RAM,
  Sum(Cast(Cast(b.Size As bigint) / 1024 / 1024 / 1024 As numeric)) As
  [HDD Size],
  tblOperatingsystem.Caption As OS,
  tblOperatingsystem.Version As [OS Version],
  a.IPAddress As IP,
  Replace(Replace(Replace(Replace(e.OU, 'OU=Computers,', ''), ',DC=amgdom,',
  ''), 'DC=com', ''), 'OU=', '') As Department,
  e.Description As Rack,
  a.Lastseen
From tblAssets a
  Inner Join tblOperatingsystem On a.AssetID = tblOperatingsystem.AssetID,
  tblADComputers e,
  tblDiskdrives b,
  tblAssetCustom c,
  tsysOS d,
  tblComputersystem f
Where a.AssetID = b.AssetID And b.AssetID = c.AssetID And a.OScode = d.OScode
  And a.AssetID = e.AssetID And b.AssetID = e.AssetID And a.AssetID = f.AssetID
  And (a.IPAddress Like '192.168.100.%' Or a.IPAddress Like '192.168.101.%' Or
    a.IPAddress Like '10.100.3.%' Or a.IPAddress Like '10.100.4.%' Or
    a.IPAddress Like '192.168.115.%' Or a.IPAddress Like '172.17.1.%' Or
    a.IPAddress Like '172.17.2.%' Or a.IPAddress Like '172.17.3.%' Or
    a.IPAddress Like '172.17.4. %') And f.Domainrole > 1 And c.State = 1
Group By a.AssetID,
  a.AssetUnique,
  d.Image,
  c.Manufacturer,
  c.Model,
  a.Processor,
  f.NumberOfProcessors,
  f.NumberOfLogicalProcessors,
  tblOperatingsystem.Caption,
  tblOperatingsystem.Version,
  a.IPAddress,
  e.Description,
  a.Lastseen,
  a.Memory,
  c.Serialnumber,
  e.OU
Order By Rack,
  Model,
  IP