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


2 REPLIES 2
MrHicUp
Engaged Sweeper
Big thanks!
I'll give it a go and let you know how it went. 🙂
RCorbeil
Honored Sweeper II
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