→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
David_Elliott
Engaged Sweeper II
SQL query noob here.

I'm looking for the existence of two files. With this code, I receive a line item if a machine is false for either of the files. I receive two line items if the machine is false for both files. I would like to ignore the workstations with a single line item and only report on machines missing both files but do that in a single line.


Select Top 1000000 IsNull(dbo.tblComputerSystemProduct.Name, 'Unknown') As Model, dbo.tblComputers.Username, dbo.tblComputers.Computer, tblADComputers.OU, dbo.tblComputers.Lastseen, tblFileVersions.Found, tblFileVersions.FilePathfull, dbo.tblComputers.Computername From dbo.tblComputers Inner Join dbo.tblComputerSystemProduct On dbo.tblComputers.Computername = dbo.tblComputerSystemProduct.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername Inner Join tblFileVersions On dbo.tblComputers.Computername = tblFileVersions.Computername Where IsNull(dbo.tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%safeboot.sys' And dbo.tblComputers.LastknownIP Like '%10.%' Or IsNull(dbo.tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%epepcmonitor.exe' And dbo.tblComputers.LastknownIP Like '%10.%' Order By dbo.tblComputers.Username
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please use the report below for the information you are after.
Select Top 1000000 IsNull(tblComputerSystemProduct.Name, 'Unknown') As Model, Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Username, tblComputers.Computer, tblADComputers.OU, tblComputers.Lastseen From tblComputers Inner Join tblFileVersions On tblComputers.Computername = tblFileVersions.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Inner Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Where IsNull(tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblComputers.Computername In (Select tblComputers.Computername From tblComputers Inner Join tblFileVersions On tblComputers.Computername = tblFileVersions.Computername Where tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%safeboot.sys') And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%epepcmonitor.exe' And tblComputers.LastknownIP Like '%10.%' Order By tblComputers.Username

View solution in original post

2 REPLIES 2
David_Elliott
Engaged Sweeper II
that helped. thanks
Hemoco
Lansweeper Alumni
Please use the report below for the information you are after.
Select Top 1000000 IsNull(tblComputerSystemProduct.Name, 'Unknown') As Model, Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Username, tblComputers.Computer, tblADComputers.OU, tblComputers.Lastseen From tblComputers Inner Join tblFileVersions On tblComputers.Computername = tblFileVersions.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Inner Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Where IsNull(tblComputerSystemProduct.Name, 'Unknown') Like '%optiplex%' And tblComputers.Computername In (Select tblComputers.Computername From tblComputers Inner Join tblFileVersions On tblComputers.Computername = tblFileVersions.Computername Where tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%safeboot.sys') And tblFileVersions.Found = 'false' And tblFileVersions.FilePathfull Like '%epepcmonitor.exe' And tblComputers.LastknownIP Like '%10.%' Order By tblComputers.Username