using WMI and VBScript to audit your workstations

Auditing hardware and software for all machines in your domain can be time consuming. For small organizations, it may be a bit manageable provided that you already have a checklist of items that you need to look at for auditing purposes. CPU, disk, RAM, OS, service pack versions, etc. are just a few of those things you would like to take into account when doing auditing and inventory. For larger organizations, this could be a big challenge. Since I work for an organization that provides IT services for clients, we are required to maintain server information for auditing and inventory purposes. Imagine me doing this for almost 200+ Windows 2000 and Windows 2003 servers in multiple domains, not to mention Windows XP workstations. Being a lazy guy as I am, I wrote a script to simply automate this task. It uses VBScript and WMI to retrieve hardware, software and operating system information from computers in the domain. This requires administrative rights on the machine where this script is executed. It generates a text file (with filename servername_yyyymmdd_Audit.txt) which contains the information retrieved by the script. This is my list of information, you can always generate a lot more information by referring to the WMI SDK. Simply replace the value in the strComputer variable to the name/IP address of the computer you wish to audit

On Error Resume Next

Const HKEY_LOCAL_MACHINE = &H80000002

'change this value to the IP address or hostname of the machine you need to audit
strIPvalue = "."

CALL GenerateReport(strIPvalue)

WScript.Echo "Inventory Complete "


'=================================================================================
'SUB-ROUTINE GenerateReport
SUB GenerateReport(strIPvalue)

'Script to change a filename using timestamps
strPath = "C:\" 'Change the path to appropriate value
strMonth = DatePart("m", Now())
strDay = DatePart("d",Now())

if Len(strMonth)=1 then
strMonth = "0" & strMonth
else
strMonth = strMonth
end if


if Len(strDay)=1 then
strDay = "0" & strDay
else
strDay = strDay
end if


strFileName = DatePart("yyyy",Now()) & strMonth & strDay
strFileName = Replace(strFileName,":","")
'=================================================================================

'Variable Declarations
Const ForAppending = 8

'===============================================================================
'Main Body
On Error Resume Next


'CompName
strComputer = strIPvalue
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
'===============================================================================

'================================================================
'For INTERNET EXPLORER
Dim strIE
Set objWMIService2 = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2\Applications\MicrosoftIE")
Set colIESettings = objWMIService2.ExecQuery("Select * from MicrosoftIE_Summary")
For Each strIESetting in colIESettings
strIE= " INTERNET EXPLORER: " & strIESetting.Name & " v" & strIESetting.Version & VBCRLF
Next


'Get Operation System & Processor Information
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem in colItems
CompName = objItem.SystemName
Next

Set objFSO = CreateObject("Scripting.FileSystemObject")
if objFSO.FileExists(strPath & CompName & "_" & strFileName & "_Audit.txt") then
WScript.Quit
end if

'Set the file location to collect the data
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(strPath & CompName & "_" & strFileName & "_Audit.txt", ForAppending, True)

''==============================================================
'Print HEADER
objTextFile.Write "================================================================" & VBCRLF & VBCRLF
objTextFile.Write " SERVER RESOURCE AUDIT REPORT " & VBCRLF
objTextFile.Write " DATE: " & FormatDateTime(Now(),1) & " " & VBCRLF
objTextFile.Write " TIME: " & FormatDateTime(Now(),3) & " " & VBCRLF & VBCRLF
objTextFile.Write "================================================================" & VBCRLF & VBCRLF & VBCRLF & VBCRLF & VBCRLF


objTextFile.Write "COMPUTER" & VBCRLF
'==============================================================
'Get OPERATING SYSTEM & Processor Information
objTextFile.Write " COMPUTER NAME: " & CompName & VBCRLF

Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem in colItems
objTextFile.Write " PROCESSOR: " & objItem.Name & VBCRLF
Next

Set colProcs = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")

For Each objItem in colProcs
objTextFile.Write " NUMBER OF PROCESSORS: " & objItem.NumberOfProcessors & VBCRLF & VBCRLF
Next


'================================================================
'Get DOMAIN NAME information
Set colItems = objWMIService.ExecQuery("Select * from Win32_NTDomain")

For Each objItem in colItems
objTextFile.Write " DOMAIN NAME: " & objItem.DomainName & VBCRLF
Next

'================================================================
'Get OS Information
Set colSettings = objWMIService.ExecQuery("SELECT * FROM Win32_OperatingSystem")
For Each objOperatingSystem in colSettings
objTextFile.Write " OPERATING SYSTEM: " & objOperatingSystem.Name & VBCRLF
objTextFile.Write " VERSION: " & objOperatingSystem.Version & VBCRLF
objTextFile.Write " SERVICE PACK: " & objOperatingSystem.ServicePackMajorVersion & "." & objOperatingSystem.ServicePackMinorVersion & VBCRLF
Next
objTextFile.Write strIE & VBCRLF & VBCRLF & VBCRLF & VBCRLF





objTextFile.Write "MOTHERBOARD" & VBCRLF

'===============================================================
'Get Main Board Information
Set colItems = objWMIService.ExecQuery("Select * from Win32_BaseBoard",,48)
For Each objItem in colItems
objTextFile.Write " MAINBOARD MANUFACTURER: " & objItem.Manufacturer & VBCRLF
objTextFile.Write " MAINBOARD PRODUCT: " & objItem.Product & VBCRLF
Next



'================================================================
'Get BIOS Information
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem in colItems
objTextFile.Write " BIOS MANUFACTURER: " & objItem.Manufacturer & VBCRLF
objTextFile.Write " BIOS VERSION: " & objItem.Version & VBCRLF & VBCRLF & VBCRLF & VBCRLF & VBCRLF
Next

objTextFile.Write "MEMORY" & VBCRLF

'===================================================================
'Get Total Physical memory
Set colSettings = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
For Each objComputer in colSettings
objTextFile.Write " TOTAL PHYSICAL RAM: " & Round((objComputer.TotalPhysicalMemory/1000000000),4) & " GB" & VBCRLF
Next

objTextFile.Write " " & VBCRLF & VBCRLF & VBCRLF & VBCRLF & "PARTITIONS" & VBCRLF

'===================================================================
'Get Logical Disk Size and Partition Information
Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType = 3")
For Each objDisk in colDisks
intFreeSpace = objDisk.FreeSpace
intTotalSpace = objDisk.Size
pctFreeSpace = intFreeSpace / intTotalSpace
objTextFile.Write " DISK " & objDisk.DeviceID & " (" & objDisk.FileSystem & ") " & Round((objDisk.Size/1000000000),4) & " GB ("& Round((intFreeSpace/1000000000)*1.024,4) & " GB Free Space)" & VBCRLF
Next

objTextFile.Write " " & VBCRLF & VBCRLF & VBCRLF & VBCRLF & "NETWORK" & VBCRLF

'====================================================================
'Get NETWORK ADAPTERS information
Dim strIP, strSubnet, strDescription

Set colNicConfigs = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")

For Each objNicConfig In colNicConfigs
'Assign description values to variable
strDescription=objNicConfig.Description

For Each strIPAddress In objNicConfig.IPAddress
'Assign IP Address to variable
strIP=strIPAddress

For Each strIPSubnet In objNicConfig.IPSubnet
'Assign Subnet to variable
strSubnet = strIPSubnet
Next

objTextFile.Write " NETWORK ADAPTER: " & strDescription & VBCRLF
objTextFile.Write " IP ADDRESS: " & strIP & VBCRLF
objTextFile.Write " SUBNET MASK: " & strSubnet & VBCRLF & VBCRLF

Next

Next

Set colNicConfigs =NOTHING


'============================================================

objTextFile.Write " " & VBCRLF & VBCRLF & VBCRLF & VBCRLF & "APPLICATION" & VBCRLF

Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall"
objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

For Each subkey In arrSubKeys
strSubKeyPath = strKeyPath & "\" & subkey

strString = "DisplayName"
objReg.GetStringValue HKEY_LOCAL_MACHINE, strSubKeyPath, strString, strDisplayName

strString = "DisplayVersion"
objReg.GetStringValue HKEY_LOCAL_MACHINE, strSubKeyPath, strString, strDisplayVersion

strDisplayName=Trim(strDisplayName)
strDisplayVersion=Trim(strDisplayVersion)
If strDisplayName <> "" And strDisplayVersion <> "" Then
objTextFile.Write " " & strDisplayName & " " & strDisplayVersion & VBCRLF
End If
Next


'===========================================



'Close text file after writing logs

objTextFile.Write VbCrLf
objTextFile.Close

'Clean Up

SET colIESettings=NOTHING
SET colItems=NOTHING
SET colSettings=NOTHING
SET colDisks=NOTHING
SET AdapterSet=NOTHING
SET objWMIService=NOTHING
SET objWMIService2=NOTHING
SET objFSO=NOTHING
SET objTextFile=NOTHING


'===================================================================
END SUB



Function HostOnline(strComputername)


Set sTempFolder = objFso.GetSpecialFolder(TEMPFOLDER)
sTempFile = objFso.GetTempName
sTempFile = sTempFolder & "\" & sTempFile

objShell.Run "cmd /c ping -n 2 -l 8 " & strComputername & ">" & sTempFile,0,True

Set oFile = objFso.GetFile(sTempFile)
set oTS = oFile.OpenAsTextStream(ForReading)
do while oTS.AtEndOfStream <> True
sReturn = oTS.ReadLine
if instr(sReturn, "Reply")>0 then
HostOnline = True
Exit Do
End If
Loop

ots.Close
oFile.delete
End Function

If you are dealing with an organization with more than 50 computers and servers, it would still be tiresome to manually execute this script on each machine/server. A better way to do it is to have an Excel spreadsheet that contains the machine names/IP addresses of all the computers in your domain. read through the list and generate the text files based on that list. In my next blog entry, I will start with reading an Excel spreadsheet using VBScript and continue on to incorporate this script.

Making a fool of your applications - File System

An understanding of how your applications work will make it a bit easy for you to fix problems when they arise. One common scenario I have seen in most applications is the concept of application logging - creating logs using either the old-fashioned log files or storing the data in a database. Logging helps application developers troubleshoot application-related problems in production environments when the need arise. I've had my shares of creating application logs in the past where I use text files to store activities happening in the application. There's a downside in using this concept. One, you are introducing additional overhead in your application since you have to do an additional step, not to mention the I/O necessary to write the log in the file system or the database. Another downside is log maintenance. If you do not maintain the logs, it wouldn't take long before they fill up your disk, causing your application to fail. There are ways to work around this. You can toggle logging and only turn it on when needed. Another approach is to create a default log maintenance procedure in the application itself like truncating logs or deleting log files older than a specific date. Maintenance will be a terrible headache if not included as a part of the logging mechanism.

I have had the opportunity to deal with such a case. I had an encounter with an application which logs every transaction by creating XML files. XML is a great way to store data. But what I have seen for the past few years is that the use of XML has been misunderstood as something to replace a relational database. This misunderstanding of its purpose has caused a lot of problems particularly when it comes to performance. You see, in order for you to read the data in the XML file, you have to load it in memory before you can even do those methods as parsing using XPath and XQuery. Imagine doing this to load a million XML files. My problem was to delete the log files stored as XML. I couldn't just delete them since they have increased in number that simply running Windows Explorer has caused my session to hang. My next step is to delete the folder containing the log files. But it's not as easy as that. The folder containing the logs is being locked by the application which is typical of all applications creating logs. To work around this problem, I had to find out what application or service is, stop it the service, rename the folder containing the logs, create a new folder with the same name as that of the old log folder, restart the service and, then, delete the old folder. The application will still see the logs folder except that now it's a new folder but with the same name. This made sure that I can still do maintenance by deleting the logs folder while making sure that application downtime is kept at a minimum. Bottomline still remains, we people are indeed smarter than these machines.

Common Table Expressions in SQL Server 2005

I initially wrote this article for www.adminprep.com but apparently, I myself am having difficulty trying to access the site every now and then so I decided to mirror the content in my blog.

Have you ever wished that Microsoft created a feature in SQL Server 2000 that combines the capabilities of views and derived tables? How about writing recursive queries in T-SQL that does not need to be very taxing? With SQL Server 2005 Common Table Expressions, you can do both with ease. In their simple form, you can think of CTEs as an improved version of derived tables that more closely resemble a non-persistent type of view. Look at CTEs as your derived tables in SQL Server 2000. A CTE can be used in many of the same ways you use a derived table. CTEs can also contain references to themselves. This allows the developer to write complex queries simpler, like recursive queries. CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

To understand what a CTE is all about, let’s first take a look at the syntax to create it in SQL Server 2005.

Syntax

In general form a CTE has the following syntax:

WITH cte_alias(column_aliases)
AS
(
cte_query
)
SELECT *
FROM cte_alias

You provide the CTE with an alias and an optional list of aliases for its result columns following the keyword WITH which usually defines the derived table based on the query definition; write the body of the CTE; and refer to it from the outer query.

To put this in the right perspective, let’s come up with a very simple example. Using the Northwind database, we want to display the employee details along with a column that displays the number of orders that the employee has written. This is done by creating a table on the fly that summarizes this information from the orders table. We then join with this table in the INNER JOIN clause based on employee ID.

WITH Count_Orders(employee_ID, orderCount)
AS
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
SELECT employeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN Count_Orders
ON Count_Orders.employee_ID = E.employeeID

Now, a lot of people might say that this is quite simple. You can do this in SQL Server 2000 using the concept of derived tables or temporary tables. If you were to write the same query in SQL Server 2000, this is how it would look like.

SELECT EmployeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
AS Count_Orders (employee_ID, order_Count)
ON Count_Orders.employee_ID = E.emplyeeID

One might argue that these two don’t have that much of a difference. Now, imagine that you need to refer to the same derived table within the query. You would have to repeat the same definition, create an alias for it before you can use it again. As you increase the number of references, your code becomes pretty long but repetitive. With CTEs, you no longer have to do these things again and your code becomes easier to read. You can define multiple CTEs and incrementally build on the earlier CTEs or define new results that are then used later on.

Recursive Queries

Whenever a CTE refers to itself, it is considered to be a recursive query. Recursive CTEs are constructed from at least two queries. One is a non-recursive query, which is also referred to as the anchor member. The other is the recursive query, also referred to as the recursive member. These queries are separated by the UNION ALL operator.

Let’s take a look at a simplified generic form of a recursive CTE.

WITH RecursiveCTE(column_list)
AS
(
-- Anchor Member:
-- SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM some_table(s)_or_view(s)
...
UNION ALL
-- Recursive Member
-- SELECT query that refers to RecursiveCTE
SELECT ...
FROM some_table(s)_or_view(s)
JOIN RecursiveCTE
...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...

Let’s extend the example we used to demonstrate how to write recursive CTEs. Looking at the Employees table in the Northwind database, we see that a particular employee reports to another employee. One question we can come up with is, “Who reports to whom?” The Employees table of the Northwind database is designed in such a way that the ReportsTo column is a foreign key field that refers to the primary key field EmployeeID. Thus, we can create a query to answer our question. A sample query using CTE will look something like this.

WITH Managers AS
(
SELECT EmployeeID, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
SELECT e.employeeID, e.ReportsTo
FROM Employees e INNER JOIN Managers m ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

The sample query contains the elements that a recursive CTE must contain. What’s more is that the code is a lot more readable. To create a similar yet non-recursive query that produces the same result in SQL Server 2000, you might come up with something similar to this code:

DECLARE @rowsAdded int
--table variable to hold accumulated results
DECLARE @managers table
(EmpID int, MgrID int, processed int default(0))

--initialize @managers who do not have managers
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees
WHERE ReportsTo IS NULL

SET @rowsAdded=@@rowcount

--do this while new employees are added in the previous iteration
WHILE @rowsAdded > 0
BEGIN
--mark employee records going to be found in this iteration with
--processed=1
UPDATE @managers SET processed=1 WHERE processed=0

--insert employees who report to employees not yet processed
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees e
INNER JOIN @managers r ON e.ReportsTo = r.EmpID
WHERE ReportsTo <> EmployeeID and r.processed = 1

SET @rowsAdded = @@rowcount

--mark employee records found in this iteration as processed
UPDATE @managers SET processed=2 WHERE processed=1
END

SELECT * FROM @managers

The first thing you will notice is the codes were quite few when using CTEs compared to the usual T-SQL query you will have to create in SQL Server 2000. This enables the developers to write complex queries with ease. You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE. To use it in the previous example

SELECT * FROM Managers OPTION (MAXRECURSION 4)

Given the example above, hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused. As our data requirements become more complex, we need the proper tools to deal with them and the new generation of T-SQL is just the right tools that we need. In my next article, I will introduce more T-SQL enhancements in SQL Server 2008 which is similar to how to address problems like this using CTEs. SQL Server 2008 has introduced a new data type called heirarchyid which I will cover soon

Fancy up your reports - SQL Server 2005 Reporting Services:alternating colors on table rows

I'm not a good graphics guy. In fact, I'd hire somebody to do web design if I have a web application project. For me, anything to do with aesthetics is totally out of the picture. I wouldn't want to spend a couple of hours designing and mixing colors and pictures. In reality, anything that has something to do with web requires visual arts. Same thing with reports. You really have to find the right color combination and layout for your reports. I was struggling with how to do this on tables which display results of my dataset queries. All I know is that I can display my resultsets with a single color. I know I can do this in the dataGrid control in ASP.NET 1.1 but I am creating my reports in SQL Server 2005 Reporting Services. After searching thru my favourite (and everybody's probably) search engine, I found the answer. You can use expressions in just about any property in your reports. To do alternating background colors in a table, you can simply use an expression like this: =IFF(RowNumber(Nothing) Mod 2,"color1","color2") where color1 and color2 are the names which you can get from the list of colors I don't guarantee that it will look nice - that depends on your visual aesthetics and how you appreciate color combination. What I did in my case was to extend this expression since I had a different color for my column header to identify the column names. My new expression would be =IIF((RowNumber(Nothing)+1) Mod 2,"color1","color2") so that the new color would start on the second row after the column header instead of the first one. I might be needing this in other functionalities like generating a report using the Matrix control so I better start looking for alternatives as early as now