Access Office 365 Mail Box by Exchange Service Managed API

So here is the requirement from clinet: We are required to monitor several Office 365 mail boxes, and if the specific email is found, we need to do some follow up activities, e.g. re-distribute the email to a nominated user.

To access the Office 365 mail box, we can use Exchange Service Managed API. In the old way we can use method listed at here. However now we can use a much easier way to handle the request, as now Office 365 provides a direct endpoint for EWS Managed API. Below is a quick demonstration of how to access Office 365 mail box.

The first step, and the only step to prepare the project, is to reference the EWS API in your project. The API can be referenced into your project from nuget directly. Or alternatively you can obtain it from Microsoft download centre. If you download the API from Microsoft download centre, you need to manually reference the API in your project. The dll can be found at “C:\Program Files (x86)\Microsoft\Exchange\Web Services\{version no.}\Microsoft.Exchange.WebServices.dll”.

Now time to start coding. Firstly we need to declare the endpoint for the EWS API.

       string emailaddr = "{o365 account}";  
       string password = "{o365 password}";  
       ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2013_SP1);  
       service.Credentials = new WebCredentials(emailaddr, password);  
       Uri u = new Uri(@"https://outlook.office365.com/EWS/Exchange.asmx");  
       service.Url = u;        

To make things simple, assume we are going to search unread emails in the inbox folder. So we create a search view for the folder

       Folder inbox = Folder.Bind(service, WellKnownFolderName.Inbox);  
       SearchFilter filter = new SearchFilter.SearchFilterCollection(LogicalOperator.And, new SearchFilter.IsEqualTo(EmailMessageSchema.IsRead, false));  
       ItemView view = new ItemView(10);  

Now there is a small trouble: we need to find the item ID from FindItems call, then we use GetItem call to get item contents.

       FindItemsResults<Item> results = service.FindItems(WellKnownFolderName.Inbox, filter, view);  
       ServiceResponseCollection<GetItemResponse> responses = service.BindToItems(results.Select(i => i.Id)  
         , new PropertySet(BasePropertySet.FirstClassProperties, EmailMessageSchema.From, EmailMessageSchema.ToRecipients));  
       foreach (var item in responses)  
       {  
         EmailAddress fromaddress = (EmailAddress)item.Item[EmailMessageSchema.From];  
         EmailAddressCollection to = (EmailAddressCollection)item.Item[EmailMessageSchema.ToRecipients];  
         string toaddress = string.Empty;  
         foreach (EmailAddress a in to)  
         {  
           toaddress += a.Address + "; ";  
         }  
         Console.WriteLine("From: {0}", fromaddress.Address);  
         Console.WriteLine("To: {0}", toaddress);  
         Console.WriteLine("Subject: {0}", item.Item.Subject);  
         Console.WriteLine("Body:{0}", item.Item.Body == null ? string.Empty : item.Item.Body.Text);  
         Console.WriteLine();  
       }  
       Console.ReadLine();  

So that is all, quick and simple. Below is the result from above code

Report SSRS report definition from TSQL

Within this holiday season, it is really difficult to bring on a topic relates to techs. So I decided to make it simple and short: let’s talk about how to manage reports by T-SQL.

If you had read my previous posts, I had demonstrated how to read and modify report definitions by C# codes. But there could be situations that we want to read report definitions for specific purpose, e.g. report documentation.

To document the report definition, try below codes (assume there is a report named "test report"):  

 

USE ReportServer
go

;WITH XMLNAMESPACES   
 (   
 DEFAULT   
 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',   
 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'  
 AS rd   
 )  
 SELECT cte.name as ReportName  
   , ds.value('(@Name)[1]', 'VARCHAR(250)') as DatasetName  
      , ds.value('(Query/DataSourceName)[1]','VARCHAR(250)') as DataSourceName  
      , ds.value('(Query/CommandText)[1]','VARCHAR(250)') as Query  
      , fd.value('(@Name)[1]','VARCHAR(250)') as FieldName  
      , fd.value('(DataField)[1]','VARCHAR(250)') as DataField  
      , fd.value('(rd:TypeName)[1]','VARCHAR(250)') as DataType  
  FROM   
  (   
      SELECT C.Name  
           ,CAST(CAST(C.Content as VARBINARY(MAX)) as xml) AS reportXML  
      FROM ReportServer.dbo.Catalog C  
      WHERE C.Content is not null  
      AND C.Type = 2 and c.Path like '%test report%'  
 ) cte  
      CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(ds)  
      CROSS APPLY ds.nodes('Fields/Field') f(fd)   
 ORDER BY name   

It them will give you result like below screenshot:

The only thing needs to be noticed is about the XML namespace declaration. As shown above, the "2010/01" is for SSRS 2012. Depends on the edition of the SSRS you are using, you might need to change the declaration a bit. For instance, if the report was created from SSRS 2008, you need to change the definition to "2008/01".

Simple and easy, isn't it?

So that is it. The last day of 2015, and last post of 2015.  Happy New Year!

SQL - custom log schema via XML

It has been a long time since my last post: After took my holiday for about one month, it is just so difficult to get myself ready for work :(

But anyway, when I came back immediately I found there were quite a lot works need to be settled: We are changing our whole dialler to a brand new one; we are having some new opportunities from both internal and externals; we are planning to update our data platforms, such as ETL management frameworks, etc, etc.

When I reviewed our team's old works, I found below codes and just want to log here. I could not find the original source of the codes, but if my memory is correct, the credit of the codes should be to one of the SQL books. (professional SQL Server 20xx, probably) So if you know where the source of below codes is, please leave me a message so I can link to codes to original source.

The purpose of below codes is to log DML actions via XML and table triggers. Basically we will turn insert and update actions into xml format through the table trigger, and then log into the DML log table.

Now firstly we have table structure like below

 Create table SysLog.DMLLog  
 (  
 LogKey bigint identity(1,1),  
 TableName varchar(255),  
 TriggerName varchar(255),  
 SpID int,  
 Username varchar(255),  
 DMLAction XML,  
 CreatedOn datetime,  
 Constraint PK_DMLLog primary key(LogKey)  
 )  
 go  

Fields we created are self-explanatory, so I won't spend time to explain it :p.

Now it is time about the trigger, assume we are going to build the trigger on Account table. Something like below:

 Create Trigger TriLogAccountDML   
 On Account  
 After insert, update, delete  
 as  
 BEGIN  
      DECLARE @XmlAction XML, @TriID int, @TblID int  

      SELECT @XmlAction = ISNULL((SELECT * FROM inserted FOR XML AUTO), '<inserted/>') + ISNULL((SELECT * FROM deleted FOR XML AUTO), '<deleted/>') 
 
      SELECT @TriID = @@PROCID  
      SELECT @TblID = parent_id FROM sys.triggers WHERE object_id = @TriID  

      INSERT INTO SysLog.DMLLog(TableName, TriggerName, SpID, Username, DMLAction, CreatedOn)  
      SELECT OBJECT_NAME(@TblID), OBJECT_NAME(@TriID), @@SPID, SYSTEM_USER, @XmlAction, GETDATE();  
 END  

Now we are done. In the end we will have our log table like below:

Quite simple isn't it? :D