Help with report showing all Operating systems XP and earlier

[stuart153]

Hi,

Please could someone assist with a relatively simple report for Lansweeper v5150?

I'm trying to do a report that shows all of our windows systems have an operating system of XP or older.


I'm pretty much there, except I want to add an additional column showing the serial number of the hardware except when I try to add tblAssetCustom to my query, it screws it up.

Looking at what I've got so far, I'm somehow managing to use tables that have been upgraded from an earlier version, and adding tblAssetCustom to this, doesn't link in with the other tables.

Has anyone got anything similar to this?

Thanks






Select Top 1000000 upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_tblComputers.Domain,
upgrade_Web40OSName.OSname,
upgrade_tblComputers.Lastseen,
upgrade_tblOperatingsystem.ProductType,
upgrade_tblOperatingsystem.Version,
upgrade_tblComputers.LastknownIP,
upgrade_tblComputers.Username
From upgrade_tblOperatingsystem
Inner Join upgrade_tblComputers On upgrade_tblOperatingsystem.Computername =
upgrade_tblComputers.Computername
Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
upgrade_web40ActiveComputers.Computername
Inner Join upgrade_Web40OSName On upgrade_Web40OSName.Computername =
upgrade_tblComputers.Computername
Inner Join upgrade_tblADComputers On upgrade_tblComputers.Computername =
upgrade_tblADComputers.Computername
Where upgrade_Web40OSName.OSname Not Like 'Win 7' And
upgrade_Web40OSName.OSname Not Like 'Win 2003' And
upgrade_Web40OSName.OSname Not Like 'Win 2003 R2' And
upgrade_Web40OSName.OSname Not Like '%2008%' And
upgrade_Web40OSName.OSname Not Like '%2012%' And
upgrade_Web40OSName.OSname Not Like '%Win 8%'
Order By upgrade_tblComputers.Computer

[stuart153]

Hi,

This works, thanks for the reply 🙂

[Hemoco · Accepted solution]

We replied via email, but we're pasting the query below as well. Note that you should not use the "upgrade" views to build new reports. Use the new 5.1 database tables for this. "Upgrade" views should only be present in reports that were upgraded from 4.X to 5.X.

Select Top 1000000 upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_tblComputers.Domain,
upgrade_Web40OSName.OSname,
upgrade_tblComputers.Lastseen,
upgrade_tblOperatingsystem.ProductType,
upgrade_tblOperatingsystem.Version,
upgrade_tblComputers.LastknownIP,
upgrade_tblComputers.Username,
tblAssetcustom.Serialnumber
From upgrade_tblOperatingsystem
Inner Join upgrade_tblComputers On upgrade_tblOperatingsystem.Computername =
upgrade_tblComputers.Computername
Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
upgrade_web40ActiveComputers.Computername
Inner Join upgrade_Web40OSName On upgrade_Web40OSName.Computername =
upgrade_tblComputers.Computername
Inner Join upgrade_tblADComputers On upgrade_tblComputers.Computername =
upgrade_tblADComputers.Computername
Inner Join tblAssetcustom on tblAssetcustom.assetid = upgrade_tblComputers.Computername
Where upgrade_Web40OSName.OSname Not Like 'Win 7' And
upgrade_Web40OSName.OSname Not Like 'Win 2003' And
upgrade_Web40OSName.OSname Not Like 'Win 2003 R2' And
upgrade_Web40OSName.OSname Not Like '%2008%' And
upgrade_Web40OSName.OSname Not Like '%2012%' And
upgrade_Web40OSName.OSname Not Like '%Win 8%'
Order By upgrade_tblComputers.Computer