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.

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.

1. Start the NightWatchman Console on 1ETRNAP and select the Clients tab
2. In the Clients page, select the Queries tab
3. 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.
4. 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
5. 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
6. 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.
7. Have a play with the other queries to get an idea of what is available
8. 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.

9. On 1ETRNAP, start SQL Server Management Studio, expand the AgilityFrameworkReporting database and then expand the Tables node
10. 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

11. 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.
12. 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]

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.

13. On 1ETRNAP Navigate to C:\Temp\Scripts (you downloaded and unzipped this earlier)
14. 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
15. 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}'

16. Now right-click the tbNWM_Query_QueryList table and select Edit Top 200 Rows
17. 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
18. Tab to the Sql column and paste the SQL query from the clipboard
19. 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

20. 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
21. Enter False in the IsCriteriaEditable column and press Enter to save the new row
22. 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.
23. Select Clients then select the Queries tab
24. 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

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.

25. 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

26. Note the *Hardware* view uses the SQL view named [dbo].[vwNWM_Query_Hardware]
27. 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
28. Observe the first four columns included in this view are ID, NetbiosName, DomainName and Obsolete

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

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.

29. On 1ETRNAP navigate to C:\Temp\Scripts
30. Double-click C:\Temp\Scripts\View-TimeInStateOverLast90Days.sql to open it in SQL Server Management Studio
31. 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
32. Ensure the AgilityFrameworkReporting database is selected then click Execute to create the SQL view and add it to the tbNWM_Query_Views table
33. Close and re-open the NightWatchman Console
34. Select Clients then select the Queries tab
35. Select any Query and Criteria of your choice and select the Time in state view from the Display drop-down
36. 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.

37. On 1ETRNAP navigate to C:\Program Files (x86)\1E\NWMMC\Console in Windows Explorer
38. Open Console.Extensions.xml.template with Notepad
39. 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.

40. Save the file as Console.Extensions.xml (remove the .template extension) – the file with the sample console extension is now 'live'
41. 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
42. 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
43. 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.
44. 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

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