Notification

Icon
Error

Custom Report for my organization - Help

Posted: Wednesday, January 1, 2020 12:36:14 PM(UTC)
Guy Lipcer

Guy Lipcer

Member Original PosterPosts: 2
0
Like
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 Angel


RC62N
#1RC62N Member Posts: 441  
posted: 1/13/2020 4:52:31 PM(UTC)
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.
Code:
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:
Code:
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
Guy Lipcer
#2Guy Lipcer Member Original PosterPosts: 2  
posted: 1/14/2020 11:37:18 AM(UTC)
Big thanks!
I'll give it a go and let you know how it went. :)

Active Discussions

Lansweeper All USB devices connected
by  earmor   Go to last post Go to first unread
Last post: Yesterday at 1:35:37 PM(UTC)
Lansweeper Software audit with installation date
by  RC62N   Go to last post Go to first unread
Last post: 7/3/2020 3:39:41 PM(UTC)
Lansweeper Duplicates (3-4) in report please helppppp!
by  AlexMZetec  
Go to last post Go to first unread
Last post: 7/2/2020 3:15:16 PM(UTC)
Lansweeper Report to find all computers with no logon data
by  Andy.S  
Go to last post Go to first unread
Last post: 7/2/2020 9:55:53 AM(UTC)
Lansweeper Change processes report to show opposite results
by  RC62N   Go to last post Go to first unread
Last post: 6/29/2020 9:38:46 PM(UTC)
Lansweeper Keep link to items when using column names
by  Tom Galvin  
Go to last post Go to first unread
Last post: 6/27/2020 12:16:03 AM(UTC)