Close Menu
    Facebook X (Twitter) Instagram
    Friday, May 16
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»SCCM report applications installed on computers without Updates

    SCCM report applications installed on computers without Updates

    Eswar KonetiBy Eswar KonetiJanuary 27, 5:40 pm3 Mins Read SCCM 2007 11,816 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Below listed the basic reports to display computers with particular add and remove application installed on computers.

    Report to list all the computers which have particular software/application installed ?

    SELECT
    c.Name0,
    a.DisplayName0

    FROM
    v_GS_ADD_REMOVE_PROGRAMS a,
    v_R_System c

    WHERE a.ResourceID = c.ResourceID
    AND a.DisplayName0 like '%Adobe acrobat%'

    GROUP BY
    c.Name0,
    a.DisplayName0

    ORDER BY c.name0

    You can also add the prompt value to ask for application name as well.

    Report to List all add and remove programs on Particular computer excludes software updates ?

    SELECT A.DisplayName0,A.InstallDate0, A.Version0,A.Publisher0

    FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B
    WHERE A.ResourceID = B.ResourceID
    AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
    AND A.DisplayName0 NOT LIKE ‘Security Update for %’
    AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
    AND A.DisplayName0 NOT LIKE ‘Update for Office %’
    AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
    AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
    AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’

    AND Name0 = @computer

    GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
    ORDER BY A.DisplayName0

    Prompt for creation @ computer:

    select Name0 from v_GS_COMPUTER_SYSTEM

    You can also apply the same report on to collection by simply adding prompt value

     

    select b.name0 [computer Name],COUNT(DisplayName0)[ARP Count] from v_GS_ADD_REMOVE_PROGRAMS a,v_GS_COMPUTER_SYSTEM B,v_FullCollectionMembership C
    where B.ResourceID=a.ResourceID and
    a.ResourceID=C.ResourceID
    and A.DisplayName0 NOT LIKE 'Hotfix for %'
    AND A.DisplayName0 NOT LIKE 'Security Update for %'
    AND A.DisplayName0 NOT LIKE 'Update for Microsoft %'
    AND A.DisplayName0 NOT LIKE 'Update for Office %'
    AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
    AND A.DisplayName0 NOT LIKE 'Update for Windows %'
    AND A.DisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
    AND A.DisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'

    AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
    AND c.CollectionID = @Collection
    group by B.Name0

    SELECT b.Name0,a.DisplayName0,A.InstallDate0, A.Version0,A.Publisher0
    FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
    WHERE A.ResourceID = B.ResourceID
    AND A.ResourceID = C.ResourceID

    AND A.DisplayName0 NOT LIKE 'Hotfix for %'
    AND A.DisplayName0 NOT LIKE 'Security Update for %'
    AND A.DisplayName0 NOT LIKE 'Update for Microsoft %'
    AND A.DisplayName0 NOT LIKE 'Update for Office %'
    AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
    AND A.DisplayName0 NOT LIKE 'Update for Windows %'
    AND A.DisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
    AND A.DisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'

    AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
    AND CollectionID =@Collection
    GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0,B.Name0
    ORDER BY b.name0

    Prompt for collection:

    SELECT CollectionID, Name FROM v_Collection

    SCCM report to list all the computers which doesnt have particular application/software installed onto it ?

    Query Report which display Computers without/doesn't have specific software in Add Remove Programs for a given collection:

    Select Distinct
    sys.Netbios_Name0,
    sys.User_Domain0,
    sys.User_Name0
    FROM
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    WHERE
    sys.ResourceID not in (select sys.ResourceID
    from
    v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    where
    DisplayName0 = @displayname)

    Prompt for Displayname:

    select DisplayName0 from v_Add_Remove_Programs

    applications installed configmgr(SCCM) report for mismatch of computers in Configmr report for computers with software not installedm without applications
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    SCCM report list collections with no deployments

    December 05, 12:04 pm

    How to migrate standalone MBAM to SCCM for bitlocker

    November 10, 2:32 pm

    25 Comments

    1. Pingback: SCCM ConfigMgr Compliance status of client for multiple software update groups | Eswar Koneti Blog

    2. Bashir on March 25, 2015 1:17 PM

      How can i create my own report to query all installed software and application on a specific collection, if you can explain it step by step it will be good, because i am new in Configuration Manager and i wanna know more about it.

      thanks.

      Reply
      • Eswar Koneti on March 31, 2015 7:26 AM

        you can try something like this SQL Query:

        SELECT A.DisplayName0, A.Version0, Count(*) AS 'Count'
        FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
        WHERE A.ResourceID = B.ResourceID
        AND A.ResourceID = C.ResourceID
        AND A.DisplayName0 NOT LIKE 'Hotfix for %'
        AND A.DisplayName0 NOT LIKE 'Security Update for %'
        AND A.DisplayName0 NOT LIKE 'Update %'
        AND CollectionID = 'PS100012'
        GROUP BY A.DisplayName0, A.Version0
        ORDER BY A.DisplayName0

        Replace the collectionID

        Reply
    3. levi on August 7, 2012 2:44 PM

      Hello Eswar, I have a scenario where this query would be very useful. But i receive the error below

      [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘‘’. — [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘.’.

      Please let me knowwhat I am doing wrong.

      Thanks

      SELECT Count(*) AS Count
      SELECT A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0

      FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

      WHERE A.ResourceID = B.ResourceID
      AND A.ResourceID = C.ResourceID
      AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
      AND A.DisplayName0 NOT LIKE ‘Security Update for%’
      AND A.DisplayName0 NOT LIKE ‘Update for Microsoft%’
      AND A.DisplayName0 NOT LIKE ‘Update for Office %’
      AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
      AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
      AND A.DisplayName0 NOT LIKE ‘Windows 2000 Hotfix%’
      AND A.DisplayName0 NOT LIKE ‘Windows Server 2003 Hotfix%’
      AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’

      AND CollectionID = @collection

      GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
      ORDER BY A.DisplayName0
      Report SQL Statement
      —————————

      Reply
      • Eswar Koneti on August 7, 2012 3:27 PM

        Hi,
        the problem is with quotes.can you please replace them by removing and adding it once again as they are converted to fancy quotes by blog.

        Reply
    4. heath on July 18, 2012 10:28 PM

      The one you provided on July 7.

      Reply
      • Eswar Koneti on July 19, 2012 7:53 AM

        hi,
        After you create report with the given query,you would need to create Prompt for COLLID which i have not given in comments posted on jul 7th.
        please create prompt for COLLID using
        SELECT CollectionID, Name FROM v_Collection

        Reply
    5. Dana Bessey on July 16, 2012 8:23 PM

      Hello Eswar
      RE: Let me say what i understood from ur question…do u want get rid of applications which are not approved but installed…. ?

      Yes true

      you can create package for uninstall and make it onto collection for the apps who are not approved….
      If you have lot of applications which are not approved,you can go with task sequence applied onto one collection…

      I do have a lots of apps so initially this would be a cleanup routine, in the future via packages it will be easy to maintain. I used the following SQL to create the report...

      SELECT DISTINCT
      RSYS.Name0 AS 'Computer',
      RSYS.User_Name0 As 'Last User ID',
      SF.FileName As 'File Name',
      SF.FileDescription As 'File Description',
      SF.FilePath As 'File Path',
      SF.FileSize As 'File Size',
      SF.FileVersion As 'File Version'
      FROM
      V_R_SYSTEM RSYS
      INNER JOIN V_GS_SoftwareFile SF ON RSYS.ResourceID = SF.ResourceID
      AND SF.FileDescription like '%Ad-Aware SE Professional%'
      AND SF.FileDescription like '%Agatha Christie - Peril at End House%'
      AND SF.FileDescription like '%ALOT Appbar%'
      AND SF.FileDescription like '%Anti-phishing Domain Advisor%'
      AND SF.FileDescription like '%Ask Toolbar%'
      AND SF.FileDescription like '%Bejeweled 2 Deluxe%'
      AND SF.FileDescription like '%Bing Bar%'
      AND SF.FileDescription like '%Blasterball 3%'
      AND SF.FileDescription like '%Blekko search bar%'
      AND SF.FileDescription like '%Bonjour%'
      AND SF.FileDescription like '%Bounce Symphony%'
      AND SF.FileDescription like '%Build-a-Lot - The Elizabethan Era%'
      AND SF.FileDescription like '%Cake Mania%'
      AND SF.FileDescription like '%Chuzzle Deluxe%'
      AND SF.FileDescription like '%Cisco Connect%'
      AND SF.FileDescription like '%Cisco Systems VPN Client 5.0.01.0600%'
      AND SF.FileDescription like '%Coupon Printer for Windows%'
      AND SF.FileDescription like '%Dell Dock%'
      AND SF.FileDescription like '%Diner Dash 2 Restaurant Rescue%'
      AND SF.FileDescription like '%DivX Setup%'
      AND SF.FileDescription like '%Excel Password Recovery%'
      AND SF.FileDescription like '%Facebook Messenger 2.1.4554.0%'
      AND SF.FileDescription like '%Farm Frenzy%'
      AND SF.FileDescription like '%FATE%'
      AND SF.FileDescription like '%FreeOnlineRadioPlayerRecorder Toolbar%'
      AND SF.FileDescription like '%GIMP 2.6.11%'
      AND SF.FileDescription like '%ICVERIFY Enterprise Edition Client%'
      AND SF.FileDescription like '%Insaniquarium Deluxe%'
      AND SF.FileDescription like '%InstantShare%'
      AND SF.FileDescription like '%InstantShareAlert%'
      AND SF.FileDescription like '%InstantShareDevices%'
      AND SF.FileDescription like '%InstantShareDevicesMFC%'
      AND SF.FileDescription like '%Jewel Quest II%'
      AND SF.FileDescription like '%Jewel Quest Solitaire%'
      AND SF.FileDescription like '%John Deere Drive Green%'
      AND SF.FileDescription like '%Lernout & Hauspie TruVoice American English TTS Engine%'
      AND SF.FileDescription like '%McAfee Security Scan Plus%'
      AND SF.FileDescription like '%Memories Disc Creator 2.0%'
      AND SF.FileDescription like '%Messenger Companion%'
      AND SF.FileDescription like '%Mobile PhoneTools%'
      AND SF.FileDescription like '%MSN Music Assistant%'
      AND SF.FileDescription like '%Music Editor Free%'
      AND SF.FileDescription like '%Musicmatch® Jukebox%'
      AND SF.FileDescription like '%Monarch 7.00%'
      AND SF.FileDescription like '%Monarch Report Explorer 5.0%'
      AND SF.FileDescription like '%Netviewer Support%'
      AND SF.FileDescription like '%NetZero Internet%'
      AND SF.FileDescription like '%NOOK for PC%'
      AND SF.FileDescription like '%pdfforge Toolbar v1.1.2%'
      AND SF.FileDescription like '%Penguins!%'
      AND SF.FileDescription like '%Plants vs. Zombies%'
      AND SF.FileDescription like '%Polar Bowler%'
      AND SF.FileDescription like '%Python 2.5.2%'
      AND SF.FileDescription like '%RandMap%'
      AND SF.FileDescription like '%Safari%'
      AND SF.FileDescription like '%Search Toolbar%'
      AND SF.FileDescription like '%SearchAssist%'
      AND SF.FileDescription like '%ShopAtHome.com Toolbar%'
      AND SF.FileDescription like '%Shutterfly Express Uploader%'
      AND SF.FileDescription like '%Skype Click to Call%'
      AND SF.FileDescription like '%Skype Toolbars%'
      AND SF.FileDescription like '%Skype™%'
      AND SF.FileDescription like '%Slingo Deluxe%'
      AND SF.FileDescription like '%Spybot - Search & Destroy%'
      AND SF.FileDescription like '%Spyware Doctor 7.0%'
      AND SF.FileDescription like '%Switch Sound File Converter%'
      AND SF.FileDescription like '%TomTom HOME 2.8.4.2596%'
      AND SF.FileDescription like '%TomTom HOME Visual Studio Merge Modules%'
      AND SF.FileDescription like '%Tweakui Powertoy for Windows XP%'
      AND SF.FileDescription like '%URL Assistant%'
      AND SF.FileDescription like '%Verizon Wireless Mobile Broadband Self Activation%'
      AND SF.FileDescription like '%VIP Access SDK (1.0.1.5)%'
      AND SF.FileDescription like '%Virtual Villagers - The Secret City%'
      AND SF.FileDescription like '%VNC Free Edition 4.1.3%'
      AND SF.FileDescription like '%VS10Runtime%'
      AND SF.FileDescription like '%VS10RuntimeWin32%'
      AND SF.FileDescription like '%VS10Runtimex64%'
      AND SF.FileDescription like '%WavePad Sound Editor%'
      AND SF.FileDescription like '%Wedding Dash%'
      AND SF.FileDescription like '%Windows Live Call%'
      AND SF.FileDescription like '%Windows Live Communications Platform%'
      AND SF.FileDescription like '%Windows Live Essentials%'
      AND SF.FileDescription like '%Windows Live Family Safety%'
      AND SF.FileDescription like '%Windows Live Installer%'
      AND SF.FileDescription like '%Windows Live Mail%'
      AND SF.FileDescription like '%Windows Live Messenger%'
      AND SF.FileDescription like '%Windows Live Messenger Companion Core%'
      AND SF.FileDescription like '%Windows Live MIME IFilter%'
      AND SF.FileDescription like '%Windows Live Photo Common%'
      AND SF.FileDescription like '%Windows Live Photo Gallery%'
      AND SF.FileDescription like '%Windows Live PIMT Platform%'
      AND SF.FileDescription like '%Windows Live SOXE%'
      AND SF.FileDescription like '%Windows Live SOXE Definitions%'
      AND SF.FileDescription like '%Windows Live Sync%'
      AND SF.FileDescription like '%Windows Live Toolbar%'
      AND SF.FileDescription like '%Windows Live Upload Tool%'
      AND SF.FileDescription like '%Windows Live UX Platform%'
      AND SF.FileDescription like '%Windows Live UX Platform Language Pack%'
      AND SF.FileDescription like '%Windows Live Writer%'
      AND SF.FileDescription like '%Windows Live Writer Resources%'
      AND SF.FileDescription like '%WinRAR 4.00 (32-bit)%'
      AND SF.FileDescription like '%Xobni Core%'
      AND SF.FileDescription like '%Yahoo! Detect%'
      AND SF.FileDescription like '%Yahoo! Software Update%'
      AND SF.FileDescription like '%Yahoo! Toolbar%'
      AND SF.FileDescription like '%Zuma Deluxe%'

      ORDER BY RSYS.Name0

      The question is, is what i am trying to do feasible and how much difficulty is involved in creating the package, if i have to hand build uninstall routines for every packages, I will attempt another method.

      Regards
      Dana

      Reply
      • Eswar Koneti on July 17, 2012 6:59 AM

        Did you try this ? http://eskonr.com/2011/09/sccm-ts-vb-script-to-uninstall-applications/

        Reply
    6. heath on July 14, 2012 5:23 PM

      Thanks, but it still does not work. When I run the report I get the error message The page cannot be displayed because an internal server error has occurred.. I've also tuned off show friendly error messages.

      Reply
      • Eswar Koneti on July 15, 2012 7:31 AM

        which query are you trying for ?

        Reply
    7. Dana Bessey on July 13, 2012 7:55 PM

      Hello Eswar, I would like to ask a question about your post linked below pertaining to malware reporting...
      http://social.technet.microsoft.com/Forums/da/configmgrgeneral/thread/884f70e9-8359-4b36-a67f-e3f8c63727fa

      Let me start off by saying I am just getting my feet wet with SCCM. I developed the report as is replacing the malware with apps to my choosing seemingly I would have to apply it to a collection to get results, what I was hoping to is to be able to create an uninstall package or similar to rid computers of a list of apps that are not approved without having to manually create a package for every app. It would also be nice perhaps if each app or each machine was stepped through rather than prcessing every app uninstall and every PC at once. Suggestions?

      Thanks in Advance.
      Dana

      Reply
      • Eswar Koneti on July 14, 2012 9:48 AM

        Hi,
        Let me say what i understood from ur question...do u want get rid of applications which are not approved but installed.... ? you can create package for uninstall and make it onto collection for the apps who are not approved....
        If you have lot of applications which are not approved,you can go with task sequence applied onto one collection...

        Reply
    8. Heath on July 6, 2012 10:04 PM

      there is no such thing as A.NAME.

      Reply
      • Eswar Koneti on July 7, 2012 3:53 PM

        see below :

        SELECT Count(*) AS Count
        SELECT B.Name0,A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0

        FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

        WHERE A.ResourceID = B.ResourceID
        AND A.ResourceID = C.ResourceID
        AND A.DisplayName0 NOT LIKE '%Hotfix for%'
        AND A.DisplayName0 NOT LIKE '%Security Update for%'
        AND A.DisplayName0 NOT LIKE '%Update for%'
        AND A.DisplayName0 NOT LIKE '%Update for Microsoft%'
        AND A.DisplayName0 NOT LIKE '%Update for Office%'
        AND A.DisplayName0 NOT LIKE '%Update for Outlook%'
        AND A.DisplayName0 NOT LIKE '%Update for Windows%'
        AND A.DisplayName0 NOT LIKE '%Windows 2000 Hotfix%'
        AND A.DisplayName0 NOT LIKE '%Windows Server 2003 Hotfix%'
        AND A.DisplayName0 NOT LIKE '%Windows XP Hotfix%'
        AND A.DisplayName0 NOT LIKE '%Driver%'

        AND CollectionID = @collection

        GROUP BY B.Name0,A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
        ORDER BY A.DisplayName0

        Reply
    9. Heath on July 6, 2012 8:09 PM

      Thank you for your example. I have your query working. I would like to add the Computer Name that the software is installed on. I thought it would be as simple as adding B.NAME0, but I get the following error message. Any ideas?

      "Column 'v_GS_COMPUTER_SYSTEM.Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


      SELECT Count(*) AS Count
      SELECT A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0,B.Name0

      FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

      WHERE A.ResourceID = B.ResourceID
      AND A.ResourceID = C.ResourceID
      AND A.DisplayName0 NOT LIKE '%Hotfix for%'
      AND A.DisplayName0 NOT LIKE '%Security Update for%'
      AND A.DisplayName0 NOT LIKE '%Update for%'
      AND A.DisplayName0 NOT LIKE '%Update for Microsoft%'
      AND A.DisplayName0 NOT LIKE '%Update for Office%'
      AND A.DisplayName0 NOT LIKE '%Update for Outlook%'
      AND A.DisplayName0 NOT LIKE '%Update for Windows%'
      AND A.DisplayName0 NOT LIKE '%Windows 2000 Hotfix%'
      AND A.DisplayName0 NOT LIKE '%Windows Server 2003 Hotfix%'
      AND A.DisplayName0 NOT LIKE '%Windows XP Hotfix%'
      AND A.DisplayName0 NOT LIKE '%Driver%'

      AND CollectionID = @collection

      GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
      ORDER BY A.DisplayName0

      Reply
      • Eswar Koneti on July 6, 2012 9:24 PM

        you will have to use group by class for a.Name0.try adding a.Name0 to group by field.

        Reply
    10. Hichem on July 4, 2011 2:23 PM

      Hi Eswar,

      Can you let me know where I get wrong?

      I want to have all the machines that are Running 2 softwares.

      set @DirLoc = (case when IsNULL(@DirLoc,'')='' then '%' else @DirLoc end)
      set @Computer = (case when IsNULL(@Computer,'')='' then '%' else @Computer end)
      set @DisplayName = (case when IsNULL(@DisplayName,'')='' then '%' else @DisplayName end)
      set @DisplayName2 = (case when IsNULL(@DisplayName2,'')='' then '%' else @DisplayName2 end)

      Select Distinct
      sys.Name0 As Computer,
      sys.Directory_Location0 As DirLoc,
      sys.User_Name0 As LastLoggedOnUser,
      arp.Publisher0 As Publisher,
      arp.DisplayName0 As DisplayName,
      arp.DisplayName0 As DisplayName2,
      arp.Version0 As Version,
      InstallDate0 As InstallDate

      FROM v_R_System sys Left JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
      WHERE
      arp.DisplayName0 Like @DisplayName
      AND
      arp.DisplayName2 Like @DisplayName2
      AND
      sys.Directory_Location0 Like @DirLoc
      AND
      Sys.name0 Like @Computer

      Order By sys.name0

      Thank you

      Reply
      • Eswar Koneti on August 8, 2011 1:42 PM

        did you create prompts for displayname,displaname2,dirloc and computer etc ?
        what error are you getting when creating the query ?

        Reply
    11. Gokul on June 6, 2011 2:51 PM

      Still Now I am getting Syntex error. Can you help me.

      Reply
      • Eswar Koneti on June 7, 2011 12:04 PM

        What error are you getting ? did u tried replaing the qutation ' from the copy report.
        can u send me the query which u have pasted into reports to eskonr@gmail.com please?

        Reply
    12. Joey on April 6, 2011 6:44 AM

      Hey Eswar, Still no go. Here is the error i get when i debug in sql on the server with the database is. Im sorry but i am not a sql person.

      Here is what it looks like in notepad when i copy it from your site. Is it space sensitive?

      *******************************************************************************************

      SELECT Count(*) AS ‘#’, SELECT A.DisplayName0,A.InstallDate0 A.Version0,A.Publisher0

      FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

      WHERE A.ResourceID = B.ResourceID
      AND A.ResourceID = C.ResourceID
      AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
      AND A.DisplayName0 NOT LIKE ‘Security Update for %’
      AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
      AND A.DisplayName0 NOT LIKE ‘Update for Office %’
      AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
      AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
      AND A.DisplayName0 NOT LIKE ‘Windows 2000 Hotfix%’
      AND A.DisplayName0 NOT LIKE ‘Windows Server 2003 Hotfix%’
      AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’

      AND CollectionID = @collection

      GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
      ORDER BY A.DisplayName0

      *****************************************************************************8

      Then when i use sql studio manager to debug it throws me the following error.

      Msg 102, Level 15, State 1, Line 2
      Incorrect syntax near '‘'.
      Msg 102, Level 15, State 1, Line 2
      Incorrect syntax near '.'.

      Any help would be greatly appreciated

      Reply
      • Eswar Koneti on April 6, 2011 11:26 AM

        Hi,
        I would say ,this must be a syntax Error with quotes.If you copy and paste the query from web,you will see this issue.
        Also there should be a comma after A.InstallDate0 in line 2 which is added now.
        can you try with this now ?

        SELECT Count(*) AS Count
        SELECT A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0

        FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

        WHERE A.ResourceID = B.ResourceID
        AND A.ResourceID = C.ResourceID
        AND A.DisplayName0 NOT LIKE 'Hotfix for %'
        AND A.DisplayName0 NOT LIKE 'Security Update for%'
        AND A.DisplayName0 NOT LIKE 'Update for Microsoft%'
        AND A.DisplayName0 NOT LIKE 'Update for Office %'
        AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
        AND A.DisplayName0 NOT LIKE 'Update for Windows %'
        AND A.DisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
        AND A.DisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'
        AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'

        AND CollectionID = @collection

        GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
        ORDER BY A.DisplayName0

        Reply
    13. Joey on March 22, 2011 4:32 AM

      Hey Eswar,

      This doesnt seem to be working for me? I am using SCCM 2007 R3 on Sql 2008. The error i get is below. Any help would be appreciated.

      ---------------------------
      Report SQL Statement
      ---------------------------
      [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '‘'. -- [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '.'.
      ---------------------------
      OK
      ---------------------------

      Reply
      • Eswar Koneti on March 22, 2011 1:05 PM

        there could be some typos with quotoes(’) in the query.could you please check it up once ? This works fine for me.

        Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.