→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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


1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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%'

View solution in original post

6 REPLIES 6
ServiceDeskAPS
Engaged Sweeper
Thank you this provides an excellent starting point 🙂
Hemoco
Lansweeper Alumni
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
Lansweeper Alumni
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
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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