Tuesday, May 1, 2012

Azure Storage Analytics Viewer

Tool usage:


Azure storage analytics is a very useful feature. It can help to isolate and debug Storage related issue. However, the metrics and log is not quite visible/readable for users.

Here I wrote a storage analytics viewer, it can make the analytics data eaiser to fetch and view. Features listed below:
  • Retrive the log data based on given time range and save to Excel format for further mining.
  • Show the metrics data in chart, so that it can be more readable
  • Some simple filter option.
The source code can be downloaded from Github
https://github.com/mogliang/Azure-Storage-Analytics-Viewer/downloads

Binary download link
https://github.com/mogliang/Azure-Storage-Analytics-Viewer/releases


Azure storage analytics viewers screenshot


User guideline:

Enable Azure storage analytics

By default azure storage analytics is disabled. User need enable azure storage analytics from Azure portal.


Check Azure storage Metrics and logs

1.      Input storage account name and key
2.      Select time range. By default, it select one day before now.
3.      Select the storage type which you want to view.
4.      Click button to metrics data
5.      After loading metrics, the chart would show the data. Right click chart to add/remove Series

6.      Click “Download Metrics” to save metrics data to local csv file.
7.      Click the datapoint on chart, all metrics properties will show on right side panel.

8.      Click “Download Log” to download transaction log and save to local csv file

Sunday, December 11, 2011

Use SQL to convert UTC time to local time


Introduction

We have a requirement that need implement a SQL function which can convert UTC time to any timezone's localtime. Not just current time, but need also support historical date. I find Chris's solution and make some code changes to meet our need.

Use

Code can be downloaded from here

To setup and test the timezone conversion function:
  1. Download and unzip sql_timezone_conversion_function.zip file.
  2. open SSMS(Sql server management studio), login to Sql server, open a query window to database [A].
  3. Run queries in “setup sql.sql” to create function and related table in target database [A].
  4. Open .NET project in SQLServerTimeZoneUtility.zip file, build and run, input sql connection string which connects database [A], it will export Windows system timezone rules to SqlAzure tables.
  5. To make a simple test, you can run “test sql.sql” in SSMS query window.
In future, to update timezone rules, just run SQLServerTimeZoneUtility tool again.

Design

Each Timezone may have 0..n daylight rules, each rule covers a time range. My design is having two tables, one store timezone base offset, the other store daylight rules

When input a utc date, the function would query tbTimeZoneRules to determine which rule fit the input date. Conversion function's sequence is as below

Since the timezone rules may change in future, we need also create an application which can export Windows system's timezone rules and update the rules in tbTimeZoneRules. Code is as below:

private static void UpdateTimeZoneRules(string connstr)
        {
            try
            {
                using (SqlConnection sqlconn = new SqlConnection(connstr))
                {
                    sqlconn.Open();

                    // clear table
                    SqlCommand sqlcmd0 = new SqlCommand(
                        "delete from tbTimeZonerules;delete from tbTimeZoneinfo",
                        sqlconn);
                    sqlcmd0.ExecuteNonQuery();

                    // read windows os timezone infos
                    ReadOnlyCollection timeZones = TimeZoneInfo.GetSystemTimeZones();
                    string[] monthNames = CultureInfo.CurrentCulture.DateTimeFormat.MonthNames;

                    // Get each time zone
                    foreach (TimeZoneInfo timeZone in timeZones)
                    {
                        string tzname = timeZone.StandardName;
                        double bias = timeZone.BaseUtcOffset.TotalMinutes;

                        // insert tbTimeZoneInfo table
                        SqlCommand sqlcmd = new SqlCommand(
                            string.Format("insert into tbTimeZoneInfo(Display,Bias) values('{0}',{1});select @@IDENTITY;", tzname, bias),
                            sqlconn);
                        decimal id = (decimal)sqlcmd.ExecuteScalar();

                        // insert TbTimeZoneRules table
                        TimeZoneInfo.AdjustmentRule[] adjustments = timeZone.GetAdjustmentRules();
                        foreach (var rule in adjustments)
                        {
                            DateTime datestart = rule.DateStart;
                            if (datestart < new DateTime(1753, 1, 2))
                                datestart = new DateTime(1753, 1, 2);

                            DateTime dateend = rule.DateEnd.AddSeconds(-1).AddDays(1);

                            SqlCommand sqlcmd2 = new SqlCommand(
                                "insert into tbTimeZoneRules(rulestart,ruleend,TimeZoneID,StdBias,DltBias,StdMonth,StdDayofWeek,StdWeek,StdHour,DltMonth,DltDayOfWeek,DltWeek,Dlthour,stdMinute,dltminute) values(@rulestart,@ruleend,@tzid,@stdbias,@dltbias,@stdmonth,@stddw,@stdw,@stdh,@dltmonth,@dltdw,@dltw,@dlth,@stdm,@dltm)",
                                sqlconn);
                            sqlcmd2.Parameters.Add("@rulestart", SqlDbType.DateTime).Value = datestart;
                            sqlcmd2.Parameters.Add("@ruleend", SqlDbType.DateTime).Value = dateend;
                            sqlcmd2.Parameters.Add("@tzid", SqlDbType.Int).Value = id;
                            sqlcmd2.Parameters.Add("@stdbias", SqlDbType.SmallInt).Value = 0;
                            sqlcmd2.Parameters.Add("@dltbias", SqlDbType.SmallInt).Value = rule.DaylightDelta.TotalMinutes;
                            sqlcmd2.Parameters.Add("@stdmonth", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.Month;
                            sqlcmd2.Parameters.Add("@stddw", SqlDbType.SmallInt).Value = (int)rule.DaylightTransitionEnd.DayOfWeek;
                            sqlcmd2.Parameters.Add("@stdw", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.Week;
                            sqlcmd2.Parameters.Add("@stdh", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.TimeOfDay.Hour;
                            sqlcmd2.Parameters.Add("@stdm", SqlDbType.SmallInt).Value = rule.DaylightTransitionEnd.TimeOfDay.Minute;
                            sqlcmd2.Parameters.Add("@dltmonth", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.Month;
                            sqlcmd2.Parameters.Add("@dltdw", SqlDbType.SmallInt).Value = (int)rule.DaylightTransitionStart.DayOfWeek;
                            sqlcmd2.Parameters.Add("@dltw", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.Week;
                            sqlcmd2.Parameters.Add("@dlth", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.TimeOfDay.Hour;
                            sqlcmd2.Parameters.Add("@dltm", SqlDbType.SmallInt).Value = rule.DaylightTransitionStart.TimeOfDay.Minute;
                            sqlcmd2.ExecuteNonQuery();
                        }
                        Console.WriteLine("{0} imported.", timeZone.DisplayName);
                    }
                }
                Console.WriteLine("Timezone rules update succeed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Timezone rules update failed.\n" + ex.Message);
            }
        }

Friday, August 13, 2010

How to consume WCF service with tcpTransport in Silverlight4

Last month, I wrote a new Silverlight4 sample for All-in-One Code Framework project. The sample called CSSL4WCFNetTcp, which demonstared how to utilize the Silverlight4 new supported netTcpTransport to create Silverlight client and consume WCF service.
For convenience, I post the sample's readme file here, someone who have interest on this sample could download 1Code latest release.

Creation
To demonstrate silverlight accessing WCF, we may need a WCF service and a Silverlight WCF client. Here we separate the creation progress into three tasks:
  1. Creating Duplex WCF service with netTcpBinding
  2. Creating Silverlight WCF client
  3. Deploying cross domain policy file
Creating Duplex WCF service with netTcpBinding
1. Create a new console project "NetTcpWCFService", add a new WCF Service to project, named WeatherService.
2. Open IWeatherService.cs, define the service contract like this:
namespace NetTcpWCFService
{
    [ServiceContract(CallbackContract=typeof(IWeatherServiceCallback))]
    public interface IWeatherService
    {
        [OperationContract(IsOneWay = true)]
        void Subscribe();
            [OperationContract(IsOneWay = true)]
        void UnSubscribe();
    }
    public interface IWeatherServiceCallback
    {
        [OperationContract(IsOneWay=true)]
        void WeatherReport(string report);
    }
}
3. Open WeatherService.cs. We use static event approach to implement subscription service.
First, prefix the ServiceBehavior to the existing WeatherService class, set InstanceContext mode to PerSession. Then, replace the default contents of the WeatherService class with the following:
[ServiceBehavior(InstanceContextMode=InstanceContextMode.PerSession)]
    public class WeatherService : IWeatherService
    {
        static event EventHandler WeatherReporting;
        IWeatherServiceCallback _callback;
        public void Subscribe()
        {
            _callback = OperationContext.Current.GetCallbackChannel();
            WeatherReporting += new EventHandler(WeatherService_WeatherReporting);
        }
        public void UnSubscribe()
        {
            WeatherReporting -= new EventHandler(WeatherService_WeatherReporting);
        }
        void WeatherService_WeatherReporting(object sender, WeatherEventArgs e)
        {
            // Remember check the callback channel's status before using it.
            if (((ICommunicationObject)_callback).State == CommunicationState.Opened)
                _callback.WeatherReport(e.WeatherReport);
            else
                UnSubscribe();
        }
    }
 
    class WeatherEventArgs:EventArgs
    {
        public string WeatherReport{set;get;}
    }

4. Create a separate thread to generate fake weather report periodically.
static WeatherService()
        {
            ThreadPool.QueueUserWorkItem(
                new WaitCallback(delegate
                {
                    string[] weatherArray = { "Sunny", "Windy", "Snow", "Rainy" };
                    Random rand = new Random();
 
                    while (true)
                    {
                        Thread.Sleep(1000);
                        if (WeatherReporting != null)
                            WeatherReporting(
                                null,
                                new WeatherEventArgs
                                {
                                    WeatherReport = weatherArray[rand.Next(weatherArray.Length)]
                                });
                    }
                }));
        }

5. Configure the app.config file to add netTcpbinding endpoint to WCF service.

Note that, only a few ports are allowed to be accessed by Silverlight, that is 4502-4534, and we need client access policy file to permit Silverlight access, please refer to Deploying cross domain policy file
6. Start the ServiceHost in Main method
static void Main(string[] args)
        {
            using (var host = new ServiceHost(typeof(WeatherService)))
            {
                host.Open();
                Console.WriteLine("Service is running...");
                Console.WriteLine("Service address: "+host.BaseAddresses[0]);
                Console.Read();
            }
        }

Creating Silverlight WCF client
Now, create Silverlight application to consume the WCF. We need one button to subscribe/unsubscribe servicie, and one listbox to display the weather report from service.
1. Create a new Silverlight project.
2. Open MainPage.xaml, Add Button, TextBlock and ListBox to MainPage and adjust layout.

3. Add Service Reference to our “weatherService” WCF service. To do this: First, set NetTcpWCFService project as startup project, press Ctrl+F5 to start the service. Then, right click Silverlight project, select “Add Service Reference”, in dialog, input the weather service address, and press OK. After done this, VS would generate wcf proxy code in Silverlight project.
4. Open MainPage.cs file, Initialize the WCF proxy in Loaded event
public partial class MainPage : UserControl,IWeatherServiceCallback
    {
        public MainPage()
        {
            InitializeComponent();
            Loaded += new RoutedEventHandler(MainPage_Loaded);
        }
 
        bool _subscribed;
        WeatherServiceClient _client;
        void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            _client = new WeatherServiceClient(
                new System.ServiceModel.InstanceContext(this));
            _client.SubscribeCompleted += _client_SubscribeCompleted;
            _client.UnSubscribeCompleted += _client_UnSubscribeCompleted;
        }
 
        void _client_UnSubscribeCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                _subscribed = false;
                btnSubscribe.Content = "Subscribe weather report";
                tbInfo.Text = "";
            }else
                tbInfo.Text = "Unable to connect to service.";
            btnSubscribe.IsEnabled = true;
        }
 
        void _client_SubscribeCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                _subscribed = true;
                btnSubscribe.Content = "UnSubscribe weather report";
                tbInfo.Text = "";
            }
            else
                tbInfo.Text="Unable to connect to service.";
            btnSubscribe.IsEnabled = true;
        }
 
        // Display report when callback channel get called.
        public void WeatherReport(string report)
        {
            lbWeather.Items.Insert(
                0,
                new ListBoxItem
                {
                    Content = string.Format("{0} {1}",DateTime.Now, report)
                });
        }
    }
5. Add event handler to handle button click event
private void Button_Click(object sender, RoutedEventArgs e)
        {
            if (!_subscribed)
            {
                _client.SubscribeAsync();
            }
            else
            {
                _client.UnSubscribeAsync();
            }
            btnSubscribe.IsEnabled = false;
        }
Now, the Silverlight WCF Client is completed. We have one last task to permit the Silverlight access.

Deploying cross domain policy file
1. Create a xml file, named "clientaccesspolicy.xml", set content as below
This file grant permissions to allow Silverlight clients from any domian to access server's 4502-4506 port.

2. Find out the server web site's root physical path (by default, C:\inetpub\wwwroot), place the policy file in that folder. To varify the deployment, browse http://localhost/clientaccesspolicy.xml, check if you could see the policy xml content.

Demo
To test the project
1. Open CSSL4WCFNetTcp solution, build.
2. Start the duplex WCF. Run NetTcpWCFService.exe, it's under CSSL4WCFNetTcp\NetTcpWCFService\bin\debug folder.
3. Right click "CSSL4WCFNetTcpTestPage.aspx" in VS, Select "View in Browser".
4. When Silverlight application loaded, click "subscribe" button, if all code and configuration is correct, you may find the listbox displaying a new record in each second.

Thursday, July 8, 2010

<standardEndpoints> section cause webrole stuck at Busy state

<standardEndpoints> is the new added wcf configuration feature in .net framework 4. However, someone who utilize this config section in his/her webrole's config file may found that the application deploying on cloud would stuck at "Busy" state.

I consulted my colleague, and she me told that this is because the current cloud VM's (Windowss Azure Guest OS 1.4 (release 201005-01)) machine.config is missing the following section.

Adding this section at the beginning of your web.config file should fix this issue.

Tuesday, June 22, 2010

How to secure WCF on cloud with HTTPS

Although windows azure provide very friendly interface which allow existing .net application be easily run on cloud, however, there are still some changes need be done before we megiate normal .net app on to cloud app.

In this blog, I'll show how to create a WCF service on webrole with transport security armed.

First, let's create a Windows azure project with 1 WCFWebRole. In WCFWebRole project, there is an existing WCF called "Service1.svc". Since, we are focusing on how to secure service, we'll just use this wcf service without change any code.

Second, change the web.config file to configure wcf security.

With this configuration, we will enable TransportWithMessageCredential security, and use custom usernamePassword authentication. And here is my custom validator:
namespace WCFServiceWebRole1
{
    public class MyCustomUsernamePasswordValidator : UserNamePasswordValidator
    {
        public override void Validate(string userName, string password)
        {
            if (userName != password)
                throw new Exception("username/password not valid.");
        }
    }
}

Note: For why I don't choose Transport security, that's because cloud doesn't support Windows authentication, due to the IIS(not sure IIS web core) limitation, 'Basic' is not choice either. However, if we don't need client authentication, then we could simply set security to transport and set clientCredential to None.

Until now, we haven't done all necessary steps to make a transport secured WCF run on local IIS. However, there are additional work need be done to enable the HTTPS protocol on cloud. Let's continue.

First, create a self-signed certificate(just for test usage). the subject name should be equal to your windows azure service's domian name.(eg. [servicename].cloudapp.net). To create certificate, open Command prompt with administrator priviledge, run the commands below.
Makecert -r -pe -n "CN=[your_ns].cloudapp.net" -b 05/10/2010 -e 12/22/2012 -eku
 1.3.6.1.5.5.7.3.1 -ss my -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

When certificate created, it would be added to your localmachine's personal store directly. You need use MMC tool to export the certificate with private key to local file *.pfx

Third, return to our cloud project, open WCFServiceWebRole's properties panel, click "Certificates" tab, Add certificate, in Thumbprint column choose our new added certificate.

Click "Endpoints" tab, check "HTTPS" endpoints, select certifice in "SSL certificate name" combobox to bind certificate to SSL port.

Next, we need upload certificate to windows azure service. Login the Windows Azure Portal, open the target service, in management page, find "Certificates" section, click "Manage"
In certificate management page, select the pfx file, input password, click "Upload" button. If succeed, the certificate would be listed here.

Ok, last step is deploy our windows azure application on cloud. Here is the result

Note that, the browser address bar is in red color, that's because the self-signed certificate is not from a trusted certificate root. To solve this issue, open certmgr tool, select "Trusted Root Certification Authorities", import our certificate.

If have questions, please post comment. This is my first blog, I'm very glad if someone get help from it :)