2 Replies Latest reply on Jan 22, 2015 6:59 AM by naimc

    Looking for SQL script to find machines with package states other than : installed

    naimc Apprentice
      Hello I recently had to troubleshooting as Appsense issue in-which a user would logon and receive a blank desktop on windows 8.1 with nothing working other than a ctrl-alt-del that would bring up the task manager.

      I found that this machine had the MMC agent install but the EMM agent was in a Pending state.  I fixed the issue by uninstalling the MMC and reinstalling both MMC and EMM.

      This made me think that I should be running SQL query to identify all computers which both the MCC and EMM agents (packages)  are in any state other than : installed 

      Does anyone have such a query ?

      (using EMM 8.5 and MMC  8.6)

      Thank you

      Naim
        • 1. Re: Looking for SQL script to find machines with package states other than : installed
          gregf SupportEmployee
          I *think* this will do it:

          SELECT pr.Name AS [ProductName]         ,m.NetBiosName      ,mpv.Name      ,mpv.Status      ,mpv.PatchStatus      ,mpv.[Type]      ,(Cast(mpv.Major AS VARCHAR(10))    + '.'    + Cast(mpv.Minor AS VARCHAR(10))    + '.'    + Cast(mpv.Build AS VARCHAR(10))    + '.'    + Cast(mpv.Revision AS VARCHAR(10)) ) AS [BaseVersion]      ,(Cast(mpv.PatchMajor AS VARCHAR(10))    + '.'    + Cast(mpv.PatchMinor AS VARCHAR(10))    + '.'    + Cast(mpv.PatchBuild AS VARCHAR(10))    + '.'    + Cast(mpv.PatchRevision AS VARCHAR(10)) ) AS [PatchVersion]         FROM [MachinePackagesView] mpv              INNER JOIN [Machines] m ON m.MachinePK = mpv.MachineKey     INNER JOIN [Packages] pa ON mpv.PackageKey = pa.PackagePK     INNER JOIN [Products] pr on pa.ProductFK = pr.ProductPK  AND (mpv.Status <> 6 OR (mpv.PatchStatus <> NULL AND mpv.PatchStatus <> 6))


          Example output:


          A status of 6 means Installed, so the query is checking that either the status of the base package is not Installed, or there's an assigned patch whose state is not Installed.
          • 2. Re: Looking for SQL script to find machines with package states other than : installed
            naimc Apprentice

            Greg Ford wrote:

             

            I *think* this will do it.



            Hi Greg.

            Thank you for the script.   It worked without any additional modifications.

            After running the query I also realized, the computer objects that are returned are the same that appear in the  Appsense MC Management console, as having a Deployed % that is not 100%.  I had not realized the same info was in the Appsense MC.

            Naim.