XML Data manipulation with PivotTables
After you download an Asset Tracking XML Data Dump from the N-sight RMM Dashboard, you can use PivotTables to manipulate and work on the data. For example, the XML Data dump includes hardware and software information but you may need to create a software only report.
PivotTables is one option to manipulate the data but you can choose other options, including a Microsoft Excel Power Query.
This section introduces how to use the Asset Tracking XML Data Dump, which contains both hardware and software information, to create a software only report.
The first stage is to retrieve the data from the Dashboard, from the Asset Tracking Dashboard, go to Reports > XML Data Dump and select All Clients or a specific Client from the drop-down. Save the file when prompted and open in a spreadsheet program, in this example Excel 2007.
The option to download the XML or SQL Data Dump is only available for contracted customers.
When the information loads, highlight a range of data to include. If you do not highlight data, the whole table is selected. Then go to Insert and from Tables select PivotTable,PivotTable.
In the Create PivotTable wizard, check the Select a table or range settings and Choose where you want the PivotTable to be placed, in a new or existing worksheet and click OK to apply.
In the following example, the full table was selected opening the PivotTable as a New Worksheet.
Each column of the Asset Tracking XML Data Dump contains a heading that is displayed under the PivotTable Field List in the PivotTable worksheet. You can edit this column heading to a more meaningful name, either by editing the column heading before or after the PivotTable is created.
To begin populating the PivotTable, select the required information from Choose fields to add to reports.
Right-click the field name for the area selection context menu Report Filter, Column Labels, Row Labels, Values - otherwise when simply selecting the field it will appear under Row Labels. When the field is not in the required area, you can drag it to the correct area.
When adding the required fields, it is useful to refer back to the source spreadsheet for details on the information each column contains.
After the PivotTable is populated, it can be edited via PivotTable Options, available by right-clicking in the table. The options include the ability to amend the Layout & Format, remove or add Totals & Filters, select the information to Display, determine what is included when Printing and how the Data for the table is stored. You can also edit each field column by right-clicking on the column heading.
The following software report example displays the chassis, device and discovered software for each machine with the columns headings renamed for clarity. New naming conventions: Chassis to Device Type, name2 to Device Name and name5 to Software.
When the report is formatted, including additional details such as logos, you can print it for presentation to the customer.
