Fun with Microsoft Power BI - Part III - Advanced Ingest and Viewing

 By Tony Lee

Welcome to part III in this series of going from zero to hero using Power BI to ingest, process, and make amazing reports. If you have read some of our other articles you can probably tell by now that we enjoy making data actionable. Honestly, it doesn't matter what type of data or even where the data ends up. As long as we can make informed decisions using the data -- we love it. Following in this theme we are going to make BlackBerry (formerly known as Cylance) Protect Threat Data Report (TDR) CSVs actionable using Power BI and Power BI Desktop. You can use any data source to follow along in this series, but our example BlackBerry Protect report is shown below which we will happily share the Power BI file at the end of the series for you to load and analyze your own data, so stay tuned for that!

Figure 1:  Our Power BI report using BlackBerry Protect TDR data

In the first article, we covered:

• Getting Started
• Data Ingest
• Adjusting Fields
• Visualizations
• Saving Your Work

In the second article, we covered:

• Tabs
• More Visualizations
• Text box
• Slicer
• Table
• Pie Charts
• Treemap
• Using Reports and Dashboards
• Uploading Reports to Power BI Service (Online)

In this article, we will cover:

• Question and Answer (Q&A) Feature
• Power BI for Mobile
• Changing the Data Source
• Scheduling Data Refresh

Question and Answer (Q&A) Feature

Ever wish you could use natural language to ask questions and get answers about your data? In Power BI, you can do just that and even save the resulting visualizations into your report (if you have the necessary permissions). It may take a bit of trial and error, but the results can be both interesting and useful. In Power BI Desktop, this is activated by going to Insert menu > Q&A. In the Power BI Service online interface, click on the report you want and then the "Ask a question" button.

Figure 2: Question and Answer feature of Power BI

Power BI is nice enough to suggest a few questions to ask, however we will list some of our questions below as well.

Example searches include:

• most recent threats data report
• count of threats data reports by create time
• show classification by sha 256, file name as matrix

Figure 3:  Example Q&A

For more Q & A information, check out:  https://docs.microsoft.com/en-us/power-bi/consumer/end-user-q-and-a

Power BI for Mobile

One of the best features of Power BI is the platform flexibility. Once you build a report and have it available in your workspace, you can view it on nearly any mobile device: Apple, Android, and Microsoft.

Source: https://powerbi.microsoft.com/en-us/mobile/

We have an Android phone that we used to view the report and try out the filters. The app is available in the play store: https://play.google.com/store/apps/details?id=com.microsoft.powerbim&hl=en_US

 

Figure 4:  Screenshot of our report displayed on a Galaxy Note 8

Changing the Data Source

In our first article, we wanted to keep things simple by uploading a static CSV file. This is great for building the initial report, but it becomes cumbersome to update the data. Fortunately for us, this CSV data came from a simple to use web API which Power BI can easily handle. In fact, Power BI supports a ton of data ingest options such as files, databases, cloud services (over 60 of them!), web APIs, and more. Now let's change our data source from static CSV file to web API, but first let's check the timestamp on the imported file. This can be achieved by hovering over the ThreatsDataReport field.

Figure 5:  Determining staleness and freshness of data

There is an option to refresh the data, but because the original data source is a static CSV, we would need to manually download it again. To change the data source, we need to Edit the query.

Figure 6:  Edit the query to change the source from static file to web query

This opens Power Query Editor. Next we click, Data source settings > Change Source... > Advanced > Then we use the file path parts to first enter the URL and then enter the token on the second line. This file will open the exact same as the static file.

Figure 7:  Changing the data source to using web API

Now that the source is changed to use the web API, we can refresh the data with a click of the button in Power BI using "Refresh data". After checking the timestamp on the new data, the update was successful!

Figure 8:  Refreshing data via the web API

Scheduling Data Refresh

Taking reporting one step beyond a manual refresh, we can also schedule a refresh of the data. In Power BI Service, expand the workspaces to see the Datasets. Click on the three dots next to the Web API data feed and select "Schedule refresh".

Figure 9:  Scheduling a refresh

Under the Schedule refresh menu, turn it on, enter the frequency and even the time to refresh. Another nice touch in this feature is email notification of failure.

Figure 10:  Settings for scheduling a daily data refresh

Conclusion

In this article, we looked at some interesting and more advanced features in Power BI to include the natural language Q&A feature, viewing reports on the Power BI mobile app, converting our static CSV file to update via web API, and finally scheduling the web API dataset to automatically update every day. Power BI is full of features and hopefully you learned something new today. Please feel free to leave feedback and your favorite Power BI features in the comments section below.

Syslog Over TLS Data Feed Splitting via syslog-ng

By Tony Lee

There are times where you may only be able to send data to a single syslog receiver, but all is not lost. Fortunately, many syslog receivers can handle splitting the feed. In this article we will use syslog-ng to show the steps needed to split a syslog feed (possibly for the purpose of taking a single feed and sending it to two SIEMs).  As a bonus, we will give you the steps to also receive and split a TLS encrypted syslog feed.

Figure 1:  Unencrypted syslog feed splitting

Basic Requirements

Since we are not handling much volume in this example, we just need minimum requirements:

Our setup

• Amazon EC2 instance
• Ubuntu Server 18.04 LTS (Red Hat is also possible, but requires different commands)
• 1 vCPU, 1GB RAM, 10GB HD

Basic Setup Steps

The syslog-ng package was not available out of the box for us so we had to run:

sudo apt update

Install and create a basic configuration file

sudo su -

apt install syslog-ng

mv syslog-ng.conf syslog-ng.bak

vim syslog-ng.conf

Note:  We used the same version found in the “Config version” field after running “syslog-ng --version”

@version: 3.13 source s_input { tcp(ip(0.0.0.0) port(10514)); }; destination d_siem{ udp("10.0.0.1" port(7514) template("$MSG") ); }; destination d_siem2{ tcp("10.0.0.2" port(7515) template("$MSG") ); }; log{ source(s_input); destination(d_siem); destination(d_siem2); };

Configuration explanation:

• template("$MSG") ensures that syslog-ng does not add another header to the message
• Single input called s_input listening on all interfaces over TCP port 10514
• Two destinations

1) d_siem sending to 10.0.0.1 on UDP 514
2) d_siem2 sending to 10.0.0.2 on TCP 515

Validate the configuration file

Use the following command to validate and troubleshoot syslog-ng configuration issues:

syslog-ng -s -f syslog-ng.conf

Start/restart the Service

Use the following command to check and restart the syslog-ng service:

service syslog-ng status

service syslog-ng restart

Validate the port is open

Use the following command to validate listening port is open:

netstat -an | grep :10514

tcp        0      0 0.0.0.0:10514           0.0.0.0:*               LISTEN

Test the Splitting

Since we are splitting TCP syslog in our example, we can use a web browser to test it:

http://<Your_syslog-ng_server>:10514

Figure 2:  Showing port details

Seeing the results in on the final server on UDP 7514 and TCP 7514

Figure 3:  Confirmation that the UDP 7514 split is working

Figure 4:  Confirmation that the TCP 7515 split is working

Enabling Syslog over TLS on the Input

We mentioned that there would be a bonus, now let’s enable TLS.

Note: This should be a requirement if your data is traversing a public network such as the Internet.

Creating private Certificate Authority (CA)

If you don’t have your own CA or choose not to use a public CA, you will need to create a CA on your syslog-ng server to sign certificates

sudo su –

cd /root

mkdir CA

cd CA

mkdir certs crl newcerts private

echo "01" > serial

cp /dev/null index.txt

cp /etc/ssl/openssl.cnf .

vi openssl.cnf

Change from:

dir = ./demoCA

to:

dir = .

:wq to save and exit vi

Generate the CA certificate

openssl req -new -x509 -keyout private/cakey.pem -out cacert.pem -days 365 -config openssl.cnf

Creating Server Certificate Request

Whether you use the local CA or an external/public CA, you will need to generate a server certificate request.

openssl req -nodes -new -x509 -keyout serverkey.pem -out serverreq.pem -days 365 -config openssl.cnf

openssl x509 -x509toreq -in serverreq.pem -signkey serverkey.pem -out tmp.pem

Common name should be the IP address or FQDN of your server. Leave email field blank.

Signing Server Certificate with CA

Next you can either get the serverreq.pem signed by your external CA, or sign it locally with the next step:

openssl ca -config openssl.cnf -policy policy_anything -out servercert.pem -infiles tmp.pem

rm tmp.pem

Either way you should end up with servercert.pem and serverkey.pem files, which will be used as

the server certificate and key for TLS encryption.

Copying Certificates to Syslog Directory

mkdir /etc/syslog-ng/cert.d

mkdir /etc/syslog-ng/key.d

mkdir /etc/syslog-ng/ca.d (if using local Certificate Authority)

cp cacert.pem /etc/syslog-ng/ca.d/ (if using local Certificate Authority)

cp servercert.pem /etc/syslog-ng/cert.d/

cp serverkey.pem /etc/syslog-ng/key.d/

cd /etc/syslog-ng/cert.d/

chown root servercert.pem

chmod 600 servercert.pem

cd /etc/syslog-ng/key.d/

chown root serverkey.pem

chmod 600 serverkey.pem

cd /etc/syslog-ng/ca.d/ (if using local Certificate Authority)

chown root cacert.pem (if using local Certificate Authority)

chmod 600 cacert.pem (if using local Certificate Authority)

Modify syslog-ng configuration file to use TLS

vim syslog-ng.conf

@version: 3.13 source s_input { tcp(ip(0.0.0.0) port(10514) tls(key_file("/etc/syslog-ng/key.d/serverkey.pem") cert_file("/etc/syslog-ng/cert.d/servercert.pem") peer_verify(optional-untrusted)) ); }; destination d_siem{ udp("10.0.0.1" port(7514) template("$MSG") ); }; destination d_siem2{ tcp("10.0.0.2" port(7515) template("$MSG") ); }; log{ source(s_input); destination(d_siem); destination(d_siem2); };

Validate the configuration file

Use the following command to validate and troubleshoot syslog-ng configuration issues:

syslog-ng -s -f syslog-ng.conf

Start/restart the Service

Use the following command to check and restart the syslog-ng service:

service syslog-ng status

service syslog-ng restart

Test the Splitting

Since we are splitting TCP over TLS syslog in our example, we can use a web browser to test it:

https://<Your_syslog-ng_server>:10514

Figure 5:  TLS enabled on the Input

Enabling Syslog over TLS on the Input & Output

Let’s go for the double bonus now and enable TLS on the Input and TCP Output

Note: This should be a requirement if your data is traversing multiple public networks such as the Internet.

Modify syslog-ng configuration file to use TLS

vim syslog-ng.conf

@version: 3.13 source s_input { tcp(ip(0.0.0.0) port(10514) tls(key_file("/etc/syslog-ng/key.d/serverkey.pem") cert_file("/etc/syslog-ng/cert.d/servercert.pem") peer_verify(optional-untrusted)) ); }; destination d_siem{ udp("10.0.0.1" port(7514) template("$MSG") ); }; destination d_siem2{ tcp("10.0.0.2" port(7515) tls(key_file("/etc/syslog-ng/key.d/serverkey.pem") cert_file("/etc/syslog-ng/cert.d/servercert.pem") peer_verify(optional-untrusted)) template("$MSG") ); }; log{ source(s_input); destination(d_siem); destination(d_siem2); };

Validate the configuration file

Use the following command to validate and troubleshoot syslog-ng configuration issues:

syslog-ng -s -f syslog-ng.conf

Start/restart the Service

Use the following command to check and restart the syslog-ng service:

service syslog-ng status

service syslog-ng restart

Test the Splitting

Since we are splitting TCP over TLS syslog in our example, we can use a web browser to test it:

https://<Your_syslog-ng_server>:10514

Figure 6:  TLS enabled on input and TCP output

Note:  We cannot TLS encrypt the UDP data stream

Set Service to Autostart on Boot

If all is well, now we can set the service to autostart at boot – just in case we experience a reboot.

sudo update-rc.d syslog-ng defaults

Added Security

Hopefully the source and destination IP addresses are static and known. This allows us to further protect the syslog ports by adding one to one firewall rules. This can be performed at both the network and host layer.

Caching Capabilities

One other option is to add some caching capabilities on this server by adding another destination of a local file. With enough disk space, it could provide some time to collect logs from the syslog-ng server before they rolled in the case of a network issue between the syslog-ng server and the downstream host.

Conclusion

In this article we showed how we can set up an intermediary syslog-ng server to split a single feed into multiple feeds. As a bonus, we also showed how we could secure the syslog traffic by enabling TLS to wrap the data with encryption. We hope this article helps get you up and running quicker when you need to split and secure that next feed.

Thanks

Zack Link for the original write up using Red Hat Linux. Syslog-ng team for the great software and documentation (https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.16/mutual-authentication-using-tls#TOPIC-956369).