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:

• Download Microsoft Assessment and Planning Toolkit (MAP) 3.1
• Read TechNet documentation on MAP Toolkit
• See MAP Toolkit Video Demos:
• Server Virtualization Candidates Assessment for Hyper-V
• Windows Vista Migration and PC Security Assessments
• Or, if you have questions about these SQL queries or need help with MAP, please post your questions on MAP TechNet Forum

Cheers,
Baldwin