SQL

ConfigMgr Report for Antimalware Policies

Posted on Updated on

Let’s say for a moment that your organization uses SCCM for management of Windows Defender Antivirus (WDAV in Windows 10, Server 2016) or System Center Endpoint Protection (SCEP for legacy platforms).  Currently in SCCM (1706 or older) the only out-of-box mechanism to identify and report upon the antimalware policies being applied to a computer is through the SCCM console, such as in the image below.

ConsoleAntimalwarePolicies

What if the organization has a separate team or individual that needs that data – but you don’t want to provide them with the SCCM console?  You give them a report of course!  This quick guide will show you key things to do to obtain that info. The key steps are:

  1. Identify the SQL views being referenced by the SCCM console.
  2. Grant read permission of the SQL view to the SRSS reporting service account.
  3. Create the SRSS report.

Step 1: Identify the SQL views being referenced by the SCCM console.

  1. In the SCCM console, open the Antimalware Policies tab on the computer record
  2. Open the site server log SMSProv.log (and scroll to the end)
  3. Find the correlating “Execute SQL=” query to identify the SQL view(s) being used

    FindSQLqueryView

Step 2: Grant read permission of the SQL view to the SRSS reporting service account.

  1. Identify the service account being used by SCCM for SRSS reporting
    Tip: navigate to Administration > Security > Accounts, then locate the account being used for “ConfigMgr Reporting Services Point”
  2. Open SQL Management Studio (with a user account that has permissions to modify SQL permissions) and select the SCCM database
  3. Run the following GRANT command against the SCCM database
GRANT SELECT ON [dbo].[vSMS_G_SYSTEM_AmPolicyStatus] TO "DOMAIN\user"

GrantSQLview

Step 3: Create the SRSS report.  First off, there are many different ways that you can design the report.  To mimic what the SCCM console does, I usedan existing report with a selection box for the Computer Name, then just modified the executing query.

    1. Used report “Computer information for a specific computer” as an example baseline for selecting the computer name for a variable.
    2. Create a new report (using SQL Report Builder) to mimic the above report with the appropriate Data Source, Data Set(s), and Parameters

      AntimalwareReportBuilder

    3. Modified the SQL query to use the following code
SELECT APS.Name, APS.Priority, APS.LastMessageTime, @variable AS 'Computer Name'
FROM vSMS_G_SYSTEM_AmPolicyStatus as APS
JOIN v_R_System as SYS on APS.MachineID = SYS.ResourceID
WHERE SYS.Name0 = @variable
  1. Test execute the report to confirm the results
    Tip: in Report Builder, click the Run button on the Home tab
  2. Save, finish, and report!

AntimalwareReportResults

App-V 5.0 – count most used packages

Posted on Updated on

If using standalone AppV 5.0 infrastructure, use the following SQL query against your AppVReporting database to retrieve a count the most used AppV packages.

select distinct package_name, COUNT(*) as 'Use Count'
from dbo.PackageInformation pkg
inner join dbo.ApplicationUsage apu on pkg.host_id=apu.host_id
where pkg.version_guid=apu.version_guid
group by package_name
order by 'use count' desc

count_most_used_pkgs

App-V 5.0 – users of apps by date

Posted on Updated on

If using standalone AppV 5.0 infrastructure, use the following SQL query against your AppVReporting database to retrieve a list of apps run by users on by date.

select distinct app_name, package_name, COUNT(*) as 'Use Count'
from dbo.PackageInformation pkg
inner join dbo.ApplicationUsage apu on pkg.host_id=apu.host_id
where app_name like '% the app name %'
and pkg.version_guid=apu.version_guid
group by app_name, package_name

users_of_apps_by_date

App-V 5.0 – count most used apps

Posted on Updated on

If using standalone AppV 5.0 infrastructure, use the following SQL query against your AppVReporting database to retrieve a count of most used applications.

select distinct app_name, package_name, COUNT(*) as 'Use Count'
from dbo.PackageInformation pkg
inner join dbo.ApplicationUsage apu on pkg.host_id=apu.host_id
where pkg.version_guid=apu.version_guid
group by app_name, package_name
order by 'use count' desc

count_most_used_apps

App-V 5.0 – count app usage

Posted on Updated on

If using standalone AppV 5.0 infrastructure, use the following SQL query against your AppVReporting database to retrieve a count of application usage.

select distinct app_name, package_name, COUNT(*) as 'Use Count'
from dbo.PackageInformation pkg
inner join dbo.ApplicationUsage apu on pkg.host_id=apu.host_id
where app_name like '% the app name %'
and pkg.version_guid=apu.version_guid
group by app_name, package_name

count_app_usage

App-V 5.0 reporting – count app usage by user

Posted on Updated on

If using standalone AppV 5.0 infrastructure, use the following SQL query  against your AppVReporting database to retrieve a count of application usage by user.

select distinct username, app_name, package_name, COUNT(*) as 'Use Count'
from dbo.PackageInformation pkg
inner join dbo.ApplicationUsage apu on pkg.host_id=apu.host_id
where app_name like '% the app name %'
      and pkg.version_guid=apu.version_guid
group by username, app_name, package_name

count_app_use_by_user

Automating SQL 2008 R2 Express for ConfigMgr

Posted on Updated on

There are many guides online on automating the installation of SQL 2008 R2 Express.  Although each was missing a small detail, a concatenation of them led me to create a successful deployment.  Use the following process if you also are stuck trying to find the answer!

  1. Run “SQLEXPR_x64_ENU.exe /UIMODE=Normal”
  2. Go through the wizard, giving the settings desired for SQL Express, including the admins
  3. On the “Ready to Install” page of the wizard, obtain a copy of ConfigurationFile.ini and then cancel the setup wizard
  4. Edit the .ini file with the following:
    • Change parameter QUIET to “True”
      (Note: if you attempt to instead use QUIETSIMPLE to display a progress bar as part of the install, the deployment will not execute in ConfigMgr)
    • Comment out or delete the PCSOURCE parameter
    • Comment out or delete the UIMODE parameter
    • At the end of the file, add IACCEPTSQLSERVERLICENSETERMS=True
      (Note: do not use quotes around True as it’s a Boolean parameter)
  5. Create a script which
    • Copies the .ini file to c:\windows\temp
    • Executes command “SQLEXPR_x64_ENU.exe /ConfigurationFile= c:\windows\temp\ConfigurationFile.ini /INDICATEPROGRESS”

SQL Query App-V 5.0 Usage

Posted on Updated on

You can use the following query on your App-V 5.0 reporting server database (AppVReporting) to get application usage information for which users ran which software on which systems and when.


select distinct username, host_name, app_name, start_time

from dbo.ApplicationUsage APPU

join dbo.ClientInformation CLIINFO on APPU.host_id=CLIINFO.host_id

order by start_time desc

appv_usage

Custom query of ACT data for apps with issues

Posted on Updated on

The application compatibility toolkit (ACT) is a great tool for analyzing compatibility of apps when doing a migration of Windows.  However, the available “reports” are limited in what they display out-of-box.  Fortunately since the tool is a SQL database, we can write our own queries to get what we need.  The below procedure/query will identify business specific apps and the detected compatibility issues.

  1. Create and customize an ACT query (within the compatibility manager) which identifies your specific business software
  2. Select all of the apps listed
  3. Create a new custom category “Priority Business Apps”
    • For the primarily used business apps, create/select sub category “PrimaryApps”
    • For the secondarily used business apps, create/select sub category “SecondaryApps”
      ACTCategories
  4. Run the following query against the ACT database
    Select distinct CAQ.subCategory, AR.AppName, LI.issueID, AIQ.severity, LI.title, LI.Details
    from Applications AR
    join AppReport_Issues_Query AIQ on AIQ.appID=AR.identity_hash
    join Categorized_Applications_Query CAQ on CAQ.objectID=AR.identity_hash
    join Localized_Issue LI on LI.issueID=AIQ.issueID
    where CAQ.category='Priority Business Apps'
    
  5. Copy the results into Excel

AppIssuesQuery

Custom Client Health Monitoring, Part 3

Posted on Updated on

This is part 3 of 3 on my custom client health monitoring reports, which I created way back in SMS 2003 and I still use today.  Part 1 outlines a custom report that I call “Computers that belong to X number of monitored collections“, which shows how many collections in which a computer resides.  Part 2, a drill down of the first report, will then show exactly which collections that computer is a member of, called Collection memberships for a specific computer.  Drilling down into that report then shows a report that will show all computer records that are in the collection.  This report can be particularly helpful when trying to identify a set of computers (such as all at a particular location) that are being impacted.

The code:

select Count(FCM.Name) as 'Total number of computers in collection', 
   COL.Name, COL.Comment, COL.CollectionID
 from v_FullCollectionMembership FCM, v_Collection COL
 where  ((COL.CollectionID='CollectionID1' and FCM.CollectionID='CollectionID1') or
   (COL.CollectionID='CollectionID2' and FCM.CollectionID='CollectionID2') or
   (COL.CollectionID='CollectionID3' and FCM.CollectionID='CollectionID3') or
   (COL.CollectionID='CollectionIDetc' and FCM.CollectionID='CollectionIDetc') )
 group by COL.Name, COL.CollectionID, COL.Comment
 order by 'Total number of computers in collection' DESC

Now, link this report for further drill down into the default SCCM report for computer information by

  1. Edit the report and select the “Links” tab
  2. Change the “Link type” to “Link to another report”
  3. Select report “Members of a selected collection”
  4. Create a new Target Report Prompt where Name=variable, Prompt text=Collection ID, and Column=4
  5. Click OK and enjoy drill down ;-)

This now completes a trio of custom client health monitoring/reporting collections!