Report on all PCs in same department

[harringg]

I'm having some scan issues (a topic for another post), but I'd like to include the PCs that are scanned and not scanned. I'm guessing this query isn't working on the "Not Scanned" PCs because there is no OS name in that field or some other "empty" field.

I'd like to modify this report to include any computer that has data in the tblcompcustom.department, regardless of it's been scanned or not in LS.

Like in the Domain Overview, when clicking on the domain name, it reports all PCs scanned or not. Then I'd like to customize the output with the fields below.

Thanks.

SELECT TOP 1000000 tblcomputers.computername,
                   tblcomputers.computerunique,
                   web40osname.compimage        AS icon,
                   web40osname.osname,
                   tblcomputers.sp              AS [Service Pack],
                   tblcompcustom.location,
                   tblcompcustom.department     AS [Research Unit],
                   tblcompcustom.custom1        AS [User Name],
                   tsyschassistypes.chassisname AS Type,
                   tblcompcustom.barcode        AS [Inventory Number],
                   tblbios.releasedate,
                   tblsystemenclosure.serialnumber
FROM   tblcomputers
       INNER JOIN tbldiskdrives
               ON tblcomputers.computername = tbldiskdrives.computername
       LEFT OUTER JOIN web40processorcapacity
                    ON tblcomputers.computername =
                       web40processorcapacity.computername
       INNER JOIN web40activecomputers
               ON tblcomputers.computername = web40activecomputers.computername
       INNER JOIN web40correctmemory
               ON web40correctmemory.computername = tblcomputers.computername
       INNER JOIN web40osname
               ON web40osname.computername = tblcomputers.computername
       INNER JOIN tblcomputersystemproduct
               ON tblcomputers.computername =
                  tblcomputersystemproduct.computername
       LEFT JOIN tblcompcustom
              ON tblcomputers.computername = tblcompcustom.computername
       INNER JOIN tblbios
               ON tblcomputers.computername = tblbios.computername
       INNER JOIN tblsystemenclosure
               ON tblcomputers.computername = tblsystemenclosure.computername
       INNER JOIN tsyschassistypes
               ON tsyschassistypes.chassistype = tblsystemenclosure.chassistypes
WHERE  tblcompcustom.department LIKE '%UNIT%'
       AND tbldiskdrives.caption = 'c:'
ORDER  BY dbo.tblcomputers.computername  

[harringg]

This report is working in v5044 for me.

Select Top 1000000 upgrade_tblcomputers.Computername,
  upgrade_tblcomputers.ComputerUnique,
  upgrade_web40osname.Compimage As icon,
  upgrade_web40osname.OSname,
  upgrade_tblcomputers.SP As [Service Pack],
  upgrade_tblcompcustom.Location,
  upgrade_tblcompcustom.Department As [Research Unit],
  upgrade_tblcompcustom.Custom1 As [User Name],
  TsysChassisTypes.ChassisName As Type,
  upgrade_tblcompcustom.BarCode As [Inventory Number],
  upgrade_tblbios.ReleaseDate,
  upgrade_tblsystemenclosure.SerialNumber
From upgrade_tblcomputers
  Left Outer Join upgrade_web40processorcapacity
    On upgrade_tblcomputers.Computername =
    upgrade_web40processorcapacity.Computername
  Inner Join upgrade_web40activecomputers On upgrade_tblcomputers.Computername =
    upgrade_web40activecomputers.Computername
  Left Join upgrade_web40correctmemory
    On upgrade_web40correctmemory.Computername =
    upgrade_tblcomputers.Computername
  Left Join upgrade_web40osname On upgrade_web40osname.Computername =
    upgrade_tblcomputers.Computername
  Left Join upgrade_tblComputerSystemProduct
    On upgrade_tblcomputers.Computername =
    upgrade_tblComputerSystemProduct.Computername
  Left Join upgrade_tblcompcustom On upgrade_tblcomputers.Computername =
    upgrade_tblcompcustom.Computername
  Left Join upgrade_tblbios On upgrade_tblcomputers.Computername =
    upgrade_tblbios.Computername
  Left Join upgrade_tblsystemenclosure On upgrade_tblcomputers.Computername =
    upgrade_tblsystemenclosure.Computername
  Left Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    upgrade_tblsystemenclosure.ChassisTypes
Where upgrade_tblcompcustom.Department Like '%UNIT%'
Order By upgrade_tblcomputers.Computername

[harringg]

Worked great! It produced the report I needed. Thanks.

[Hemoco · Accepted solution]

Please try the report below instead. It lists all computers whose tblCompCustom.Department entry includes the word "UNIT", regardless of whether or not these computers have been successfully scanned.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], tblBIOS.ReleaseDate, tblSystemEnclosure.SerialNumber From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Where tblCompCustom.Department Like '%UNIT%' Order By dbo.tblcomputers.computername

[Joaomarcalo]

Lansweeper wrote:
Please try the report below instead. It lists all computers whose tblCompCustom.Department entry includes the word "UNIT", regardless of whether or not these computers have been successfully scanned.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], tblBIOS.ReleaseDate, tblSystemEnclosure.SerialNumber From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Where tblCompCustom.Department Like '%UNIT%' Order By dbo.tblcomputers.computername

Hey there, i'm trying to do this in the new Lansweeper v.5039.
I noticed that is not the same tbls, but im kinda bad making reports, can i have a little help please?