RE: [mssms] RE: Report help?

Mike Murray Tue, 12 Dec 2017 13:57:06 -0800

Bump…

Yes, I updated the source to reflect the new baseline.


[cid:image001.png@01D3734A.D7C67CC0]

From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> 
[mailto:listsad...@lists.myitforum.com] On Behalf Of Sherry Kissinger
Sent: Thursday, December 7, 2017 6:32 AM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: Re: [mssms] RE: Report help?

"We migrated to a brand new ConfigMgr server"
So is this still correct?

-- Set the Baseline CI_UniqueID (AuthoringScopeId + LogicalName) based on a 
predefined value matching MBAM proprietary baseline

          SET @baseline_CI_UniqueID = 
N'ScopeId_69458D63-EB89-4A48-A165-9163E4732FEF/LogicalName_9f81cc17-a076-4083-ab73-e48463381567'

On Wed, Dec 6, 2017 at 12:07 PM, Mike Murray 
<mmur...@csuchico.edu<mailto:mmur...@csuchico.edu>> wrote:
bump

From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> 
[mailto:listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com>] 
On Behalf Of Mike Murray
Sent: Tuesday, December 5, 2017 11:08 AM
To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com>
Subject: [mssms] Report help?


This sender failed our fraud detection checks and may not be who they appear to 
be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing>

Feedback<http://aka.ms/SafetyTipsFeedback>

We migrated to a brand new ConfigMgr server. When I set up the BitLocker 
reports, there were some issues as the view names weren’t the same as what I 
have in the DBI updated the views in this compliance report, but I’m not 
getting any results. I’ve verified compliance data is populating in the DB. If 
I run a bare bones drive encryption report, it reports properly. The report 
below shows nothing. Can someone assist and tell me what I’m missing?


          DECLARE @baseline_CI_UniqueID varchar(512)
          DECLARE @CICompliant int
          DECLARE @CINotCompliant int

          -- Set CI compliant value
          SET @CICompliant = 1

          -- Set CI non compliant value
          SET @CINotCompliant = 2

          -- Set the Baseline CI_UniqueID (AuthoringScopeId + LogicalName) 
based on a predefined value matching MBAM proprietary baseline
          SET @baseline_CI_UniqueID = 
N'ScopeId_69458D63-EB89-4A48-A165-9163E4732FEF/LogicalName_9f81cc17-a076-4083-ab73-e48463381567'

          SELECT MBAMPolicy.EncodedComputerName0 [Computer Name],
            ComputerSystem.Domain0 [Domain Name],
            RSystem.User_Domain0 + N'\' + RSystem.User_Name0 [Device Users],
            CompStat.ComplianceState [Compliance Status],
            CASE
              WHEN MBAMPolicy.MBAMPolicyEnforced0 IS NULL THEN -1 -- (when 
outer join returns null : N/A)
              WHEN CompStat.ComplianceState = @CINotCompliant AND 
MBAMPolicy.MBAMPolicyEnforced0 = 3 THEN 1 -- tmp user exempt
              WHEN CompStat.ComplianceState = @CICompliant AND 
MBAMPolicy.MBAMPolicyEnforced0 = 2 THEN 2 -- user exempt
              ELSE 0 -- not exempted
            END AS Exemption,
            CASE
              WHEN CompStat.ComplianceState = @CICompliant AND 
MBAMPolicy.MBAMPolicyEnforced0 = 0 THEN 50 -- policy not enforced
              WHEN CompStat.ComplianceState = @CICompliant AND 
MBAMPolicy.MBAMPolicyEnforced0 > 0 THEN 0 -- No Error
              WHEN CompStat.ComplianceState = @CINotCompliant AND 
MBAMPolicy.MBAMMachineError0 IS NOT NULL THEN MBAMPolicy.MBAMMachineError0 -- 
MBAM agent error status
              ELSE -1 -- No Details \ Errors
            END AS [Compliance Status Details],
            MAX(AssignmentStatus.LastEvaluationMessageTime) [Last Evaluation 
Date]
          FROM v_FullCollectionMembership FullCollection
            INNER JOIN v_R_System_Valid RSystem
              ON FullCollection.ResourceID = RSystem.ResourceID
            INNER JOIN v_GS_COMPUTER_SYSTEM ComputerSystem
              ON RSystem.ResourceID = ComputerSystem.ResourceID
            INNER JOIN v_SMSCICurrentComplianceStatus CompStat
              ON CompStat.ItemKey = RSystem.ResourceID
                          INNER JOIN v_ConfigurationItems ConfigItems
                                                        ON ConfigItems.ModelID 
= CompStat.ModelID
            LEFT OUTER JOIN v_GS_WIN32REG_MBAMPOLICY MBAMPolicy
              ON MBAMPolicy.ResourceID = FullCollection.ResourceID
            LEFT OUTER JOIN v_CIAssignmentToCI AssignmentCI
              ON AssignmentCI.CI_ID  = ConfigItems.CI_ID
            LEFT OUTER JOIN v_CIAssignmentStatus AssignmentStatus
              ON AssignmentStatus.AssignmentID = AssignmentCI.AssignmentID
              AND AssignmentStatus.ResourceID = RSystem.ResourceID
          WHERE MBAMPolicy.EncodedComputerName0 IS NOT NULL
            AND FullCollection.IsClient = 1
            AND FullCollection.CollectionID = @collectionIdParameter
            AND ConfigItems.CI_UniqueID = @baseline_CI_UniqueID
          GROUP BY RSystem.ResourceID,
            ComputerSystem.Domain0,
            MBAMPolicy.EncodedComputerName0,
            RSystem.User_Domain0,
            RSystem.User_Name0,
            CompStat.ComplianceState,
            MBAMPolicy.MBAMPolicyEnforced0,
            MBAMPolicy.MBAMMachineError0



Best Regards,

Mike Murray
Desktop Engineer/IT Consultant - IT Support Services
California State University, Chico
530.898.4357<tel:(530)%20898-4357>
mmur...@csuchico.edu<mailto:mmur...@csuchico.edu>

Remember, Chico State will NEVER ask you for your password via email!
For more information about recognizing phishing scam emails go to: 
http://www.csuchico.edu/isec/basics/spam-and-phishing.shtml






--
Thank you,

Sherry Kissinger

My Parameters:  Standardize. Simplify. Automate
Blog: http://mnscug.org/blogs/sherry-kissinger

RE: [mssms] RE: Report help?

Reply via email to