Custom Reports - Help with Generating

[ServiceDeskAPS]

I need some custom reports generated and am having some issues with the report builder, and SQL statements required to obtain the correct information that I am looking to export to excel from LanSweeper. Any assistance on the following items would be much appriciated as my SQL programming skills are quite rusty, and I am also having issues in finding the correct tables to get the information from.

Each of the reports needed are listed below, I am not sure if they will all be possible through the tool so if anybody could let me know that would be excellent as well.

Windows Servers - Physical
Manufacturer
Model
Serial #
Logical Name
OS
Version
Service Pack
Bit Type (X86 or X64)
IE Version
Insight Mgr. Agent Version
IP Address

Windows Servers - Virtual
Logical Name
OS
Version
Service Pack
Bit Type (X86 or X64)
IE Version
IP Address

UNIX Servers - Physical
Manufacturer
Model
Serial #
Logical Name
Type (standalone or blade)
OS
Version
IP Address

UNIX Servers - Logical
Logical Name
Type (Zone or LDM))
OS
Version
IP Address

Workstations
Manufacturer
Model
Serial #
Type (Desktop, Laptop, MAC)
OS
Version
Service Pack
Bit Type (X86 or X64)
IE Version
Admin Rights?
MAC Address

[ServiceDeskAPS]

Thank you this provides an excellent starting point 🙂

[Hemoco]

You can base the workstations report on this one (note the duplicates for each network card, can't be combined with admin groups)

Select tblComputers.Computername, tblComputers.ComputerUnique,
  tblComputerSystemProduct.Vendor, tblComputerSystemProduct.Name As Model,
  tblComputerSystemProduct.IdentifyingNumber, tblComputers.OScode,
  tblComputers.SP, tblComputersystem.SystemType, tblComputers.LastknownIP,
  IE.softwareName As IE, TsysChassisTypes.ChassisName, tblNetwork.MACaddress,
  tblNetwork.IPEnabled
From tblComputers Inner Join
  tblComputerSystemProduct On tblComputers.Computername =
    tblComputerSystemProduct.Computername Inner Join
  tblOperatingsystem On tblComputers.Computername =
    tblOperatingsystem.Computername Inner Join
  tblComputersystem On tblComputers.Computername =
    tblComputersystem.Computername Left Join
  (Select tblsoftware.ComputerName, tblsoftware.softwareName
    From tblsoftware
    Where tblsoftware.softwareName Like '%windows internet explorer%')
    IE On IE.ComputerName = tblComputers.Computername Inner Join
  tblSystemEnclosure On tblComputers.Computername =
    tblSystemEnclosure.Computername Inner Join
  TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes Inner Join
  tblNetwork On tblComputers.Computername = tblNetwork.Computername
Where tblComputersystem.Domainrole < 2 And tblNetwork.MACaddress <> ''
  And tblNetwork.IPEnabled = 1

[Hemoco]

Unix reports can be based on this:

Select tblCustDevices.Displayname, tblCustDevices.Vendor, tblCustDevices.Model,
  tblCustDevices.Ipaddress
From tblCustDevices Inner Join
  tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype
Where tblCustDevices.Devicetype = 12

[Hemoco · Accepted solution]

Report 1 and 2 can be based on this sql (you will need to tweak it for your needs)

Select tblComputers.Computername, tblComputers.ComputerUnique,
  tblComputerSystemProduct.Vendor, tblComputerSystemProduct.Name As Model,
  tblComputerSystemProduct.IdentifyingNumber, tblComputers.OScode,
  tblComputers.SP, tblComputersystem.SystemType, tblComputers.LastknownIP,
  IE.softwareName As IE, insight.softwareName As Insight
From tblComputers Inner Join
  tblComputerSystemProduct On tblComputers.Computername =
    tblComputerSystemProduct.Computername Inner Join
  tblOperatingsystem On tblComputers.Computername =
    tblOperatingsystem.Computername Inner Join
  tblComputersystem On tblComputers.Computername =
    tblComputersystem.Computername Left Join
  (Select tblsoftware.ComputerName, tblsoftware.softwareName
    From tblsoftware
    Where tblsoftware.softwareName Like '%windows internet explorer%')
    IE On IE.ComputerName = tblComputers.Computername Left Join
  (Select tblsoftware.ComputerName, tblsoftware.softwareName
    From tblsoftware
    Where tblsoftware.softwareName Like '%insight management%') insight On
    insight.ComputerName = tblComputers.Computername
Where tblComputerSystemProduct.Name Not Like 'vmware%'

[ServiceDeskAPS]

IE Version = the version that would be listed in Add Remove Programs ex. Windows Internet Explorer 7, Windows Internet Explorer 8

Insight Mgr. = In Add Remove Programs, correct name for the product is HP Insight Management Agents

How is a server defined physical or logical = based on manufacturer

Logical Name = computer name

Admin rights = Want to generate a list of users/groups per workstation that have been granted local admin rights to the machine in computer management.

MAC Address = Thats fine I am aware that I will get multiple results on some machines

There should be 5 different reports. I appriciate any assistance

[Hemoco]

IE Version = are you scanning for file iexplorer.exe?
Insight Mgr. Agent Version = is this in the software list? what's the correct name.
How do you define if a server is physical or virtual? based on manufacturer?
logical name= is this the computer name?
Admin rights= what do you mean by this?
Mac address= there will be multiple results if you have multiple network cards or virtual adapters