Earlier, you saw how NightWatchman clients are viewed on the Clients view in the NightWatchman console, and that this view provides two grouping structures (Location and Organization) through which clients are presented. You may have already found that it is difficult to find clients that are buried within the two grouping structures – the search function only works within groups at the lowest tier level, so you must know what group a client is in before you can search for it.
NightWatchman 7 introduced an additional tab on the Clients view labelled Queries. The Queries tab allows an administrator to execute a number of pre-defined queries and display the results in the console using the format of any of a number of predefined views. It is possible to define additional queries and views, but this requires directly editing the AgilityFrameworkReporting database. Once the results have been returned to the console, they can be copied to the clipboard or exported to a CSV file.
One or more machines can be selected from the query results in the console and woken up using a simple right-click action. This is similar in concept to being able to wake up one or more clients within a Location or Organization group, but now you can select the machines you want to wake up using various queries rather than being restricted to your current group definitions. NightWatchman 7 also introduced a feature that allows administrators to define custom actions that can be executed from this right-click menu that can use columns from the query results as parameters in the action command line. For example, you could create an action that opens the NightWatchman log file from a client (or multiple clients) selected in the query results.
In this lab, you will explore the query user interface, have a go at adding a new query and view and then add a custom action that can be executed by right-clicking one or more clients in the query results.

Using Pre-defined Queries and Views

In this exercise, you will observe the queries and display views that are predefined when NightWatchman is installed.

To execute a query, you must select a Query and a Display. The Display defines the columns that will be returned in the results and is directly related to a SQL View in the AgilityFrameworkReporting database. The table below provides the mapping between the Displays and the associated SQL Views.


Query Display

AgilityFrameworkReporting Database View

Hardware

dbo.vwNWM_Query_Hardware

Hierarchy

dbo.vwNWM_Query_Hierarchy

Network adapters

dbo.vwNWM_Query_Network

Power

dbo.vwNWM_Query_Power

Software

dbo.vwNWM_Query_Software

Summary

dbo.vwNWM_Query_Summary

The Query and Criteria fields help limit and filter the results returned from the selected Display.
The Query will default to All computers and the Display to Hardware. If any query other than All Computers is selected, the Criteria box will be enabled. For some queries, the Criteria field will be a drop-down or combo-box control, which has a number of options you can select, or it will be a free text box where you can simply type your criteria. By default, Obsolete machines are excluded from the results, so if you want to include these you must check the Include obsolete machines in results option.

1ETRNAP



Start the NightWatchman Console on 1ETRNAP and select the Clients tab


In the Clients page, select the Queries tab


With the default Query (All Computers) and Display (Hardware) selected, click Submit and observe the results set returned


Note that it is possible to select all items (by checking the Select All checkbox) or you can select one or more items individually. Selected items can be copied to the clipboard, or exported to CSV using the Export items to CSV file… link on the top-right of the query results. The results can be sorted by clicking the column heading, and columns can be dragged around to change the order they appear in from left to right.


The columns that are included in the results are defined by the Display option you selected (Hardware in this case). Change the Display to Hierarchy and click Submit. Observe the different columns that are displayed. Try each of the different Display options to see the different pre-defined views available


Now change the Query to By subnet. Note that the Criteria field is now enabled in the form of a combo-box, populated with the list of client subnets (based on agent inventory). Select 10.0.1.0 from the drop-down list and click Submit. You should see three lab workstations 1ETRNW71, 1ETRNW73 and 1ETRNW101 returned, amongst other possible machines from the restored database


Now change the Query to By hardware and note the Criteria field is now a free text box. Enter Vostro in the Criteria text box, select Hardware as the Display option and click Submit


The Hardware query has been defined to use wildcards around the Criteria you specified, so the above step will return any computer that has "vostro" in either the Chassis Manufacturer or Chassis Model columns.


Have a play with the other queries to get an idea of what is available


It is possible to restrict access to the Queries tab through Role Security, defined in the Security tab in the NightWatchman Console.


Adding Custom Queries and Views

In this exercise, you will learn how the pre-defined queries and views are stored in the AgilityFrameworkReporting database and how to add your own custom queries. For this exercise, you will use an example SQL query and view as it is not within the scope of this course to cover the details of the AgilityFrameworkReporting database or SQL query structure. In your production environment, you can always use 1E Professional Services to define new queries and views according to your requirements if you do not have the skills to do this yourself.

Examining the Pre-defined Queries

The Queries presented in the Query drop-down box in the NightWatchman Console are defined in the AgilityFrameworkReporting database, specifically in the tbNWM_Query_QueryList table. In this task, you will examine one of the pre-defined queries and learn the requirements for defining queries.

1ETRNAP



On 1ETRNAP, start SQL Server Management Studio, expand the AgilityFrameworkReporting database and then expand the Tables node


Right-click the tbNWM_Query_QueryList table and select Select Top 1000 rows. The following table identifies the columns in this table


Column

Description

Id

Unique ID and key for each query

Name

The name of the query as it appears in the Query drop-down

Description

Description of the query as displayed to the right of the Query drop-down

Sql

The SQL query that will return the full dataset

CriteriaSql

Optional SQL query to return items to populate the Criteria drop-down

IsCriteriaEditable

When using the CriteriaSql to populate the Criteria drop-down, you can set this column to 1 to enable the user to use their own text as well as the list options (i.e. the Criteria control is a Combo-box). If this column is set to 0 the user will only be able to select criteria from the list


Below is a copy of the Sql column for the By NetBIOS name query


SELECT [CI].[Id] FROM [dbo].[tbAFR_Dimension_ConfigurationItem] [CI] WHERE [CI].[NetbiosName] LIKE '%{CRITERIA}%'


All queries must return just the Configuration Item ID (tbAFR_Dimension_ConfigurationItem.Id) which is used to identify the machines in the result set. Views (which we will look at next) define which additional columns associated with that ID are returned in the result set. Note the use of the {CRITERIA} placeholder. When the query is executed, this will be substituted with whatever the user entered or selected in the Criteria box.


Now look at the By subnet query. This includes the following in the CriteriaSql column



SELECT DISTINCT [SubNet] AS [Criteria] FROM [dbo].[tbAFR_Dimension_NetworkAdapters] WHERE LEN(Subnet) > 0 ORDER BY [SubNet]


This 'sub-query' returns a distinct list of subnets from the inventoried Network Adapters and returns these in alphanumeric order in a column named Criteria. This will be used to populate the Criteria dropdown list in the NightWatchman console. The selected option will be substituted in the {CRITERIA} placeholder in the main query in the Sql column.


CriteriaSql must return a single column, aliased as Criteria, which should be de-duplicated and ordered as it should appear in the drop-down list.

Adding a Custom Query

You will now learn how to add a custom query to the Query drop-down. The purpose of this task is to show you how custom Queries can be added rather than how to build the SQL query, so you will use a query that has been prepared for you.

1ETRNAP



On 1ETRNAP Navigate to C:\Temp\Scripts (you downloaded and unzipped this earlier)


Double-click C:\Temp\Scripts Query-ByComputerOU.sql (remove the .txt extension first) and open it in the SQL Query Editor window, in the available databases droplist select AgilityFrameworkReporting, and execute the query


The sample provides a query to find all machines in a given OU, along with a Criteria sub-query used to list all known OUs from the database. Select the main SQL query below the Query comment as shown below and copy this to the clipboard


SELECT          [CI].[Id]
FROM            [dbo].[tbAFR_Dimension_ConfigurationItem] [CI]
INNER JOIN      [dbo].[tbAFR_Dimension_ComputerSystem] [CS]
	ON          [CI].[Id] = [CS].[ConfigItemId]
WHERE           ISNULL(CS.[MachineOU], '(none)') = '{CRITERIA}'


Now right-click the tbNWM_Query_QueryList table and select Edit Top 200 Rows


In the empty row at the bottom of the table, click in the Name column and enter By OU, then tab to the Description column and enter Select computers in the specified OU


Tab to the Sql column and paste the SQL query from the clipboard


Return to the Query Editor Windows that has Query-ByComputerOU.sql open and select the criteria query below the Criteria comment as shown below and copy this to the clipboard


SELECT DISTINCT CASE WHEN ISNULL(MachineOU, '') = '' THEN '(none)' 
                     ELSE MachineOU 
                END AS [Criteria]
FROM            [dbo].[tbAFR_Dimension_ComputerSystem] AS CS 
ORDER BY        1


Return to the query Editor Window where you are editing the tbNWM_Query_QueryList table and paste the clipboard into the CriteriaSql column in the By OU query row


Enter False in the IsCriteriaEditable column and press Enter to save the new row


Close the NightWatchman console and re-open it


The query list table is cached when the console is opened, so changes will not be reflected in the console until it is restarted.


Select Clients then select the Queries tab


From the Query drop-down, select By OU and note the Criteria field is now populated with a list of OUs that have been identified through NightWatchman inventory. Select the Workstations OU and click Submit to view all the computers in the Workstations OU



It is important to ensure the SQL queries used to create custom queries in NightWatchman have been properly tested before inserting into the table for use by the console. Incorrect queries could result in inaccurate data being generated via the queries.

Examining the Pre-defined Views

In the previous task, you saw that the Query only returns a single column (tbAFR_Dimension_ConfigurationItem.Id). The columns that are displayed in the console when a query is executed are determined by the view selected in the Display drop-down. In this task, you will examine one of the predefined views and learn the requirements for defining views.

1ETRNAP



In SQL Server Management Studio, in the AgilityFrameworkReporting database right-click the tbNWM_Query_Views table and select Select Top 1000 Rows. The following table identifies the columns in this table


Column

Description

Id

Unique ID and key for each view

Name

The name of the view as it appears in the Display drop-down

Value

The name of the SQL View that will be used when this view is selected

Description

A brief description of what the view displays. This appears to the right of the Display drop-down when the view is selected


Note the *Hardware* view uses the SQL view named [dbo].[vwNWM_Query_Hardware]


In SQL Server Management Studio, expand the Views node and locate the vwNWM_Query_Hardware view. To examine the view, right-click it and select Script View as > CREATE To > New Query Editor Window


Observe the first four columns included in this view are ID, NetbiosName, DomainName and Obsolete



SELECT          [Base].[Id]    
,               [Base].[NetbiosName]
,               [Base].[DomainName]
,               [Base].[Obsolete]


These columns are required in every view and should always be obtained from the predefined vwNWM_Query_Base view, which is then joined with the other database tables that include the columns we want in the result set.

Adding a Custom View

In this task, you will learn how to add a custom view. Again, we will use a prepared example, as the purpose is to familiarise you with the process rather than the actual SQL coding.

1ETRNAP



On 1ETRNAP navigate to C:\Temp\Scripts


Double-click C:\Temp\Scripts\View-TimeInStateOverLast90Days.sql to open it in SQL Server Management Studio


Note that this script will create a SQL View named vwNWM_Query_Custom_TimeInState and also add a row to the tbNWM_Query_Views table so it appears in the Display dropdown list in the console


Ensure the AgilityFrameworkReporting database is selected then click Execute to create the SQL view and add it to the tbNWM_Query_Views table


Close and re-open the NightWatchman Console


Select Clients then select the Queries tab


Select any Query and Criteria of your choice and select the Time in state view from the Display drop-down


Note that the new view shows the percentage of time that each machine has spent in each power state over the last 90 days


Working with Console Extensions

The console extensions feature enables an administrator to define custom actions that can be executed against (or using parameters from) the selected items in the Query result set. In this exercise, you will create a simple custom action that will start a Remote Desktop Connection to the selected computer.

Adding a Console Extension

Console extensions are defined in Console.Extensions.xml, which is located in the Console sub-folder of the NightWatchman Management Center installation directory. When the console is first installed, this file has an additional .template extension, which you remove when you are ready to start adding console extensions to the file.

Note that because this is a local file in the Console installation directory, any extensions defined will only be available on the local machine. If you have the NightWatchman Console installed on multiple computers, you can change the location that the console looks for this file to a network share, so the custom actions can be shared by all users. The location of the file is defined in the Extensions.Folder key in the NWM.Console.exe.config file. By default, this key is commented out and the Console installation folder is the expected location of Console.Extensions.xml.


1ETRNAP



On 1ETRNAP navigate to C:\Program Files (x86)\1E\NWMMC\Console in Windows Explorer


Open Console.Extensions.xml.template with Notepad


Note that the template file already includes an extension to start a Remote Desktop session as a sample. A Console Extension has the following attributes


Attribute

Description

Name

This is the text that appears in the right-click menu

Description

This is the tooltip text that appears when hovering over the item

Scope

The scope of the action defines whether it can be used on a single computer (Computer), a set of one or more computers (ComputerSet) or a Location or Organization grouping tier (Tier)

CommandLine

The command line that will be executed when the action is selected. This can include different substitution strings depending on the Scope of the action, as follows

Scope

Placeholders

Substituted with

Computer

{$ComputerID}
{$NetbiosName}
{$Domain}

tbAFR_Dimension_ConfigurationItem.ID
NetBIOS computer name
NetBIOS domain name

Tier

{$TierId}
{$TierLevelId}
{$TierType}
{$TierName}

tbAFR_Dimension_Tier.Id
tbAFR_Lookup_TierLevel.ID
tbAFR_Lookup_TierLevel.Type
tbAFR_Dimension_Tier.Name

ComputerSet

{$ComputerListFileName}

The full path and name of a comma-delimited file that defines the ConfigItemID, NetBIOS name and Domain name of each of the selected items. The command line would need to parse this file and execute the action for each row in the file.



Save the file as Console.Extensions.xml (remove the .template extension) – the file with the sample console extension is now 'live'


Open the NightWatchman Console (if it is already open, close and re-open it to refresh the console extensions) and select the Queries tab on the Clients page


Run a query to return the lab workstations (you can use the NetBIOS Name containing 1ETRNW as your query) and right-click one of the workstations returned in the result set


Note there is now an Actions menu option. Select this and click the Remote Desktop action (this is the sample action that was defined in Console.Extensions.xml)


Ensure the machine you have selected is powered on.


The action launches a Remote Desktop session on the selected computer. If the selected computer is on and accessible, you will be prompted for credentials (you can log in with 1ETRN\user and Passw0rd if you want to start the session). Close the Remote Desktop session when you are done



Note that the custom actions initiated through the console are executed in the security context of the logged-on user – the user must have whatever permissions are required to execute the action.


Console extensions are essentially command lines executed when initiated. Thus, you can script any custom action within the NightWatchman environment as long as it can be executed via a command line. The command line for remote desktop, for example, is mstsc /v {$netbiosname}.

Lab Summary

In this lab, you have learned how to use the Query feature of the NightWatchman Console to find clients using various pre-defined queries. You have seen how the results of the query can be modified using the display options. There are a number of common queries and display views pre-defined, but you have also learned how you can create your own queries and display views by editing the database directly.
You then learned how you can extend the functionality of the NightWatchman Console by defining custom command-line actions in Console.Extensions.xml. These actions are then available by right-clicking the query results. The action executes a command line that can include substitution strings for properties of the selected item (such as NetBIOS Name), enabling you to do things like start a Remote Desktop session, ping the computer or open the 1E Agent log files remotely. You use the Scope setting in the console extension definition to determine whether the action should be available to just a single computer, to a set of computers or to a grouping tier. Where an action is applied to a set of computers, the console will generate a comma-separated file containing the ConfigItemID, NetBIOS Name and Domain Name of each computer selected. The action (e.g. a script) would then need to use the {$ComputerListFileName} substitution string to identify the full path and name of that file, then parse the file and execute the desired actions for each row in the file.

Next Page

Ex 3 - NWM v7.3 - Using - Working with Policies