Reports on "inactive" computers

[cousig]

I have this custom reports that is used to get a list of all active computers with some specific where clauses.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblComputersystem.Lastchanged, Web40OSName.Compimage As icon, tblADusers.Name, tblComputers.Lastseen, tblComputers.Firstseen, tblComputersystem.Model From tblComputersystem Inner Join tblComputers On tblComputersystem.Computername = tblComputers.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Inner Join tblADusers On tblADusers.Username = tblComputers.Username Where tblComputers.ComputerUnique Like '%l__-%' And tblComputers.ComputerUnique Not Like '%medv%' And tblComputersystem.Domainrole < '2' Order By dbo.tblComputers.Computer

Now I would like to have a report that only shows inactive computers with the same where clauses.

How should i do this?

[cousig]

Great, thanks, that worked fine!

[Hemoco · Accepted solution]

Please use the report below.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputersystem.Lastchanged, Web40OSName.Compimage As icon, tblADusers.Name,
tblComputers.Lastseen, tblComputers.Firstseen, tblComputersystem.Model
From tblComputersystem Inner Join
tblComputers On tblComputersystem.Computername = tblComputers.Computername
Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
tblADusers On tblADusers.Username = tblComputers.Username Inner Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Where tblComputers.ComputerUnique Like '%l__-%' And
tblComputers.ComputerUnique Not Like '%medv%' And
tblComputersystem.Domainrole < '2' And tblCompCustom.State = 2
Order By dbo.tblComputers.Computer