December 27, 2011 at 8:24 am
I have a sql query I run but I need to exclude some data from it and I'm having a hard time figuring it out.
Org Query:
[Code]
SELECT i.name as Server, fd.path
FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
where fd.name = 'ESRDEF.BIN' and Path not like '%BinHub'
Order by i.name, fd.path[/Code]
Problem is Windows 7 and Windows 2008 I get duplicated data because of the pointer reference of Document and Settings
So for those OS's I would like to exclude all finds LIKE 'Windows Server 2008%' and LIKE 'Windows 7%'
Here is some messed up not working stuff I have started with.
[Code]
IF (vComputer.[OS Name]) LIKE 'Windows Server 2008%'
-- IF select * from vComputer where vComputer.[OS Name]) LIKE 'Windows Server 2008%'
BEGIN
Select * FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
where Path NOT LIKE 'C:\Documents and Settings%' AND fd.name = 'ESRDEF.BIN' and Path not like '%BinHub'
END
Else
BEGIN
Select * FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
WHERE fd.name = 'ESRDEF.BIN' and Path not like '%BinHub'
END
--Order by i.name, fd.path [/code]
January 3, 2012 at 7:32 am
it's hard to figure out what you're looking for without sample data and expected results. Can you provide these as well as table layouts for the 2 tables involved?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 3, 2012 at 8:25 am
Mike, This data returns machine names and file location paths for anything found in the Inv_Installed_File_Details where the file name = 'ESRDEF.BIN' but excludes entries where the path is like '%binhub'.
The data i need to look at for this is in this location: vComputer.[OS Name]
I want to basically exclude data based on the OS Name. If someone can help me in doing that process I can do the where statements for what is is I want to exclude when. I just don't know how to make the original determination.
AGain I'm just trying to take the Org Query and add some determination to it. If OS name LIKE 2008 and OS name Like 7 then do this... If not then do this ...
The reason for this is Microsoft uses junction points for document and settings in 64bit os's. My inventory program (Altiris) inventories both locations. I can't change this but I can control the report. So my goal is to exclude these duplicated entries from my report.
sample data from a report:
ap17C:\Documents and Settings\All Users\Application Data\Symantec\Symantec Endpoint Protection\12.1.671.4971.105\Data\Definitions\VirusDefs\20120102.018
ap18C:\Documents and Settings\All Users\Application Data\Symantec\Symantec Endpoint Protection\12.1.671.4971.105\Data\Definitions\VirusDefs\20120102.018
AP19C:\Documents and Settings\All Users\Application Data\Symantec\Symantec Endpoint Protection\12.1.671.4971.105\Data\Definitions\VirusDefs\20120102.018
AP19C:\ProgramData\Symantec\Symantec Endpoint Protection\12.1.671.4971.105\Data\Definitions\VirusDefs\20120102.018
ap17 is a xp box
ap18 is a 2003 box
ap19 is a 2008 box and you can see the duplicated data.
January 3, 2012 at 8:35 am
You can use CASE in a WHERE clause.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 12, 2012 at 10:22 am
can you be more specific on what you mean? by CASE?
I'm researching case now if I find something I will respond regarding this.
Update:
from what I have read about case I don't believe it can help me. Looks like case takes a found value and you can replace the value with something else. Which is not what I want to do I don't believe as I don't want something else I don't want a value at all.
Please explain if I'm wrong as I'm no expert and need the help I could be completely out in left field.
January 12, 2012 at 10:49 am
lawson2305 (1/12/2012)
can you be more specific on what you mean? by CASE?I'm researching case now if I find something I will respond regarding this.
Not without a clearer idea of what you are trying to do. This is a guess, let us know if it helps:
Select * -- raw columns
FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
where fd.name = 'ESRDEF.BIN'
and Path not like '%BinHub'
AND i.[OS Name]) LIKE 'Windows Server 2008%'
UNION ALL
Select * -- columns with calculations
FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
WHERE fd.name = 'ESRDEF.BIN'
and Path not like '%BinHub'
AND i.[OS Name]) NOT LIKE 'Windows Server 2008%'
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 12, 2012 at 11:00 am
Using what you provided I made this:
Select i.name as Server, fd.path
FROM dbo.Inv_Installed_File_Details fd JOIN vComputer i ON i.Guid = fd._ResourceGuid
where fd.name = 'ESRDEF.BIN' and Path not like '%BinHub' AND i.[OS Name] LIKE 'Windows Server 2008%' and Path NOT LIKE 'C:\Documents and Settings%'
UNION ALL
Select i.name as Server, fd.path
FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
WHERE fd.name = 'ESRDEF.BIN' and Path not like '%BinHub'AND i.[OS Name] NOT LIKE 'Windows Server 2008%'
Order by i.name, fd.path
This looks good so far I need to go through the data and double verify though.
January 12, 2012 at 11:06 am
I think that's the same as this:
Select i.name as Server, fd.path
FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
WHERE fd.name = 'ESRDEF.BIN' and Path not like '%BinHub'
AND (
(i.[OS Name] NOT LIKE 'Windows Server 2008%')
OR (i.[OS Name] LIKE 'Windows Server 2008%' and Path NOT LIKE 'C:\Documents and Settings%')
)
Order by i.name, fd.path
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 12, 2012 at 11:11 am
ChrisM@home
I think this did the trick!
Thank you very much for your help.
January 12, 2012 at 11:14 am
You're welcome.
Can I recommend to you the link in my sig "please read this"? It's quite funny, and it's helpful for new forum members too.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply