Monat: Juli 2017

Azure Cyber Analytics Program for Power and Utilities Customers

The utilities industry is under continuous and persistent threat. The Ukraine attack was a wake-up call for many utilities who would not have considered something as improbable as a serial-to-Ethernet gateway vulnerability to be one of the key factors in allowing hackers to turn-off power to more than 230,000 Ukrainian residents. The E-ISAC’s detailed analysis of the attack shows how existing SCADA and communications processes were used to compromise systems. As we learn more about the CrashOverride Malware at the heart of this attack, the importance of proactive protection becomes evident. The WannaCry cryptoworm ransomware attack underscored again the importance of updating and patching systems (Microsoft has published guidance for WannaCry), and just days ago, U.S. Power Firms were the target of attacks which, while not fully analyzed yet, show signs of credential harvesting in order to compromise power facilities including the Wolf Creek nuclear facility in Kansas. If passive defense is no longer sufficient, how can customers actively protect themselves and their systems?

Commitment to the Industry

Microsoft is deeply aware of the importance of cybersecurity for companies supporting the electric grid and is committed to helping partners and customers secure their nations’ most critical of critical infrastructure. In furtherance of this commitment, we are announcing a cyber program: “Microsoft Azure Certified Elite Partner Program for Cyber Analytics in Power and Utilities”. Microsoft has invested deeply in tools, analytics, cyber intelligence, and services for our own Cloud, and we believe it is imperative we engage customers to put these capabilities to work for them as well. While we are beginning this program in the U.S., there are plans to quickly expand worldwide.

Microsoft is demonstrating a commitment to the industry by covering the initial costs for deploying and running the Operations Management Suite (OMS) for program participants. The program is designed to engage Azure Certified Elite System Integrators to perform the OMS Service integration for utility customers enrolled in the program. What this means to the utilities industry is customers can better track threat actors currently in their network, identify malicious software dialing outbound from their servers, and establish an alerting system to enable active network cyber defense. The program also includes a limited Azure subscription which can be used to support training and development, and for expediting implementation/deployment projects. In short, there is significant upside to this program.

Microsoft Azure Certified Elite Partner Program

The program uses the Microsoft Azure OMS Advanced Log Analytics Service to analyze customer logs uploaded to an Azure Storage Account. This includes the data acquisition of network cyber logs across the utility enterprise and ICS networks to an Azure repository. Global malicious site and threat actor intelligence is used to provide utility companies greater visibility into the current security state of their networks. The OMS alerting capability is also used to notify a utility if intrusion or new malware is detected, almost immediately.

OMS Data Collection

Operations Management Suite is a collection of management services that were designed in the cloud from the start. Rather than deploying and managing on-premises resources, OMS components are entirely hosted in Azure so configuration is minimal, and you may be up and running literally in a matter of minutes. Data collected by Log Analytics is stored in the OMS repository hosted in Azure.

Connected sources generate the data that gets collected into the OMS repository. There are many types of connected sources supported:

An agent installed on a Windows or Linux computer connected directly to OMS.
A System Center Operations Manager (SCOM) management group connected to Log Analytics. SCOM agents continue to communicate with management servers which forward events and performance data to Log Analytics. OMS can forward log data via SCOM Agents as well.
An Azure storage account that collects Azure Diagnostics data from a worker role, web role, or virtual machine in Azure.
Various Azure resources (full list here) pushing data as a connector, extension, or via Diagnostics depending on the resource.
O365 Data
Custom logs

Threat intelligence

Microsoft runs dozens of cloud services across dozens of regions throughout the world, creating a truly global scale which enables us to achieve a unique view of the threat landscape. The insights we derive, informed by trillions of signals from billions of sources, create an intelligent security graph that we use to inform how we protect all endpoints, better detect attacks and accelerate our response. Microsoft’s sophisticated tools help us know, for example, where attacks came from, meaning we can better and more quickly identify malicious IP addresses. Our goal is to enable our customers to benefit from this knowledge to help protect their resources.

Antimalware assessment

One of the most important tools to defend your systems is antimalware software. Building upon existing antimalware capabilities in OMS, the antimalware solution has been extended to enable nearly full coverage for Microsoft Antimalware engines, as well as to detect the protection status of antimalware that registers its existence using the Windows Security Center APIs.

If you are interested in participating in this program, please contact your Microsoft Account Executive, or Larry Cochrane (L.Cochrane@Microsoft.com), Azure Energy Principal Program Manager.
Quelle: Azure

Tableau and Azure SQL DB, a match made in the cloud

In recent years, Microsoft SQL and Tableau Engineering teams have been working closely together to provide a superior user experience with the two platforms. Today we are sharing some advice on how to optimize the connectivity between Azure SQL DB and Tableau.

Our teams previously teamed up for the SQL Server 2016 launch and for the Azure SQL Data Warehouse launch. Today, this partnership relies on the fact that SQL Server is Tableau’s most common data source, in combined cloud and on-premises usage as detailed in the recent Tableau Cloud Data Brief. 

Our engineering benchmarks, and several global customer engagements, lead us to have a closer look at optimal connectivity and how to leverage the specificities of both platforms.

Without further ado, here are the main learnings.

Out-of-the-box experience works well

We observed that most customers fared well by simply replicating their on-premises approach. Azure SQL DB uses the same drivers as SQL Server 2016, which inherently reduces complexity. With Tableau Desktop UI there is a single SQL Server connector for Azure SQL DB, Azure SQL Data Warehouse, or SQL Server 2016, running on premises or in a public cloud like Azure.

Tableau Live Querying provides the best performance

Network bandwidth permitting, the Live Query mode of Tableau allows the heavy lifting to occur in Azure SQL DB, while also providing more up to date information to Tableau users, as opposed to extract based connectivity. This implies doing some sizing and performance testing with different Azure SQL DB SKUs. In our experience, Azure SQL DB latency and throughput can meet the most stringent Tableau requirements.

For example, we advised a joint-customer to move from S0 (10 DTUs) to P1 Premium (125 DTUs), which instantly removed latency issues. The cost impact is commonly offset by an improved user experience and increased customer satisfaction.

Other Tableau best practices

Isolate date calculations: As much as possible, pre-compute information. Tableau will compute it once and the database may be able to use an index.
Use Boolean fields: Don’t use 0 and 1 as indicators for true and false, just use Boolean fields. They are generally faster.
Don’t change case: Don’t put UPPER or LOWER in comparisons when you know the case of the values. 
Use aliases: Where possible, label text using Tableau’s alias feature, rather than in a calculation. Aliases aren’t sent to the database so they tend to be faster.
Use formatting when possible: Don’t use string functions when you can just use formatting. Then use aliases to label the fields.
Replace IF / ELSEIF with CASE: It’s a good idea to do this as CASE statements are generally faster.

Using a query tuning methodology

We used the following methodology to analyse Tableau queries in order to identify and address bottlenecks:

Enable query store
Run the provided workloads
Monitor DTU consumption using dynamic management views to ensure that tier limits are not being reached
Check for index recommendations and usage
Prioritize statements based on highest execution time
Examine top queries and associated execution plans
Apply suggestions
… iterate

Key tools for Azure SQL DB Optimization

For Azure SQL Database customers in general, consider recommending using the following:

Azure SQL Database Query Performance Insight
SQL Database Advisor

Checking service level constraints

To determine if you are hitting DTU limits for a workload, take a look at the following query:

SELECT [end_time], [avg_cpu_percent], [avg_data_io_percent],
       [avg_log_write_percent], [avg_memory_usage_percent]
FROM [sys].[dm_db_resource_stats];

This returns one row for every 15 seconds for the last hour. We used this for testing the provided workloads to determine if we needed to bump up to the next tier. For a less granular view of this data, we used sys.resource_stats catalog view in the master database.

Monitoring index recommendations and usage

Periodically check missing index recommendations

For any Tableau customer, given the diverse workload characteristics, it is a good idea to periodically check missing index recommendations. We don’t recommend adding all recommendations arbitrarily, but we do like to periodically assess the cost/benefit of specific recommendations over time. 

SELECT [migs].[group_handle], [migs].[unique_compiles], [migs].[user_seeks],
            [migs].[user_scans], [migs].[last_user_seek], [migs].[last_user_scan],
            [migs].[avg_total_user_cost], [migs].[avg_user_impact],
            [migs].[system_seeks], [migs].[system_scans],
            [migs].[last_system_seek], [migs].[last_system_scan],
            [migs].[avg_total_system_cost], [migs].[avg_system_impact],
            [mig].[index_group_handle], [mig].[index_handle], [mid].[index_handle],
            [mid].[database_id], [mid].[object_id], [mid].[equality_columns],
            [mid].[inequality_columns], [mid].[included_columns],
            [mid].[statement]
FROM [sys].[dm_db_missing_index_group_stats] AS [migs]
INNER JOIN [sys].[dm_db_missing_index_groups] AS [mig]
ON ( [migs].[group_handle] = [mig].[index_group_handle] )
INNER JOIN [sys].[dm_db_missing_index_details] AS [mid]
ON ( [mig].[index_handle] = [mid].[index_handle] );

Validate index usage over time

Conversely, we recommend making sure that indexes are pulling their weight over the long term. Some indexes may not be useful over a long period of time, so we recommend checking index usage via the following applicable dynamic management views. 

SELECT OBJECT_NAME([s].[object_id]) AS [Table Name],
            [i].[name] AS [Index Name], [s].[user_seeks], [s].[user_scans],
            [s].[user_lookups], [s].[user_updates], [s].[last_user_seek],
            [s].[last_user_scan], [s].[last_user_lookup], [s].[last_user_update],
            [s].[system_seeks], [s].[system_scans], [s].[system_lookups],
            [s].[system_updates], [s].[last_system_seek], [s].[last_system_scan],
            [s].[last_system_lookup], [s].[last_system_update]
FROM [sys].[dm_db_index_usage_stats] AS [s] 
INNER JOIN [sys].[indexes] AS [i] 
ON [s].[object_id] = [i].[object_id]
AND [i].[index_id] = [s].[index_id]
INNER JOIN [sys].[objects] AS [o]
ON [i].[object_id] = [o].[object_id]
WHERE OBJECTPROPERTY([s].[object_id], 'IsUserTable') = 1
ORDER BY [s].[user_updates] DESC;

Best practices

Monitor over time and ensure you do not drop indexes without ensuring that all representative workloads have been run over the testing period.
Be cautious about dropping indexes that are used to define uniqueness. The indexes may not be used for traversal, but still may be necessary for estimation and enforcing purposes.
The best scenario for monitoring index usage is for indexes where you are uncertain if it will be helpful and used once created. You can add the index, run the workload, and then check sys.dm_db_index_usage_stats. You can check the plans too, but for larger workloads, checking the DMV is faster.

We hope this is useful to you and we’re curious to read your comments and feedback on how you use Tableau and Azure SQL DB. If you are new to this scenario, Tableau is available with a trial license key, and try the Azure free trial to unlock the benifits you can use against Azure SQL DB. Tableau Server is also available as a ready-to-spin image on the Azure Marketplace.

If you’re looking at more complex deployment scenarios and want to upgrade your Tableau and Azure skills, we’d recommend a look at our Tableau and Cloudera Quickstart Azure template.

You can also follow and connect with the Azure SQL DB team on Twitter.

Acknowledgments

This article is a collaboration between several people. Special thanks to Dan Cory (Tableau), Nicolas Caudron (Microsoft) and Gil Isaacs (Microsoft).

 

Quelle: Azure