Trying to create a "PCs on domain" report, but getting duplicates with IP addresses.

[necronom]

Hi,

I'm trying to create a report similar to the one that you get from the Web Console when you click the domain name, apart from I need to use tblADComputers.Description instead of the Description we get with it, and I'd like the DELL Service Tag added, which appears to be tblComputerSystemProduct.IdentifyingNumber.

I've looked for the domain report in the All Available Reports link, but I can't see it, then I looked for the report files on the server, but I can't see any

I tried to re-create it from a blank sheet, but I'm getting multiple records back. There doesn't appear to be a list of the fields/tables, so it's difficult to find the fields I need, and viewing the tables isn't easy; I'm having to save the report, create a new one, do a select * to look at it, then re-load my report back in - not very convenient.

There also doesn't appear to be an immediate mode where I can type quick selects to see what's going on, and the results on screen are squashed, so reading the column names isn't possible.

So, I need some help with this, if possible.

Either can someone point me to the reports SQL, so I can see how the domain one works, or can anyone say what I need to do to get mine working similarly?

The problem is that I'm getting between 3 and 6 records for most PCs, each with a different IP address. I don't know how the domain report is selecting which one to use. I thought it might be the records that have a WINSPrimaryServer as non-blank, but that wasn't it, so there must be something else I need to look for.

Here's what I have:

Select Top 1000000 dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique, tblADComputers.Description, Web40OSName.OSname, dbo.tblComputers.SP, Web40OSName.Compimage As icon, web40ProcessorCapacity.NrOfProcessors, web40ProcessorCapacity.MaxClockSpeed, web40CorrectMemory.Memory, tblNetwork.IPAddress, tblComputerSystemProduct.IdentifyingNumber From dbo.tblComputers Inner Join dbo.tblComputersystem On dbo.tblComputers.Computername = dbo.tblComputersystem.Computername Left Outer Join dbo.tblOperatingsystem On dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername Inner Join tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername Inner Join tblNetwork On dbo.tblComputers.Computername = tblNetwork.Computername Inner Join web40CorrectMemory On web40CorrectMemory.Computername = dbo.tblComputers.Computername Inner Join web40ProcessorCapacity On web40ProcessorCapacity.Computername = dbo.tblComputers.Computername Inner Join tblComputerSystemProduct On dbo.tblComputers.Computername = tblComputerSystemProduct.Computername Where tblNetwork.IPEnabled = 1 And dbo.tblComputersystem.PartOfDomain = 1 And dbo.tblComputers.Domain Like 'TASLTD' Order By dbo.tblComputers.Computer


Any help would be appreciated.

Thanks,

Paul.

[necronom]

Excellent! Thanks for that. It appears that it was the "Last Known IP" that I didn't know existed.

[Hemoco · Accepted solution]

Try this one:

Select Top 1000000 dbo.tblComputers.Computername,
  dbo.tblComputers.ComputerUnique, tblADComputers.Description,
  Web40OSName.OSname, dbo.tblComputers.SP, Web40OSName.Compimage As icon,
  web40ProcessorCapacity.NrOfProcessors, web40ProcessorCapacity.MaxClockSpeed,
  web40CorrectMemory.Memory, dbo.tblComputers.LastknownIP,
  tblComputerSystemProduct.IdentifyingNumber
From dbo.tblComputers Inner Join
  dbo.tblComputersystem On dbo.tblComputers.Computername =
    dbo.tblComputersystem.Computername Left Outer Join
  dbo.tblOperatingsystem On dbo.tblComputers.Computername =
    dbo.tblOperatingsystem.Computername Inner Join
  dbo.web40ActiveComputers On dbo.tblComputers.Computername =
    dbo.web40ActiveComputers.Computername Inner Join
  Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
  Left Join
  tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername
  Inner Join
  web40CorrectMemory On web40CorrectMemory.Computername =
    dbo.tblComputers.Computername Inner Join
  web40ProcessorCapacity On web40ProcessorCapacity.Computername =
    dbo.tblComputers.Computername Inner Join
  tblComputerSystemProduct On dbo.tblComputers.Computername =
    tblComputerSystemProduct.Computername
Where dbo.tblComputersystem.PartOfDomain = 1 And dbo.tblComputers.Domain Like
  'TASLTD'
Order By dbo.tblComputers.Computer