Lansweeper logo
Home Download Features Demo Buy now Help Support forum
 
    Most requested support articles:
  Lansweeper troubleshooting guide.
  The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
  WMI Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
  How to configure the windows firewall using group policies.
  Support:  
 support@lansweeper.com  
Skype: Lansweeper  
  Mo-Fri 9h-17h CET  
Welcome Guest Search | Active Topics | Log In | Register

Tag as favorite
Multiple results per device
Options
Previous Topic Next Topic
imaginethat
#1 Posted : Thursday, May 06, 2010 5:52:01 AM
Rank: Freeware user

Groups: Member
Posts: 2
Location: here
Hi all
I am trying to create a report that gives me a few different things, IP, MAC, MachineName, Monitor Serial Number etc.
I have been able to run the existing reports fine, but if I add anything to these reports, or create reports of my own, I get all the data, but there are 10 results for each device.
What am I doing wrong?
Here is an example of a report i have created, and each device gives me 10 results, and the MAC seems to be different on 5 of those results, so a bit odd.
Select Top 1000000 dbo.tblBIOS.SerialNumber, dbo.tblComputers.Computer
As Hostname, dbo.tblComputers.LastknownIP, dbo.tblNetwork.MACaddress
From dbo.tblComputers Inner Join
dbo.tblBIOS On dbo.tblComputers.Computername = dbo.tblBIOS.Computername
Inner Join
tblSystemEnclosure On dbo.tblComputers.Computername =
tblSystemEnclosure.Computername
Inner Join
tblComputerSystemProduct On dbo.tblComputers.Computername =
tblComputerSystemProduct.Computername
Inner Join
tblNetwork On dbo.tblComputers.Computername = tblNetwork.Computername

What is the difference between dbo.tblBlablabla.Bla and just tblBlablabla.Bla.
Also I am not sure what the difference is between the "tbl" and the "web40rep" are on the right hand column of the lansweeper config window.
Is the infinite symbol linking the tables important?

Look forward to hearing from you.

cheers

imaginethat
Back to top
WWW
Lansweeper
#2 Posted : Thursday, May 06, 2010 10:08:31 AM

Rank: Administration

Groups: Administration, Premium Users
Posts: 10,378
I made only a small change to the report
Code:
Select Top 1000000 dbo.tblComputers.Computername,
  dbo.tblComputers.ComputerUnique, dbo.tblComputers.Computer As Hostname,
  dbo.tblBIOS.SerialNumber, dbo.tblComputers.LastknownIP,
  tblNetwork.Description, dbo.tblNetwork.MACaddress
From dbo.tblComputers Inner Join
  dbo.tblBIOS On dbo.tblComputers.Computername = dbo.tblBIOS.Computername
  Inner Join
  tblSystemEnclosure On dbo.tblComputers.Computername =
    tblSystemEnclosure.Computername Inner Join
  tblComputerSystemProduct On dbo.tblComputers.Computername =
    tblComputerSystemProduct.Computername Inner Join
  tblNetwork On dbo.tblComputers.Computername = tblNetwork.Computername


I added the description of the network card.
The problem is that each computer can have multiple (virtual) network cards each with their own mac address.

The same applies to tblmonitors, they can have multiple monitors
Back to top
imaginethat
#3 Posted : Monday, May 10, 2010 1:57:29 AM
Rank: Freeware user

Groups: Member
Posts: 2
Location: here
Ah ha, thank you very much for the prompt and accurate help.
With the description on the NIC turned on I could see what you mean, there are many virtual NIC devices, so can filter them out.

Thanks again for your help.

cheers
Back to top
WWW
bungargp
#4 Posted : Monday, May 10, 2010 3:55:22 PM

Rank: Premium user

Groups: Premium Users
Posts: 20
Location: Orlando, FL
I think my problem may be that some of my laptops are on a docking station. It appears that they are showing up twice in my report below. Is there a way to only have them appear once...say to the wired nic?
Code:
Select Top 1000000 dbo.tblComputers.Computer As [Computer Name],
  dbo.tblComputersystem.Manufacturer, dbo.tblComputersystem.Model,
  Cast(dbo.web30ProcessorCapacity.NrOfProcessors As VarChar) + ' * ' +
  Cast(dbo.web30ProcessorCapacity.MaxClockSpeed As VarChar) + ' Mhz' As
  Processor, Cast(Cast(Cast(dbo.tblComputersystem.TotalPhysicalMemory As BigInt)
  / 1024 / 1024 As Numeric) As VarChar) + ' KB' As Memory,
  Cast(Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
  1024 As Numeric) As VarChar) + ' GB' As HDDsize, dbo.tblComputers.LastknownIP
  As [IP  Address], dbo.tblComputers.LastActiveScan As [Date Scanned],
  tblComputerSystemProduct.IdentifyingNumber As Serial,
  tblBIOS.SMBIOSBIOSVersion As [BIOS Version],
  tblADComputers.OperatingSystemServicePack As [XP Service Pack],
  tblSystemEnclosure.SMBIOSAssetTag
From dbo.tblComputers Inner Join
  dbo.tblComputersystem On dbo.tblComputers.Computername =
    dbo.tblComputersystem.Computername Inner Join
  dbo.tblDiskdrives On dbo.tblComputers.Computername =
    dbo.tblDiskdrives.Computername Left Outer Join
  dbo.web30ProcessorCapacity On dbo.tblComputers.Computername =
    dbo.web30ProcessorCapacity.Computername Inner Join
  tblComputerSystemProduct On dbo.tblComputers.Computername =
    tblComputerSystemProduct.Computername Inner Join
  tblBIOS On dbo.tblComputers.Computername = tblBIOS.Computername Inner Join
  tblADComputers On tblADComputers.Computername = dbo.tblComputers.Computername
  Inner Join
  tblSystemEnclosure On dbo.tblComputers.Computername =
    tblSystemEnclosure.Computername
Where dbo.tblComputersystem.Domainrole = '1' And dbo.tblDiskdrives.Caption =
  'c:'
Order By dbo.tblComputers.Computername
Back to top
bungargp
#5 Posted : Wednesday, May 12, 2010 12:37:41 PM

Rank: Premium user

Groups: Premium Users
Posts: 20
Location: Orlando, FL
Any answers to my problem above?
Back to top
i.kulgu
#6 Posted : Wednesday, May 12, 2010 12:53:12 PM

Rank: Premium user

Groups: Premium Users
Posts: 70
Location: Holland
Try ; Select distinct
Back to top
bungargp
#7 Posted : Wednesday, May 12, 2010 12:58:58 PM

Rank: Premium user

Groups: Premium Users
Posts: 20
Location: Orlando, FL
I am not sure what you mean by this...can you give me a little more detail...thanks for your reply!
Back to top
Lansweeper
#8 Posted : Wednesday, May 12, 2010 1:06:01 PM

Rank: Administration

Groups: Administration, Premium Users
Posts: 10,378
You can filter out chassistype=12 which is a docking station.
Back to top
i.kulgu
#9 Posted : Wednesday, May 12, 2010 1:21:45 PM

Rank: Premium user

Groups: Premium Users
Posts: 70
Location: Holland
Try this

Code:
Select Distinct Top 1000000 dbo.tblComputers.Computer As [Computer Name],
  dbo.tblComputersystem.Manufacturer, dbo.tblComputersystem.Model,
  Cast(dbo.web30ProcessorCapacity.NrOfProcessors As VarChar) + ' * ' +
  Cast(dbo.web30ProcessorCapacity.MaxClockSpeed As VarChar) + ' Mhz' As
  Processor, Cast(Cast(Cast(dbo.tblComputersystem.TotalPhysicalMemory As BigInt)
  / 1024 / 1024 As Numeric) As VarChar) + ' KB' As Memory,
  Cast(Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
  1024 As Numeric) As VarChar) + ' GB' As HDDsize, dbo.tblComputers.LastknownIP
  As [IP  Address], dbo.tblComputers.LastActiveScan As [Date Scanned],
  tblComputerSystemProduct.IdentifyingNumber As Serial,
  tblBIOS.SMBIOSBIOSVersion As [BIOS Version],
  tblADComputers.OperatingSystemServicePack As [XP Service Pack],
  tblSystemEnclosure.SMBIOSAssetTag, dbo.tblComputers.Computername
From dbo.tblComputers Inner Join
  dbo.tblComputersystem On dbo.tblComputers.Computername =
    dbo.tblComputersystem.Computername Inner Join
  dbo.tblDiskdrives On dbo.tblComputers.Computername =
    dbo.tblDiskdrives.Computername Left Outer Join
  dbo.web30ProcessorCapacity On dbo.tblComputers.Computername =
    dbo.web30ProcessorCapacity.Computername Inner Join
  tblComputerSystemProduct On dbo.tblComputers.Computername =
    tblComputerSystemProduct.Computername Inner Join
  tblBIOS On dbo.tblComputers.Computername = tblBIOS.Computername Inner Join
  tblADComputers On tblADComputers.Computername = dbo.tblComputers.Computername
  Inner Join
  tblSystemEnclosure On dbo.tblComputers.Computername =
    tblSystemEnclosure.Computername
Where dbo.tblComputersystem.Domainrole = '1' And dbo.tblDiskdrives.Caption =
  'c:'
Order By dbo.tblComputers.Computer, dbo.tblComputers.Computername
Back to top
bungargp
#10 Posted : Wednesday, May 12, 2010 1:43:58 PM

Rank: Premium user

Groups: Premium Users
Posts: 20
Location: Orlando, FL
Thanks...still getting the double entries for some of my laptops. I wonder if it has anything to do with those laptops being on docking stations?
Back to top
i.kulgu
#11 Posted : Wednesday, May 12, 2010 1:49:10 PM

Rank: Premium user

Groups: Premium Users
Posts: 70
Location: Holland
I don't think.. did you tried what lansweeper said ?

Quote:
You can filter out chassistype=12 which is a docking station.
Back to top
bungargp
#12 Posted : Wednesday, May 12, 2010 2:14:35 PM

Rank: Premium user

Groups: Premium Users
Posts: 20
Location: Orlando, FL
Somehow I missed that response...and it worked!! Thanks again!
Back to top
Users browsing this topic
Guest
Tag as favorite
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Contact: E-mail Lansweeper - Skype : Lansweeper
Copyright 2004 - 2011 © Hemoco bvba