How to Visualize Windows Firewall Traffic (Integrating nodeProtect with Power BI)

nodeProtect
Azure
Power BI
Functions
Database
April 21, 2020

The nodeProtect agent collects statistical and configuration data from each connected device. Each collection of data is stored and viewable in the nodeProtect portal.

In some situations, such as security, analysis, and research jobs, we might need to dig deeper in the collected data and gather insights in correlation with custom analysis jobs outside of nodeProtects current scope.

In these cases, it’s beneficial to extract and integrate the data with business analytic services, such as Power BI. With the smart tools and analytical capabilities provided by Power BI we can gather the insights we need and drive business decisions to achieve smart results.

Requirements

Lets dig into the details and see how we can set this up in our environment. Before we start, make sure that the following requirements are in place:

  • A nodeProtect account
  • One or more nodeProtect agents installed
  • An Azure subscription
  • Power BI Desktop

Gather intel and statistics from nodeProtect

First things first. Make sure that you have at least one agent installed in nodeProtect.

Next, run a Windows Firewall Statistics job and Analyze Windows Firewall Rules on each node that you want in your report.

Verify that the jobs are completed (a green checkbox is displayed under Status).

Prepare your resources in Azure

In Azure, we need the following resources:

  • Azure Sql Server Database - Storage location for analysis data
  • Azure Function - Transform data from nodeProtect to the database
  • Key Vault - Storing configurations used by the functional app

First, create a resource group to hold the resources. The example demonstrates how to create a resource group using PowerShell.

-- CODE language-markup --
New-AzResourceGroup -Name rg-nodeprotect-pbi-test -Location 'West Europe'

Create an Azure Sql Server Database

Next, Add a Azure SQL Server Database. The example below demonstrates how to create a Basic Azure Sql Server Database in Azure using PowerShell

-- CODE language-markup --
$resourcegroupname= 'rg-nodeprotect-pbi-test'
$serverName = 'sql-np-pbi-test'
$databaseName = 'sqldb-np-pbi-test'
$location = 'West Europe'
$credentials = Get-Credential

New-AzSqlServer -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-Location $location `
-SqlAdministratorCredentials $credentials

New-AzSqlDatabase -ResourceGroupName $resourcegroupname `
-ServerName $serverName `
-DatabaseName $databaseName `
-Edition Basic

Create an Azure Sql Server Database Table

The nodeProtect API returns data as JSON documents. In order to get the data into the database we need to transform the data and insert it into a set of tables.

Lets take a look at the table design. In this example we’ll keep it fairly simple and create a single table that contains node details and a couple of measurements based on collected data from statistical jobs.

The device details that we will focus on in this post includes:

  • NodeId - Unique device identifier
  • Name - Device friendly name
  • OperatingSystem - Device operating system
  • IPAddress - Device ip address
  • LastHeartbeat - Last known heartbeat
  • StatisticCount - Number of statistic jobs collected
  • RuleCount - Number of firewall rule jobs collected

This data is just a subset of the information collected by nodeProtect and we will dig into specific details later on. But for now, let’s stick to the basics.

The following query demonstrates how to create a table in a Azure Sql Server Database.

-- CODE language-markup --
SET ANSI_NULLSON
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Node](
          [NodeId] [uniqueidentifier] NOT NULL,
          [Name] [nvarchar](50) NOT NULL,
          [OperatingSystem] [nvarchar](100) NULL,
          [IPAddress] [nvarchar](50) NULL,
          [LastHeartbeat] [datetime] NULL,
          [StatisticCount] [int] NULL,
          [RuleCount] [int] NULL,
   CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED
   (
              [NodeId] ASC
   )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create a Data transformation Azure Function

The Azure Function app will be used to tranform and combine data from the nodeProtect API and store the data in the sql server database.

In Visual Studio, Start a new project and select the Azure Function template.

Type a name for your project and click on create. In this example we will call the project nodetransformer.

Set the functionality to: Azure Functions v3 (.NET Core), select Time Trigger, and click on create.

Before adding any code, make sure that you add the following packages to your project:

  • Microsoft.EntityFrameworkCore - Lightweight version of Entity Framework. used for Sql Server database integrations.
  • nodeProtect.Client.Sdk - nodeProtect Sdk library that enables extension development.

You also need the following configuration values:

  • APIKey - nodeProtect API key. Can be requested on-demand from the nodeProtect team.
  • APISecret - nodeProtect API secret. Can be requested on-demand from the nodeProtect team.
  • ConnectionString - Sql server database connection string. Can be found here:

The code example below demonstrates how to utilize the nodeProtect API integration to connect to nodeProtect and get details about:

  • Nodes
  • Firewall statistics
  • Firewall Rules

Next, the code demonstrates how use System.Data.SqlClient to add or update entries in the dbo.Node table.

-- CODE language-markup --
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.Azure.WebJobs;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using nodeProtect.Sdk;

namespace NodeTransformer
{
   public static class Transformer
   {
       [FunctionName("TransformData")]
       public static void Run([TimerTrigger("0 */5 * * * *")]TimerInfo myTimer, ILogger log)
       {
           log.LogInformation($"nodetransformerfunction executed at: {DateTime.Now}");

           // Getapplication settings
           string apiKey= Environment.GetEnvironmentVariable("APIKey");
           string apiSecret = Environment.GetEnvironmentVariable("APISecret");

           // GetNodes using nodeProtect.Sdk
           Client npClient = new Client();
           npClient.Connect(apiKey,apiSecret);
           List<Node> nodes =npClient.GetNode();
           List<NodeItem>nodeItems = newList<NodeItem>();

           foreach (Node node in nodes)
           {
               // GetNodeData
               List<NodeData> statistics= npClient.GetFirewallStatistics(node.Id);
               List<NodeData>rules = npClient.GetFirewallRules(node.Id);

               //Convert NodeData to match custom Database
               NodeItem nodeItem =ConvertToNodeItem(node, statistics.Count, rules.Count);
               nodeItems.Add(nodeItem);
           }

           // AddNodeItems to custom Database
           AddNodeItem(nodeItems);
       }

       public static NodeItem ConvertToNodeItem(Nodenode, int statisticCount, int ruleCount)
       {
           NodeItem nodeItem = new NodeItem
           {
               NodeId = new Guid(node.Id),
               IPAddress =node.IPAddress,
               LastHeartbeat =node.LastHeartbeat,
               Name = node.Name,
               OperatingSystem =node.OSName,
               RuleCount = ruleCount,
               StatisticCount =statisticCount
           };

           return nodeItem;
       }

       public static void AddNodeItem(List<NodeItem>nodeItems)
       {
           using (var context = new DatabaseSession())
           {
               var existingNodes =context.Nodes;
               foreach (NodeItemnodeItem in nodeItems)
               {
                   if (existingNodes.Contains(nodeItem))
                   {
                      context.Nodes.Update(nodeItem);
                   }
                   else
                   {
                      context.Nodes.Add(nodeItem);
                   }
               }
               context.SaveChanges();
           }
       }

       public class DatabaseSession: DbContext
       {
           public DbSet<NodeItem> Nodes {get; set; }

           protected override void OnConfiguring(DbContextOptionsBuilderoptions)
           =>options.UseSqlServer(Environment.GetEnvironmentVariable("ConnectionString"));
       }

       [Table("Node")]
       public class NodeItem
       {
           [Key]
           public Guid NodeId { get; set; }
           public string Name { get; set;}
           public string OperatingSystem { get; set; }
           public string IPAddress { get; set; }
           public DateTime LastHeartbeat { get;set; }
           public int StatisticCount { get; set; }
           public int RuleCount { get; set; }
       }
   }
}

Add the code to your .cs file in your azure function project and publish the code by clicking on Build/Publish in Visual Studio. Either publish the code to a new function or use an existing if created beforehand.

Add a System Assigned Managed Identity

Next, we need to add the configuration values in Azure. Start by clicking on your published azure function in Azure and click on Platform Features. Click on Identity.

Next, set the status to On on the system assigned managed identity.

Configure Azure KeyVault

To store the configuration values in a secure way we need to create a key vault and add the secrets to it. The example below demonstrates the secrets added to the key vault.

After the secrets are added, we need to give the Azure function access to read the secrets. Click on Access Policies/Add Access Policy.

Select the Get and List Secret Permissions

Click on Select Principal and add the Azure functions system assigned managed identity.

Click on Add and save your changes.

Configure Azure Function

Next, head back to the Azure function and click on Configuration. Click on New application setting and add the following settings:

  • APIKey
  • APISecret
  • ConnectionString

The values point to the key vaults secret identifier. We can find the uri on each secret

The format for the value should be:

-- CODE language-markup --
@Microsoft.KeyVault(SecretUri=https://yoursecretidentifiergoeshere)

Finally, start the Azure function

And verify that the nodeProtect data is populated in the custom database.

Connect the dots in Power BI

The last part in this post will demonstrate how we can use Power BI to display and work with the data gathered by nodeProtect.

Open up Power BI Desktop and click on the SQL Server icon. Add:

  • Server Name
  • Database
  • Direct Query

And click on OK.

Click on Database and fill in:

  • User Name
  • Password

Click on Connect

Select the Node table and lick on Load.

You should now see the table displayed in the Model view.

Click on the Report view and start designing your report.

The example below uses:

  • Table - Displays Name, LastHeartbeat, and IPAddress
  • Card - Displays RuleCount (number of rule collections collected)
  • Card - Displays StatisticCount (number of statistic jobs performed)
  • Stacked bar chart - Displays node by Operating System

Conclusion

And that concludes our integration between nodeProtect and Power BI. In upcoming posts we’ll dig deeper into the capabilities of nodeProtect and Power BI and see how we can combine the products to produce insightful business intelligence reports.

Over and out

//nodeProtect Team

Appendix

Take full control of your systems
need a secure and modern way of managing Windows Firewall?
Discover how your systems are interacting with each other and how you can minimize risk against hackers and ransomware with the help of nodeProtect.
Get Started - It's free
Learn & Get Help
DocsVideo TutorialsBlog
contact@nodeprotect.com
© 2020 addlevel - a part of TRUESEC. All rights reserved.
COOKIE DECLARATION AND SETTINGS