Custom Report for my organization - Help

[MrHicUp]

Hi,
Can you help me create a pretty straight forward report that needs to have the specific tables:

*Computer name
*Domain User name
*OS info (win7 64bit/32bit, win10 64bit/32bit etc')
*Processor Model
*Memory Capcity (4GB,8GB etc')
*Office Version
*Manufacture Brand(Dell, HP, Lenovo Etc')
*Manufacture Service Tag/Serial Number
*Hard Drive Model and Capacity
*If there is a way to show computer model+type(desktop or laptop)

Thanks for all the Help

[MrHicUp]

Big thanks!
I'll give it a go and let you know how it went. 🙂

[RCorbeil]

First off, Microsoft labels a lot of components that aren't Microsoft Office proper as Microsoft Office something-or-other, so figure out what filters you'll need to trim the list down to just Microsoft Office. This set of exclusions works on my inventory. You may need to expand on it, depending on yours.

Select
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher
From
  tblSoftwareUni
Where
  tblSoftwareUni.softwareName LIKE 'Microsoft Office%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Add-in%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Meeting%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Visio%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Outlook%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Assemblies%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Publisher%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Runtime%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Interface%'
  AND tblSoftwareUni.softwareName NOT LIKE '%OneNote%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Metadata%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Click-to-Run%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Access%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Engine%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Components%'

Once you've confirmed that, if necessary, update that list in this and see if it covers everything you're looking for:

Select Top 1000000
  tblAssets.AssetID,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Case
    When tblComputersystem.Domainrole <= 1 Then 'workstation'
    When tblComputersystem.Domainrole > 1 Then 'server'
    Else 'undefined'
  End As [Role],
  tblDomainroles.Domainrolename,
  tblAssets.Lastseen,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblADusers.Name,
  tsysOS.OSname,
  tblAssets.SP,
  tblAssets.Version As [OS Version],
  Case
    When tblComputersystem.SystemType Like 'X86%' Then '32-bit'
    When tblComputersystem.SystemType Like 'x64%' Then '64-bit'
    Else ''
  End As [OS Bitness],
  tblOperatingsystem.Caption As [OS Caption],
  tblAssets.NrProcessors,
  tblAssets.Processor,
  tblAssets.Memory,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblFloppy.Model As [HDD0 Model],
  Floor(tblFloppy.Size / Power(10, 9)) As [HDD0 Capacity (GB)],
  Case
    When tblPortableBattery.Name Is Not Null Then 'Laptop'
    When tblAssetCustom.Manufacturer Like 'Microsoft%' And tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
    Else TsysChassisTypes.ChassisName
  End As Chassis,
  Software.softwareName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblADusers On tblAssets.Userdomain = tblADusers.Userdomain And tblAssets.Username = tblADusers.Username
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblFloppy On tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.Name Like '%PHYSICALDRIVE0'
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
  Left Join (Select Distinct
               tblSystemEnclosure.AssetID,
               tblSystemEnclosure.ChassisTypes
             From tblSystemEnclosure
             WHERE tblSystemEnclosure.ChassisTypes <> 12) AS Enclosure ON Enclosure.AssetID = tblAssets.AssetID
  Left Join TsysChassisTypes On Enclosure.ChassisTypes = TsysChassisTypes.Chassistype
  Left Join (Select
               tblSoftware.AssetID,
               tblSoftwareUni.softwareName,
               tblSoftwareUni.SoftwarePublisher
             From
			   tblSoftware
			   Inner Join tblSoftwareUni On TblSoftwareUni.SoftID = tblSoftware.softID
             Where
               tblSoftwareUni.softwareName LIKE 'Microsoft Office%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Add-in%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Meeting%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Visio%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Outlook%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Assemblies%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Publisher%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Runtime%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Interface%'
               AND tblSoftwareUni.softwareName NOT LIKE '%OneNote%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Metadata%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Click-to-Run%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Access%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Engine%'
               AND tblSoftwareUni.softwareName NOT LIKE '%Components%') AS Software ON Software.AssetID = tblAssets.AssetID
Where
 tblAssetCustom.State = 1       -- Active
 And tblAssets.Assettype = -1   -- Windows
Order By
  [Role],
  tblAssets.AssetName