Find computer WITHOUT a specific filename on it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find computer WITHOUT a specific filename on it

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-07 : 08:40:59

Leif writes "Hi,

My question i clear and simple "How should a transact SQL script look like to find computers without specific filename on it"
Our computers are invertoried by MS SMS software invetory and all .exe files on the computers are placed in a SQL database.

I have try this one out but i will not work, it still shows computer in the result that have the file p95tray.exe on it. What I wont is all computers that don´t have this file:

Use S00_SMS
select distinct v_R_System.Name0
, v_R_System.AD_Site_Name0, v_HS_COMPUTER_SYSTEM.Model0, v_GS_LOADSET_GLOBALCORE.Version0 AS 'GCL Version'

from v_R_System inner join v_HS_COMPUTER_SYSTEM
on v_HS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
inner join v_GS_SoftwareFile
on v_GS_SoftwareFile.ResourceID = v_R_System.ResourceID
inner join v_GS_LOADSET_GLOBALCORE
on v_GS_LOADSET_GLOBALCORE.ResourceID = v_R_System.ResourceID

where v_GS_LOADSET_GLOBALCORE.Version0 = 3.0 AND (v_HS_COMPUTER_SYSTEM.Model0 LIKE '%2374%' OR v_HS_COMPUTER_SYSTEM.Model0 LIKE '%2371%')
AND v_R_System.ResourceID NOT IN (select v_R_System.ResourceID from v_R_System
inner join v_GS_SoftwareFile on
v_GS_SoftwareFile.ResourceID = v_R_System.ResourceID
where v_GS_SoftwareFile.FileName = 'p95tray.exe')
Order by v_R_System.Name0
GO

Please help!

Best regards

Leif"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-12-08 : 12:09:04

Try this. We may need some OUTER JOINs but let's see what this does.

select	distinct v_R_System.Name0
	, v_R_System.AD_Site_Name0
	, v_HS_COMPUTER_SYSTEM.Model0
	, v_GS_LOADSET_GLOBALCORE.Version0 AS 'GCL Version'

from	v_R_System inner join v_HS_COMPUTER_SYSTEM
	  on v_HS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
	inner join v_GS_SoftwareFile 
	  on v_GS_SoftwareFile.ResourceID = v_R_System.ResourceID
	  and v_GS_SoftwareFile.FileName <> 'p95tray.exe'
	inner join v_GS_LOADSET_GLOBALCORE
	  on v_GS_LOADSET_GLOBALCORE.ResourceID = v_R_System.ResourceID

where 	v_GS_LOADSET_GLOBALCORE.Version0 = 3.0 
AND 	(v_HS_COMPUTER_SYSTEM.Model0 LIKE '%2374%' OR v_HS_COMPUTER_SYSTEM.Model0 LIKE '%2371%'	) 
Order by v_R_System.Name0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources