Start Excel 2002 from NTreport, format the report by sending keyboard strokes to Excel

Start Excel 2002 from NTreport, format the report by sending keyboard strokes to Excel

How it works

NTreport writes data to a file, opens the file in Excel, sends key strokes to Excel to format the data.

You can use the principles in this example to control any word processing, spreadsheet or database application on the market - as long as a program can read a clear text file and receive keyboard strokes you can use it to format NTreport data.

Version compatibility

This example was written for Excel 2002 (Office XP) but it works on all Excel versions with minor or no modifications. Notice that keyboard shortcuts can differ between different language versions of Excel.

Output example

Task

Collect data from three servers and create a formatted report in Excel.

The report should look like this:

RAM \\SERVER1

total RAM, free RAM (free %)

Security log \\SERVER1

event records

Application log \\SERVER1

event records

RAM \\SERVER2

total RAM, free RAM (free %)

Security log \\SERVER2

event records

Application log \\SERVER2

event records

Create the script

In the SetupBatcher / NTreport editor:

·	Click "Action" > "Create list of machines" > "NT servers" and choose domain

The result should look something like this:

[Machines_Begin]
\\EDU8   D2   NT PDC
\\EDU9   D2   NT PDC
\\EDU4   D2   NT PDC
[Machines_End]

In NTinfo:

·	Click the "Domain" radio button and make sure the combo box says "LIST".

·	Under General options

Uncheck "Show scan"

·	Under Hardware

Check "RAM"

·	Under Event log reader

Check the "Log" check box

In the "Date" field, enter "DAY:0-1"

·	Click the NTreport button "Create script from GUI"

You should now have a script that looks like this:

[Settings_Begin]
FileType=NTreport
NTinfo.RAM=Yes
NTinfo.Event.Log=Yes
NTinfo.Event.Log.Log=*ALL*
NTinfo.Event.Log.Date=DAY:0-1
[Settings_End]

[Machines_Begin]
\\EDU8   D2   NT PDC
\\EDU9   D2   NT PDC
\\EDU4   D2   NT PDC
[Machines_End]

[Batch_Begin]
Clear.Window Main
Get.Data *LIST*
[Batch_End]

Press CTRL+R to run the script.

Here's what the output will look like in the NTinfo output window:

We are now going to use the Filter command to filter rows that contains the strings "ELOG_Application", "ELOG_Security" and "RAM". If such rows are found they are written to the file c:\output.txt with the Write.File command. Then we open the file in Excel.

If you give Excel a TAB delimited file as in-parameter it will automatically put the tab separated data in different cells. Since we are going to export a range of columns we must specify range delimiter; in [Settings] add the row:

NTreport.Delimiter=%TAB%

And since we want to display the data from each machine separately we must set FlowMode to 2; in [Settings] add the line:

NTreport.FlowMode=2

(In FlowMode 2 the Get.Data command must be put inside a Machines.Loop..Machines.EndLoop statement, also, in this mode Get.Data has no parameters.)

Here's the finished script that extracts data, filters it, creates a file and opens the file in Excel:

[Settings_Begin]
FileType=NTreport
NTinfo.RAM=Yes
NTinfo.Event.Log=Yes
NTinfo.Event.Log.Log=*ALL*
NTinfo.Event.Log.Date=DAY:0-1
NTreport.Delimiter=%TAB%
NTreport.FlowMode=2
[Settings_End]

[Machines_Begin]
\\EDU8   D2   NT PDC
\\EDU9   D2   NT PDC
\\EDU4   D2   NT PDC
[Machines_End]

[Batch_Begin]
DOS del c:\output.txt
Machines.Loop
Clear.Window Main
Get.Data
Write.File c:\output.txt ;; RAM %machine%
Clear.Window Filter
Filter %col2%=RAM
If filter
  Write.File c:\output.txt ; Filter ; %TAB%%col3%M, %col4%M free (%col5%)
EndIf
Write.File c:\output.txt ;; Application log %machine%
Clear.Window Filter
Filter %col2%=ELOG_Application
If filter
  Write.File c:\output.txt ; Filter ; %TAB%%col3-%
EndIf
Write.File c:\output.txt ;; Security log %machine%
Filter %col2%=ELOG_Security
If filter
  Write.File c:\output.txt ; Filter ; %TAB%%col3-%
EndIf
Machines.EndLoop
DOS start excel.exe c:\output.txt
[Batch_End]

Result:

It needs formatting!

To format we use the Keyboard command to send key strokes to Excel.

A search for "keyboard shortcuts" in Excel returns some useful information:

And by looking at underscores in the main menu we can find out ALT+key combinations:

Here's the finished script:

[Settings_Begin]
FileType=NTreport
NTinfo.RAM=Yes
NTinfo.Event.Log=Yes
NTinfo.Event.Log.Log=*ALL*
NTinfo.Event.Log.Date=DAY:0-1
NTreport.Delimiter=%TAB%
NTreport.FlowMode=2
[Settings_End]

[Machines_Begin]
\\EDU8   D2   NT PDC
\\EDU9   D2   NT PDC
\\EDU4   D2   NT PDC
[Machines_End]

[Batch_Begin]
DOS del c:\output.txt
Machines.Loop
Clear.Window Main
Get.Data
Write.File c:\output.txt ;; RAM %machine%
Clear.Window Filter
Filter %col2%=RAM
If filter
  Write.File c:\output.txt ; Filter ; %TAB%%col3%M, %col4%M free (%col5%)
EndIf
Write.File c:\output.txt ;; Application log %machine%
Clear.Window Filter
Filter %col2%=ELOG_Application
If filter
  Write.File c:\output.txt ; Filter ; %TAB%%col3-%
EndIf
Write.File c:\output.txt ;; Security log %machine%
Filter %col2%=ELOG_Security
If filter
  Write.File c:\output.txt ; Filter ; %TAB%%col3-%
EndIf
Machines.EndLoop
DOS start excel.exe c:\output.txt
// Wait for Excel to start before sending key strokes
Wait 2000
Keyboard CAPSOFFSCROLLOFFNUMOFF
// CTRL+SPACE = Select the entire column
// Column 1: CTRL+b = bold text
Keyboard CTRL+SPACECTRL+b
// Column 2-8: RIGHT = right arrow key, ALT+oca = format / column / autofit selection
Keyboard RIGHTCTRL+SPACEALT+ocaRIGHTCTRL+SPACEALT+ocaRIGHTCTRL+SPACEALT+ocaRIGHTCTRL+SPACEALT+ocaRIGHTCTRL+SPACEALT+ocaRIGHTCTRL+SPACEALT+ocaRIGHTCTRL+SPACEALT+oca
// Column 9: ALT+ocw50ENTER = format / column / width = 50
Keyboard RIGHTCTRL+SPACEALT+ocw50ENTER
// Column 9: ALT+oeRIGHT  = format / cells / alignment, ALT+w = wrap text, ENTER=OK
Keyboard ALT+oeRIGHTALT+wENTER
// CTRL+a = select all
Keyboard CTRL+a
// ALT+oe  = format / cells / alignment,  ALT+v = text alignment vertical, UPUP=top,  ENTER=choose, ENTER=OK
Keyboard ALT+oeALT+vUPUPENTERENTER
// CTRL+HOME = cursor to cell A1
Keyboard CTRL+HOME
[Batch_End]

The result will look like the first screen shot in this example.

Now it is up to you to use the Keyboard command to add more formatting, print and save the Excel file!