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: 429  
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 SQL Triggers
by  Kyle Zimmerman   Go to last post Go to first unread
Last post: 3/28/2020 9:43:26 PM(UTC)
Lansweeper Access LS Knowledge base from outside network
by  Hagobian  
Go to last post Go to first unread
Last post: 3/28/2020 12:46:44 AM(UTC)
Lansweeper Wake on Lan wol.exe issue
by  Socal_s197   Go to last post Go to first unread
Last post: 3/27/2020 10:05:42 PM(UTC)
Lansweeper Scanning Queue locked
by  KevinA-REJIS  
Go to last post Go to first unread
Last post: 3/27/2020 8:48:45 PM(UTC)
Lansweeper Asset Radar requirements
by  bbeavis   Go to last post Go to first unread
Last post: 3/27/2020 6:58:20 PM(UTC)
Lansweeper LSAgent Cloud Relay error
by  nlertn  
Go to last post Go to first unread
Last post: 3/27/2020 6:35:55 PM(UTC)
Lansweeper Scanning IP Range Subnet
by  WeatherDave   Go to last post Go to first unread
Last post: 3/27/2020 5:39:17 PM(UTC)