Home » Azure ServiceBus: working with datetime filters

Azure ServiceBus: working with datetime filters

I already wrote a blog post about Azure ServiceBus filters which contains the basics of ServiceBus filters and how to use them (Azure ServiceBus filters). It describes most of the filters, but it does not contain how to work with datetime filters. The reason for it is, that datetime filters need to be set programmatically via C#, Powershell or others. The very nice and useful tool ServiceBus explorer also doesn’t support datetime filters and therefore it’s not so easy to test them. So let’s at first see how a standard filter looks like:

A “standard” filter in servicebus looks like:
sys.label LIKE ‘%bus%’

A datetime filter must be set via C# and looks like:

var filter = new SqlFilter("datetime >= @datetime");
filter.Parameters.Add("@datetime", DateTime.Parse("2016-09-12"));

You can see that the filter and the parameter are set in two steps. We need to use the SqlFilter for it and that’s also the reason why it can’t be done via service bus explorer. Therefore we need to do it via C# or Powershell:

How to set and test datetime filters via Powershell and ServiceBus explorer

The solution is a Powershell script which sets a filter and reads data from the ServiceBus. I assume that the topic and the subscription are already created and that there is some testdata in it. If not, please check the following blog post: How to create a topic with testdata in Azure ServiceBus. That blog post uses the classic azure portal. In the new portal it looks like:

20160912_01_servicebusportal

You can find the most important things there – add a topic, add a subscription and the connection strings.

If you don’t have any messages in your subscription (e.g. because you created a new one) – the simplest way is to use the ServiceBus explorer (also described in this blog post).

Powershell Script

Let’s jump over to the Powershell script which reads messages from the servicebus, filters the messages and creates a new subscription with the filter:

Preqrequisites:
Please ensure that you loaded the most important Azure modules and that you have the newest version of Microsoft.ServiceBus.dll. If not, this should do the job:

# import the basic azure modules
Import-Module Azure
Import-Module AzureRM.Profile
Import-Module Azure.Storage

Add-Type -Path "c:\[mypath]\Microsoft.ServiceBus.dll" # can be downloaded via NuGet package and is also part of the ServiceBus explorer

If all prerequisites are met, you can use the following script to test a filter against an existing subscription. If you want to create a new subscription based on the new filter, you can simply uncomment the last line and replace the subscription name with the one that you prefer.
The script is just to demonstrate how to use the date time filter. It defines a filter for messages that were sent to the ServiceBus (sys.EnqueuedTimeUtc) in the year 2016 (between 01.01.2016 and 01.01.2017).

$sbTopicName = "[YOUR SERVICEBUS TOPIC NAME]"                              # the name of the topic in the servicebus
$sbSubscriptionName = "[YOUR SERVICEBUS TOPIC SUBSCRIPTION NAME]"          # the name of the topic subscription 
$connectionString = "Endpoint=sb://... [THE SERVICEBUS CONNECTION STRING]" # the connection string to the servicebus

$namespaceManager = [Microsoft.ServiceBus.NamespaceManager]::CreateFromConnectionString($connectionString);
$topic = $namespaceManager.GetTopic($sbTopicName)
$sbsubscription = $namespaceManager.GetSubscription($topic.Path, $sbSubscriptionName)

# define the filter
$sqlfilter = new-object Microsoft.ServiceBus.Messaging.SqlFilter("sys.EnqueuedTimeUtc >= @datetime AND sys.EnqueuedTimeUtc < @datetime2")
$sqlfilter.Parameters.Add("@datetime", [DateTime]::Parse("2016-01-01 00:00"));
$sqlfilter.Parameters.Add("@datetime2", [DateTime]::Parse("2017-01-01 00:00"));
$filter = $sqlfilter.Preprocess()

$subscriptionClient = [Microsoft.ServiceBus.Messaging.SubscriptionClient]::CreateFromConnectionString($connectionString, $topic.Path, $sbsubscription.Name)
$messages = $subscriptionClient.PeekBatch(100) # receive 100 messages from the servicebus subscription

"all messages: " # show all 100 messages 
$messages | ft MessageId, ExpiresAtUtc, EnqueuedTimeUtc, Properties

"==================================================================="
"filtered messages: " # show only the messages that matches the filter
$messages | where-object { $filter.Match($_) } | ft MessageId, ExpiresAtUtc, EnqueuedTimeUtc, Properties

#if you want to create a new subscription with the defined filter - uncomment the following line of code
#$namespaceManager.CreateSubscription($sbTopicName, "Messages2016", $sqlfilter) # "Messages2016" is the name of the new subscription

If you execute it and you don’t see any messages – please ensure that there are already messages in the subscription. The script shows the first 100 messages that are available and after that it shows the ones that matches the filter. If “all messages” doesn’t show anything – use servicebus explorer (or whatever you like) to send some test messages to your topic/subscription.

The same piece of code can be “translated” to C# and used inside of an application:

C# sample to set and test ServiceBus datetime filter

Before we can use the C# code, we have to add the NuGet package for the ServiceBus to our project:

20160912_02_servicebusnugetpackage

After that we can use the following piece of code which does the same as the Powershell script above. In fact it is the same as the script above – just translated to C#.

        static void Main(string[] args)
        {
            string sbTopicName = "[YOUR SERVICEBUS TOPIC NAME]";  // the name of the topic in the servicebus
            string sbSubscriptionName = "[YOUR SERVICEBUS TOPIC SUBSCRIPTION NAME]"; // the name of the topic subscription
            string connectionString = "Endpoint=sb://... [THE SERVICEBUS CONNECTION STRING]"; //the connection string to the servicebus

            var namespaceManager = Microsoft.ServiceBus.NamespaceManager.CreateFromConnectionString(connectionString);
            var topic = namespaceManager.GetTopic(sbTopicName);
            var sbSubscription = namespaceManager.GetSubscription(topic.Path, sbSubscriptionName);

            var sqlfilter = new Microsoft.ServiceBus.Messaging.SqlFilter("sys.EnqueuedTimeUtc >= @datetime AND sys.EnqueuedTimeUtc < @datetime2");
            sqlfilter.Parameters.Add("@datetime", DateTime.Parse("2016-01-01 00:00"));
            sqlfilter.Parameters.Add("@datetime2", DateTime.Parse("2017-01-01 00:00"));
            var filter = sqlfilter.Preprocess();

            var subscriptionClient = Microsoft.ServiceBus.Messaging.SubscriptionClient.CreateFromConnectionString(connectionString, topic.Path, sbSubscription.Name);
            var messages = subscriptionClient.PeekBatch(100); // receive 100 messages from the servicebus subscription

            Console.WriteLine("all messages: "); 
            messages.ToList().ForEach(x => WriteToOutput(x)); // show all 100 messages 

            Console.WriteLine("filtered messages: ");
            var filteredMessages = from x in messages where filter.Match(x) select x; // show only the messages that matches the filter
            filteredMessages.ToList().ForEach(x => WriteToOutput(x));

            // if you want to create a new subscription with the defined filter - uncomment the following line of code
            // namespaceManager.CreateSubscription(sbTopicName, "Messages2016", sqlfilter); // "Messages2016" is the name of the new subscription

            Console.Read();
        }

        private static void WriteToOutput(BrokeredMessage x)
        {
            var properties = from v in x.Properties select "{ " + v.Key + " : " + v.Value + " }";
            Console.WriteLine(string.Format("{0} {1} {2} {3}", x.MessageId, x.ExpiresAtUtc.ToString(), x.EnqueuedTimeUtc.ToString(),
                string.Concat(properties)));
        }

Additional information

Leave a Reply

Your email address will not be published. Required fields are marked *