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:
- You must complete a run on a network with MAP Toolkit (so you will have a database to query!)
- 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
