→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ismail
Engaged Sweeper II
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

1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

1 REPLY 1
Nick_VDB
Champion Sweeper III
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