Hardware information from last 90 days

[Sdonovan415]

Daniel is there a report I can pull all the Hardware information from the last 90 days.
I was using this sql Query.

declare @LastSeenDays int
set @LastSeenDays = 90



select
upper(a.Domain) as DomainName,
upper(a.AssetName) as DeviceName,
--replace(replace(os.Caption,''(R)'',''''),'','','''') as OperatingSystem,
replace(os.Caption,'(R)','''') as OperatingSystem,

case
when os.Caption like '%Server%' then 'Server'
when bat.AssetID is not null then 'Laptop'
when os.Caption is null then null
else 'Desktop'
end as DeviceType,
case
when os.ServicePackMajorVersion > 0 and os.ServicePackMinorVersion > 0 then os.ServicePackMajorVersion + '.' + os.ServicePackMinorVersion
when os.ServicePackMajorVersion > 0 then os.ServicePackMajorVersion
end as ServicePack,
a.IPAddress as IPAddress,
case
when ac.Model = 'VMware Virtual Platform' then 'Virtual'
when ac.Model = 'Virtual Machine' then 'Virtual'
when ac.Model = 'HVM domU' then 'Virtual'
when ac.Model = 'VirtualBox' then 'Virtual'
when ac.Manufacturer = 'QEMU' then 'Virtual'
when ac.Model is null then null
when ac.Model = '' then null
else 'Physical'
end as PhysicalOrVirtual,
ac.Manufacturer as Make,
ac.Model as Model,
b.SerialNumber as SerialNumber,
ltrim(replace(replace(p.Name,'(R)',''),'(TM)','')) as CPUName,
c.NumberOfProcessors as NumberPhysicalCPUs,
case
when c.NumberOfLogicalProcessors is null then c.NumberOfProcessors
else c.NumberOfLogicalProcessors
end as NumberLogicalCPUs,
cast(c.TotalPhysicalMemory as bigint) as RAMinBytes,
case
when a.Username <> '' then a.Userdomain + '\' + a.Username
end as LastLoggedOnUser,
isnull(a.Lastseen, a.Firstseen) as LastSeenDate,
null as Environment,
null as PhysicalHost,
null as BusinessUnit,
null as CountryOfUse,
null as OSMobility,
null as TerminalServer

from tblAssets a
join tblComputersystem c on a.AssetID = c.AssetID
left join tblOperatingsystem os on os.AssetID = a.AssetID
left join tblBIOS b on b.AssetID = a.AssetID
left join tblPROCESSOR p on p.AssetID = a.AssetID and p.DeviceID ='CPU0'
left join tblBattery bat on bat.AssetID = a.AssetID
left join tblAssetCustom ac on a.AssetID = ac.AssetID

where
(datediff(d, isnull(a.Lastseen, a.Firstseen), getdate()) <= @LastSeenDays or @LastSeenDays = 0)

order by 2

[Daniel_B · Accepted solution]

I moved your second question to a new topic. Please find a report below which lists several hardware information for Windows assets:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case When Coalesce(tblPortableBattery.AssetID, '') = '' Then 
    Case When tblComputersystem.Domainrole > 1 Then 'Server' Else 'Desktop' End
    Else 'Laptop' End As Assettype,
  Case When tblAssetCustom.Model like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then 'virtual'
	Else 'physical' End As [virtual/physical],
  tblOperatingsystem.Caption As [Operating system],
  tblOperatingsystem.ServicePackMajorVersion As [Service Pack],
  tblOperatingsystem.WindowsDirectory As [Windows folder],
  tblOperatingsystem.RegisteredUser As [Registered user],
  tblOperatingsystem.Organization,
  tblAssets.Domain,
  tblComputersystem.SystemType,
  tblOperatingsystem.SerialNumber As [OS serial No],
  tblOperatingsystem.InstallDate As [OS Install date],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As [Service tag],
  TsysChassisTypes.ChassisName As Chassis,
  tblAssets.Processor,
  tProcessor.[Physical Processors],
  tProcessor.[Number of Cores],
  tProcessor.[Number Logical Processors],
  tblVideoController.Caption As [Video card],
  tblBIOS.Caption As BIOS,
  tblBIOS.Manufacturer As [BIOS manufacturer],
  tblBIOS.Version As [BIOS version],
  tblBIOS.SerialNumber As [BIOS Serial No],
  CorrectMemory.Memory,
  Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As [Max Memory Capacity],
  Cast(CorrectMemory.Used As numeric) As [Slots  used],
  tblPhysicalMemoryArray.MemoryDevices As [Slots available],
  Cast(Cast(tDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As Numeric) As
  [Disk total free space (GB)],
  Cast(Cast(tDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As
  [Disk total size (GB)]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Left Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Left Join (Select tblProcessor.AssetID,
    Count(tblProcessor.NumberOfCores) As [Physical Processors],
    Sum(tblProcessor.NumberOfCores) As [Number of Cores],
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Number Logical Processors]
  From tblProcessor
  Group By tblProcessor.AssetID) tProcessor On tblAssets.AssetID =
    tProcessor.AssetID
  Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblPhysicalMemoryArray On tblAssets.AssetID =
    tblPhysicalMemoryArray.AssetID
  Inner Join (Select tblAssets.AssetID,
    Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
    Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
  From tblAssets
    Left Outer Join (TsysMemorytypes
    Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
      tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
      tblPhysicalMemory.AssetID
  Group By tblAssets.AssetID,
    tblPhysicalMemory.MemoryType
  Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory
    On CorrectMemory.AssetID = tblAssets.AssetID
  Left Join (Select tblDiskdrives.AssetID,
    Sum(tblDiskdrives.Size) As Size,
    Sum(tblDiskdrives.Freespace) As Freespace
  From tblDiskdrives
  Where tblDiskdrives.DriveType = 3
  Group By tblDiskdrives.AssetID) tDiskdrives On tblAssets.AssetID =
    tDiskdrives.AssetID
  Left Join tblPortableBattery On tblAssets.AssetID = 
    tblPortableBattery.AssetID 
Where tblAssetCustom.State = 1 And DateDiff(d, Coalesce(tblAssets.Lastseen,
  tblAssets.Firstseen, GetDate()), GetDate()) <= 90
Order By tblAssets.AssetName