Microsoft Assessment and Planning (MAP) Toolkit Team Blog

An agentless inventory, assessment, and reporting tool that can securely assess IT environments for various platform migrations— including Windows 8, Office 2013 and 365, Windows Server 2012, Hyper-V, Windows Azure, and Microsoft SQL Server 2012

Microsoft Assessment and Planning Toolkit How-To Series: Part 2 (Customized Reporting using SQL Queries)

Microsoft Assessment and Planning Toolkit How-To Series: Part 2 (Customized Reporting using SQL Queries)

  • Comments 1
  • Likes

To all of the Microsoft Assessment and Planning Toolkit users out there, you must be wondering about this...

Since MAP Toolkit is a database-driven tool, if the MAP Toolkit auto-generated reports and proposal documents do not provide the specific data I am looking for, can I create my own custom reports?  The answer is YES.  Today, we will give you some insights on how to create your own SQL queries and help you unleash the power of MAP!  Before you get started though, please make sure you have completed these pre-requisites:

  1. You must complete a run on a network with MAP Toolkit (so you will have a database to query!)
  2. You must use the MAP Toolkit instance called MAPS to do the following query using SQL Server 2005

You can create simple to very complex queries to learn all there is about each desktop, server or other devices that the MAP Toolkit has inventoried.  Here are a few of the sample queries. 

/*
*****************************************************************************************
   What MAP 3.1 inventory databases exist in this instance of SQL Server?
*****************************************************************************************
*/
 
 SELECT [name] as Database_Name
 FROM [master].[dbo].[sysdatabases]
 WHERE [dbid] > 4 and [name] <> 'SystemInfo'
 GO
 

 /*
******************************************************************************
   This is the simplest query to get all the information stored in the
   device table.  As you can see, there is lots of great information
   just in the [devices] table.
******************************************************************************
*/
 USE LUCERNE -- Or whatever database you have inventoried
 GO
 SELECT * FROM [devices]
 GO

If you're interested in finding machines that run on a specific OS, you can do this:

 SELECT [dns_host_name] AS Computer_Name,
    [operating_system] AS Operating_System,
 COALESCE([wmi_os_version], [ad_os_version],
    [net_server_enum_os_version] ) AS OS_Version,
 CASE
  WHEN d.[os_architecture] IS NULL THEN '32 bit'
    ELSE
  d.[os_architecture]
 END AS OS_Architecture
 FROM [devices] d
 WHERE [operating_system] like '%SERVER%'
 ORDER BY [operating_system], [os_architecture],[computer_system_name]
 GO

Here is a more complicated queries to get more machine-level information including BIOS, memory, service pack, etc:

 SELECT
 COALESCE([dns_host_name],[computer_system_name],
  [ad_dns_host_name], [server_name]) AS Computer_Name,
 CASE
  WHEN d.[enclosure_manufacturer] IS NULL THEN 'Unknown'
 ELSE
  [enclosure_manufacturer]
 END AS Manufacturer,
 CASE
  WHEN d.[model] IS NULL THEN 'Unknown'
 ELSE
  [model]
 END AS Model,
 CASE
  WHEN d.[operating_system] IS NULL THEN 'Unknown'
 ELSE
  [operating_system]
 END AS Operating_System,
 COALESCE([wmi_os_version], [ad_os_version], [net_server_enum_os_version] ) AS OS_Version,
 CASE
  WHEN [operating_system_service_pack] IS NULL THEN 'Unknown'
 ELSE
  [operating_system_service_pack]
 END AS Service_Pack,
 CASE
  WHEN d.[os_architecture] IS NULL THEN '32 bit'
    ELSE
  d.[os_architecture]
 END AS OS_Architecture,
 CASE
  WHEN d.[number_of_processors] IS NULL THEN '32 bit'
    ELSE
  d.[number_of_processors]
 END AS Processors,
 CASE
  WHEN d.[total_physical_memory] IS NULL THEN '32 bit'
    ELSE
  d.[total_physical_memory]
 END AS Memory,
 CASE
  WHEN [smbios_ASset_tag] IS NULL THEN 'Unknown'
 ELSE
  [smbios_ASset_tag]
 END AS Asset_Tag,
 CASE
  WHEN [bios_manufacturer] IS NULL THEN 'Unknown'
 ELSE
  [bios_manufacturer]
 END AS BIOS_Manufacturer,
 CASE
  WHEN [bios_serial_number] IS NULL THEN 'Unknown'
 ELSE
  [bios_serial_number]
 END AS Bios_Serial_Number,
 CASE
  WHEN [bios_version] IS NULL THEN 'Unknown'
 ELSE
  [bios_version]
 END AS BIOS_Version,
 CASE
  WHEN [roles] IS NULL THEN 'Unknown'
 ELSE
  [roles]
 END AS Roles
 FROM [devices] d
 ORDER BY [operating_system], 1
 GO

Here are a few more advanced SQL queries:

/*
********************************************************************************
   Installed Software - Here is a query to summarize what is installed
********************************************************************************
*/
 SELECT [vendor] AS Vendor,[name] as Software,[version],
    COUNT(name) AS Quantity
 FROM [products]
 GROUP BY [vendor],[name],[version]
 ORDER BY [vendor],[name],[version], Quantity

/*
******************************************************************************************
   Installed Services - Here is a query to summarize what services are running
******************************************************************************************
*/
 SELECT [caption] AS Service, [description] AS Description,
    [name] AS [Name], COUNT(state) AS Quantity
 FROM [services]
 WHERE [state] = 'Running'
 GROUP BY [caption],[description],[name]
 ORDER BY [caption],[description],[name]

/*
***********************************************************************************
   Find Machines Running Any Service
   Here is a query to find the machines that run a particular service like
   SMS or the computer browser
***********************************************************************************
*/
 SELECT
 COALESCE(d.[dns_host_name],d.[computer_system_name],
  d.[ad_dns_host_name], d.[server_name]) AS Computer_Name
 FROM [devices] d
 INNER JOIN [services] s ON d.[device_number] = s.[device_number]
 WHERE s.[name] = 'CcmExec' -- SMS Agent Name
-- **********************************************************************************************************
 SELECT
 COALESCE(d.[dns_host_name],d.[computer_system_name],
  d.[ad_dns_host_name], d.[server_name]) AS Computer_Name,
 s.[name] as Name, s.[description] AS [Description]
 FROM [devices] d
 INNER JOIN [services] s ON d.[device_number] = s.[device_number]
 WHERE s.[name] = 'Browser' -- Windows Browser

Lastly, you can also create a custom security assessment report on inventoried desktop machines, for example, using this SQL query - it's slightly more complicated.  :-)

/*
**************************************************************************
   Custom Security Reporting
   Here is a little more complicated query that gives you a custom
   security report
**************************************************************************
*/
  DECLARE @unknown nvarchar(max)
  DECLARE @ComputerName nvarchar(max)
  DECLARE @SecurityCenterAssessment nvarchar(max)
  DECLARE @WMIStatus nvarchar(max)
  DECLARE @CurrentOperatingSystem nvarchar(max)
  DECLARE @ServicePackLevel nvarchar(max)
  DECLARE @MachineType nvarchar(max)
  DECLARE @Virtual nvarchar(max)
  DECLARE @Physical nvarchar(max)
  DECLARE @ScNotRunning nvarchar(max)
  DECLARE @AvNotFound nvarchar(max)
  DECLARE @AvNotEnabled nvarchar(max)
  DECLARE @AspyNotFound nvarchar(max)
  DECLARE @AspyNotEnabled nvarchar(max)
  DECLARE @FwNotFound nvarchar(max)
  DECLARE @FwNotEnabled nvarchar(max)
  DECLARE @culture_info nvarchar(max)
  SET @culture_info = 'US-en'
  SET @unknown = dbo.get_str(2003, @culture_info)
  SET @ComputerName = dbo.get_str(2068, @culture_info)
  SET @SecurityCenterAssessment = dbo.get_str(3700, @culture_info)
  SET @WMIStatus = dbo.get_str(2069, @culture_info)
  SET @CurrentOperatingSystem = dbo.get_str(2072, @culture_info)
  SET @ServicePackLevel = dbo.get_str(2073, @culture_info)
  SET @ScNotRunning = dbo.get_str(3701, @culture_info)
  SET @AvNotFound = dbo.get_str(3702, @culture_info)
  SET @AvNotEnabled = dbo.get_str(3703, @culture_info)
  SET @AspyNotFound = dbo.get_str(3704, @culture_info)
  SET @AspyNotEnabled = dbo.get_str(3705, @culture_info)
  SET @FwNotFound = dbo.get_str(3706, @culture_info)
  SET @FwNotEnabled = dbo.get_str(3707, @culture_info)
  SET @MachineType = dbo.get_str(3708, @culture_info)
  SET @Virtual = dbo.get_str(3710, @culture_info)
  SET @Physical = dbo.get_str(3709, @culture_info)
  CREATE TABLE #TempCustomSecurity
  (
    computer_name    nvarchar(255) NULL,
    manufacturer     nvarchar(255) NULL,
    model      nvarchar(255) NULL,
    operating_system    nvarchar(255) NULL,
    os_version     nvarchar(255) NULL,
    device_number       uniqueidentifier NOT NULL,
    security_assessment nvarchar(255) NULL,
    machine_type     tinyint
  )
  INSERT INTO #TempCustomSecurity (
   computer_name,
   manufacturer,
   model,
   operating_system,
   os_version,
   device_number,
   security_assessment,
   machine_type
  )
  SELECT
  COALESCE(d.[dns_host_name],d.[computer_system_name],
   d.[ad_dns_host_name], d.[server_name]) AS Computer_Name,
  CASE
   WHEN d.[enclosure_manufacturer] IS NULL THEN 'Unknown'
  ELSE
   d.[enclosure_manufacturer]
  END AS Manufacturer,
  d.[model] as Model,
  d.[operating_system] AS Operating_System,
  COALESCE([wmi_os_version], [ad_os_version], [net_server_enum_os_version] ) AS OS_Version,
     d.device_number,
     CASE
    WHEN COALESCE(d.wmi_scan_result, 1) != 0
      /* WMI scan failed so we don't have sufficient data for assessment */
      THEN @unknown + CHAR(10)
    ELSE
      (CASE
      /* Security Center Service isn't running */
      WHEN svc1.device_number IS NULL OR
        svc1.state != 'Running' OR
        svc1.status != 'OK'
     THEN @ScNotRunning + CHAR(10)
      ELSE
     /*
      * Security Center Service is running, so now we check AV, AS, and
      * firewall individually.
      *
      * Antivirus
      */
     (CASE
        WHEN avs.instance_guid IS NULL
       THEN @AvNotFound + CHAR(10)
        WHEN COALESCE(avs.on_access_scanning_enabled, 0) = 0
       THEN @AvNotEnabled + CHAR(10)
        ELSE ''
      END) +
     /* Antispyware */
     (CASE
        /* XP >= SP2 */
        WHEN dbo.IsDeviceRunningXP(d.device_number) = 1 AND
       COALESCE(d.service_pack_major_version, 0) >= 2
       THEN
         (CASE
         WHEN svc3.device_number IS NULL
        THEN @AspyNotFound + CHAR(10)
         WHEN COALESCE(svc3.state, '') != 'Running' OR
           COALESCE(svc3.status, '') != 'OK'
        THEN @AspyNotEnabled + CHAR(10)
         ELSE ''
       END)
        /* Vista >= SP1 */
        WHEN dbo.IsDeviceRunningVista(d.device_number) = 1 AND
       COALESCE(d.service_pack_major_version, 0) >= 1
       THEN
         (CASE
         WHEN aspys.instance_guid IS NULL
        THEN @AspyNotFound + CHAR(10)
         WHEN (COALESCE(aspys.product_state, 0) & 61440 /* 0xF000 */) = 0
        THEN @AspyNotEnabled + CHAR(10)
         ELSE ''
       END)
        /*
      * All other client platforms.  Note that anything below XP SP2
      * will fall under the Security Center Service not running case,
      * so this case should only apply to Vista RTM.
      */
        ELSE
       (CASE
       WHEN aspys.instance_guid IS NULL
         THEN @AspyNotFound + CHAR(10)
       WHEN COALESCE(aspys.product_enabled, 0) = 0
         THEN @AspyNotEnabled + CHAR(10)
       ELSE ''
        END)
      END) +
     /* Windows Firewall */
     (CASE
        WHEN fws.instance_guid IS NULL AND
       svc2.device_number IS NULL
       THEN @FwNotFound + CHAR(10)
        WHEN COALESCE(fws.enabled, 0) = 0 AND
       (COALESCE(svc2.state, '') != 'Running' OR
        COALESCE(svc2.status, '') != 'OK')
       THEN @FwNotEnabled + CHAR(10)
        ELSE ''
      END)
    END)
     END,
     h.[machine_type]
   FROM
     dbo.devices d
    INNER JOIN
     dbo.hardware_assessment_clients_include_vm h
    ON d.device_number = h.device_number
    LEFT OUTER JOIN
     dbo.services svc1
    ON d.device_number = svc1.device_number AND
    LOWER(svc1.name) = 'wscsvc'   /* Windows Security Center Service */
    LEFT OUTER JOIN
     dbo.services svc2
    ON d.device_number = svc2.device_number AND
    LOWER(svc2.name) = 'mpssvc'   /* Windows Firewall Service */
    LEFT OUTER JOIN
     dbo.services svc3
    ON d.device_number = svc3.device_number AND
    LOWER(svc3.name) = 'windefend'/* Windows Defender */
    LEFT OUTER JOIN
     dbo.antivirus_settings avs
    ON d.device_number = avs.device_number
    LEFT OUTER JOIN
     dbo.antispyware_settings aspys
    ON d.device_number = aspys.device_number
    LEFT OUTER JOIN
     dbo.firewall_settings fws
    ON d.device_number = fws.device_number

 SELECT [computer_name] AS Computer_Name,
   [manufacturer] AS Manufacturer,
   [model] AS Model,
   [operating_system] AS Operating_System,
   [os_version] AS OS_Version,
   [security_assessment] AS Security_Information,
   CASE
  WHEN [machine_type] = 0 THEN 'Physical Machine'
  WHEN [machine_type] = 1 THEN 'Virtual Machine'
   ELSE
  'Unknown'
   END AS Machine_Type
 FROM #TempCustomSecurity tcs
 ORDER BY Computer_Name
 DROP TABLE #TempCustomSecurity

 To learn more about MAP Toolkit, please check out these links or take a test drive of MAP yourself:

Cheers,
Baldwin

Comments
  • 132 Microsoft Team blogs searched, 59 blogs have new articles in the past 7 days. 122 new articles found

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment