SQL
ConfigMgr Report for Antimalware Policies
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.
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:
- Identify the SQL views being referenced by the SCCM console.
- Grant read permission of the SQL view to the SRSS reporting service account.
- Create the SRSS report.
Step 1: Identify the SQL views being referenced by the SCCM console.
- In the SCCM console, open the Antimalware Policies tab on the computer record
- Open the site server log SMSProv.log (and scroll to the end)
- Find the correlating “Execute SQL=” query to identify the SQL view(s) being used
Step 2: Grant read permission of the SQL view to the SRSS reporting service account.
- 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” - Open SQL Management Studio (with a user account that has permissions to modify SQL permissions) and select the SCCM database
- Run the following GRANT command against the SCCM database
GRANT SELECT ON [dbo].[vSMS_G_SYSTEM_AmPolicyStatus] TO "DOMAIN\user"
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.
-
- Used report “Computer information for a specific computer” as an example baseline for selecting the computer name for a variable.
- Create a new report (using SQL Report Builder) to mimic the above report with the appropriate Data Source, Data Set(s), and Parameters
- 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
- Test execute the report to confirm the results
Tip: in Report Builder, click the Run button on the Home tab - Save, finish, and report!
App-V 5.0 – count most used packages
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
App-V 5.0 – users of apps by date
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
App-V 5.0 – count most used apps
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
App-V 5.0 – count app usage
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
App-V 5.0 reporting – count app usage by user
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
Automating SQL 2008 R2 Express for ConfigMgr
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!
- Run “SQLEXPR_x64_ENU.exe /UIMODE=Normal”
- Go through the wizard, giving the settings desired for SQL Express, including the admins
- On the “Ready to Install” page of the wizard, obtain a copy of ConfigurationFile.ini and then cancel the setup wizard
- 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)
- Change parameter QUIET to “True”
- 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
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
Custom query of ACT data for apps with issues
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.
- Create and customize an ACT query (within the compatibility manager) which identifies your specific business software
- Select all of the apps listed
- 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”
- 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'
- Copy the results into Excel
Custom Client Health Monitoring, Part 3
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
- Edit the report and select the “Links” tab
- Change the “Link type” to “Link to another report”
- Select report “Members of a selected collection”
- Create a new Target Report Prompt where Name=variable, Prompt text=Collection ID, and Column=4
- Click OK and enjoy drill down ;-)
This now completes a trio of custom client health monitoring/reporting collections!
t3chn1ck 