SharePoint farms are common in large and small businesses, and the ability to set up simple lists, document libraries, schedules and team sites make it a handy multipurpose tool.  SharePoint lists support the business objectives and are also employed to track the resources used to support the business itself.  All businesses use computers to perform their work, and these assets are often tracked for depreciation, warranty repairs, and replacement. How many pieces of equipment are out of warranty? What networking equipment is due for replacement?  When was the inventory list last updated? 

HOW TO SAVE A SHAREPOINT LIST TO EXCEL 

The built-in SharePoint views can be used to answer a few of these questions, but you can also use Excel with its database view and pivot-table feature to simplify the analysis of your current data. It is also often helpful to show when and who last updated a record by adding a few additional columns and then exporting the list to Excel.

  • From within your SharePoint site, select Site Contents in the list view on the left.
  • From the Site Contents list, choose the three periods (…) beside the name of your Inventory list, then choose Settings from the popup menu
  •  At the bottom of the List Settings, choose to create a new view for your list.  Select Standard View from the view type.
  •  Change the View Type to Personal View (this will just be for temporary use) and in the column name list, check the Modified and Modified By columns.  These will allow us to see who last updated the record and when it was last updated.
  • Choose the option under “List” tab to export data to Excel.
  • Accept the dialog prompt to open the data in Excel.
  • If you receive a security notice in Excel, click the Enable button to allow the data connector to import the SharePoint data.
  • A new spreadsheet is opened and a table is created from SharePoint list. 

Now that my list is in Excel as a table, you can use the column headings to sort and filter the data in a manner similar to SharePoint views, but in a much more interactive fashion.  The headings at the top of each column can drop down to change the sort order and show or hide items in the table.  For example, if you choose the Modified column and change the sort order to show Sort Newest to Oldest, you can quickly see when hardware assets were recently updated.

A more powerful tool to analyze this data within Excel is by using the Pivot Table feature.  This tool lets you combine both traditional (numeric or date) and graphical filters to see trends to summarize information for a more useful view of the data.  For example, we can take the table of computer assets and show how many devices have been replaced annually during the previous years.  This could be used to help set an expectation of hardware replacement needs for the next year.

HOW TO ANALYZE DATA USING A PIVOT TABLE 

  • In Excel, choose the Insert tab and then select Pivot Table.  The current table will be selected, and you should add the Pivot Table as a new worksheet to keep the document organized.
  • In the new worksheet, choose the columns that should appear in the Pivot Table.  The table shown below Client Name and Item Type are selected.
  • Below the column selection list is an area to define how the columns you selected will be used to display values in the Pivot Table. 
  • From the Analyze menu, select Insert Timeline.  This table uses the Warranty Expiration date column as the controlling data.  With this control, you can change the granularity setting in the upper-right corner from Days to Years – this will allow the summation of warranties that will expire for each client over a series of years which we can select.
  • The Timeline control has been adjusted by sliding the shading to cover the years between 2011 and 2019. The chart now reflects the trends for warranty expirations during the past 5 years and into the future based on currently recorded asset records.
According to SANS, one of the most respected sources for information security in the world, maintaining an inventory of both authorized and unauthorized assets on your network is the topmost important critical security control any organization can have in place.
— Bill Valaski

Another invaluable analysis of your business assets might be to look at the devices that are connecting to your network and compare it with the items in your asset list. According to SANS, one of the most respected sources for information security in the world, maintaining an inventory of both authorized and unauthorized assets on your network is the topmost important critical security control any organization can have in place.  You could use the NMAP tool to gather a list of devices connecting to your network (nmap -T4 -F) and compare it with your asset list to determine missing, misconfigured, or rogue devices on your network.

Having data is good, getting useful information out of it is better. Often times, the data about the data (metadata) is as handy as the original data itself to spot trends and provide insight into business operations.
— Bill Valaski

As mentioned earlier, the ability to analyze your current information in Excel and spot trends or look for missing information can be invaluable.  Having data is good, getting useful information out of it is better.  Often times, the data about the data (metadata) is as handy as the original data itself to spot trends and provide insight into business operations.

BILL VALASKI

IT Consultant | AfidenceIT