WMI - Introduction

In an enterprise environment, WMI lets you to query and update information on clients machines and applications.  It provides a standarize means for managing a system whether it is a local or in a network.  Developers can use WMI programmatically by Windows Scripting, C++/VB and of course .NET.

WMI Installer Provider

It lets us to access and manage Windows Installer through WMI classes.  Following are the core classes of WMI Installer Provider:

• Win32_Product
• Win32_SoftwareElement
• Win32_SoftwareFeature

The Win32_Product class contains methods to install, uninstall and uninstall an application programmatically.

In addition, Windows provides set of tools to manage WMI.  These are:

• WMI CIM Studio, WMI Object Browser, WMI Event Registration and WMI Event viewer  - see MSDN.
• WinMgmt.Exe - For manage
• WbemTest.Exe - WMI Tester tool

WMI in .NET

The System.Management class provides rich set of classes to manage WMI in .NET.  See: MSDN Library or .NET SDK Documentation.

In the froser_foo thread, a sample code has been provided.

In the next series, I'll explain the problem I'm still facing with WMI Installer Provider in very detail.

C...U...

I came to know that (not surprisingly) you cannot decode the details in MS SQL Server transaction log other than using third party tools like AuditDB, Idera SQL Compliance and so on.  These external tools are among the Microsoft wing to provide transaction log information.  For others, Microsoft does not give any official support/documentation.   Thats the business trick. :)

Okay, beware when you purchase third party tools.  Check that whether they support transaction log or use automated triggers for each and every tables in a database?

One good thing I came to know is SoftTree Technologies recently released database auditing API for SQL Server, Oracle and Sybase.  (Source: http://snipurl.com/SoftTreeAuditDBAPI).  But for this, you have to pay a big amount.

Bye.

Two months before, I received a requisition to automate the installation of a set of MSIs for our products on any remote machine.  The strategy of the this automated installation has some intelligent alignments (thanks to my team managers) of product features with related MSIs.  The GUI part of the tool is used to configure such things.  The command line tool just take a configuration and install a set of features on the given machines.

The basic functionality of the command line tool is to install a set of MSIs on one or more remote machines.  For this, we planned to used WMI.  Before that I briefly explain the environment.

Development Platform: .NET 2.0

Server Platform: Windows Server 2003 Ent

Decided that all the machines are connected in a same network domain.  We are successfully implemented the command line tool (here after I called CMDTL).  After the first test, we met a problem while installing a MSI on remote machine.  The story is really complicated and also by the help of fraser_foo, I was moved some more steps for the solution, but...?! (okay).  At the time of development, I saw a thread in Microsoft forum initiated by fraser_foo.  You can visit that thread by just googling "Remote MSI Installation" or visit that thread.  You can see a long replies.

Okay, Let me explain the story.

Let three machines A, B and C in a domain called "XDOMAIN".  And let consider the user "ADMIN_USER" has all administrator credential on all machines.  The domain is working in ADS (Active Directory service) enivronment.

A: Here, I'm running the CMDTL.

B: On this, the MSI packages are installed and shared with all set of permission to ADMIN_USER.  Let take a sample MSI called SAMSETUP.msi.

C: The target machine on which I need to install SAMSETUP.msi.

All the three machines are running in Win2003 Server with WMI Installer Provider.

Note:

By default, WMI Windows Installer Provider is not installed in Win2003.  You have to install by

select Start > Control Panel > Add or Remove Programs.

In Add or Remove Programs window,  press Add or Remove Windows Components button. 

In Windows Components Wizard window, select Management and Monitoring Tools from the Components list.  Press Details.. button. 

In Management and Monitoring Tools window, select WMI Windows Installer Provider in the Subcomponents of Management and Monitoring Tools list.

Press OK.

See the below scenario:

Scenario 1:

Running CMDTL in A.  SAMSETUP.msi file in B. Target machine is A itself.

Result --> PASS

Scenario 2:

Running CMDTL in A. SAMSETUP.msi file in C.  Target machine is C.

Result --> PASS

Scenario 3:

Running CMDTL in A. SAMSETUP.msi file in B.  Target machine is C.

Result --> FAIL

But we really need the third scenario only.

Okay, In this article, I'm going to explain the below:

• WMI
• WMI Installer
• WMI in .NET
• Problem with WMI Installer
• WSH
• Remoting Scripting

C...U.. I'll meet on series 2.

::fn_dblog

I've tried more to know the details of transaction log column information.  During this time, I found a system function ::fn_dblog which is more convenient than DBCC LOG for the following reasons even though it does not have any documentation:

1. DBCC LOG consumes more power to retrieve transaction log info.

2. It is a DML query and flexible than DBCC.

Syntax: SELECT * FROM ::fn_dblog(<START_LSN>, <END_LSN>) [WHERE ...]

START_LSN :  Starting Log Sequence Number

END_LSN: Ending Log Sequence Number

We know that the transaction log keeps records by  CURRENT LSN column.  Every transaction entry can be identified by an unique LSN.  The fn_dblog function fetches all the records from log.  Here you can pass start & end LSN to limit your search, otherwise you should NULL on both.

Example:

Use MyTestDB
SELECT * FROM ::fn_dblog(NULL, NULL)

Following column are displayed:

First section:

Current LSN                         Operation                       Context                     Transaction ID
-----------------------------------------------------------------------------------------------------------------
0000003a:00000077:0001 LOP_SET_BITS                LCX_DIFF_MAP          0000:00000000
0000003a:00000077:0002 LOP_BEGIN_XACT           LCX_NULL                  0000:00000c1b
0000003a:00000077:0003 LOP_MODIFY_COLUMNS LCX_CLUSTERED        0000:00000c1b
0000003a:00000077:0004 LOP_SET_BITS                LCX_DIFF_MAP           0000:00000000

Second Section:

Previous LSN                       FlagBits Alloc                       Alloc                         Page ID
                                                         UnitID                    UnitName
-------------------------------------------------------------------------------------------------------------------
00000000:00000000:0000 0x0000 6488064                 Unknown Alloc Unit 0001:00000006
00000000:00000000:0000 0x0200 NULL                       NULL                       NULL
0000003a:00000077:0002 0x0200 281474981691392 sys.sysdbfiles.clst   0001:00000055
00000000:00000000:0000 0x0000 6488064                 Unknown Alloc Unit 0001:00000006

and more...... 

In this, I can understand the purpose of the following column based on the semantics:

Operation -

Which type of operation occurred.

Some meaning values are: LOP_BEGIN_XACT - Internal checkpoint of an INSERT or UPDATE transaction.

LOP_MODIFY_COLUMNS - update statement called and LOP_INSERT_ROWS - insert row.

Alloc Unit Name  - object name

Okay come back to the function.

In this function, you cannot pass the LSN value as displayed above (0000003a:00000077:0001).  The function accepts only number formated NVARCHAR values.  So, we have to convert these hexa-decimal to NVARCHAR.

How........?

You can do it in two ways:

I. Using your calc (Calculator in WinXP)

   1. Let the hexa 0000003a:00000077:0001.  It contains 3 parts separated by ':'.

   2. Open you calc in hex mode.

   3. Cut the first part of hexa and paste it into calc.

   4. Change the mode to decimal.

   5. Copy the displayed value to a notepad.

  6. Do the step 3 - 5 for other 2 parts.

  7. Finally, you will get a string '58:119:1'.

Instead of above fn_dblog call, use the below:

Use MyTestDB
SELECT * FROM ::fn_dblog('58:119:1', '58:119:4')

It displays only LSN from 0000003a:00000077:0001 to 0000003a:00000077:0004.

II. Programmatically (Thanks killspid: http://killspid.blogspot.com/)

Here is the excerpt from his blog:

SET NOCOUNT ON
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)
SET @LSN = (SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))
PRINT @LSN
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @LSN_HEX =
(SELECT i FROM @tbl WHERE id = 1) + ':' + (SELECT i FROM @tbl WHERE id = 2) + ':' + (SELECT i FROM @tbl WHERE id = 3)
PRINT @LSN_HEX
SELECT *
FROM ::fn_dblog(@LSN_HEX, NULL)

Okay, but how can i know the details of other columns?

Its a series of PAIN....

Last week, I've received a problem from my internal team to find out a way to logg all CRUD (Create, Read, Update and Delete) transactions happened in a SQL Server database using transaction log file.  They have analysed and did some analysis on other options like SSIS, stored procedure replication in OLTP, etc.  But they want to know in transaction log file.

I've taken this and was searching that is there any APIs to access transaction log. After a while, I found that SQL Server does not provide any API to access transaction log. Instead, we can use DBCC LOG commands to access transaction log.

Though, The DBCC LOG is an undocumented command, I can read transaction log, but the information are in either enumerated or Hex format. And there is not documentation found about the details displayed by DBCC LOG.

And one of the major drawback is that transaction log does not keeps to log SELECT queries. 

I found number of third party utilities who used transaction log to fetch the information and display them in user understandable format.

Still I'm searching and analysing the same, but I cannot find a result.

How can we define the term Industry?

In general, other industries receive the resources from upstream suppliers. Using the product line tools or machines, they automate the production. Finally, they distribute products across supply chains of highly supplied and interdependent suppliers.

As in the above paragraph, we (the software industry) are doing the same thing, then how do the other people differ from us?

In the above paragraph, look at the following terms:

· Resources from upstream suppliers.

· Product line tools.

· Automation of production.

We slightly differ from other industries to do the above tasks. Let us understand the nuances of the above terms:

Resources from upstream suppliers:

All successful industries are working in modular and scale-up manner. For example, the automobile industries purchased their resources for their automobile from upstream suppliers. The resources are produced based on the industry standard and it is directly used into the production without performing any modification.

Software industry also well matured in platform based approach. You can purchase any third party .NET WinForm controls and you can directly integrate into your application. It is very uncommon to meet compatibility issues. But we are more dependants on platform components. We do not have any universal standard in most parts unlike in services oriented middleware. Now only XML Web Services have matured to some extent and are interoperable with other platform components with the great advent of XML.

Product line tools

Every product (eg. Indica, Sumo) in a product family (eg. Tata Motors) share common features across the members in the product family (eg. transmission and drivetrain components). And every product differs from others by its unique features. Industries approach these features by using product line tools.

The common features are implemented and kept as a core asset for product development. New product can be produced by assembling existing core assets and implement of unique features.

Our industry does not use any standard mechanism of developing product line tools and their usage.

Automation of production

To be honest, how many of us used internally developed automated tools in our product development. Yep, some of us have automated tools for generating objects for a relation database. Some of us may have used them to generate standard UI. But how do we use the automated tools through SDLC is a matter of concern in the software factory arena.

Okay, then what is Software Factory?

Simply put, “Economies of reuse” is the basic slogan of software factory. In a given domain, reusing solutions to common sub problems is called economies of reuse. It reduces total cost of solving multiple problems in the given domain. Let’s take automobile industry, after the designing of a car model, a bulk of cars can be produced in the production phase. Unlike this, in our industry, after the design, a system has been developed. The number of cars produced in production phase will also affects the cost of production. But in our industry, the number of copy of the same application does not affect the cost of production (just a cost of CD/download time). This is where our industry differs from others. So, the production phase in other industry naively compared with the development process in our industry.

I’ll explain this concept in detail in the next article.