exclude certain entries but not all when something is found

  • 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]

  • 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/

  • 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.

  • You can use CASE in a WHERE clause.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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.

  • 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%'


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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.

  • 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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home

    I think this did the trick!

    Thank you very much for your help.

  • 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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/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