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.