cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cousig
Engaged Sweeper
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?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
cousig
Engaged Sweeper
Great, thanks, that worked fine!
Hemoco
Lansweeper Alumni
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