Today I've decided to show you how to put together all the relevant information from Yammer, using the export API (/export) and a few complementary API calls.
The scenario:
Ideally, this would be an Azure web job running each day, pulling information from Yammer and storing it into an Azure Database. After the information being stored, we will use some Power BI magic to show the data in a cool interactive way.
Let's start with the export API, by creating a method to perform the call and retrieve the csv files in a zipped folder.
public ZipArchive GetExport(DateTime exportStartDate)
{
var url = String.Format("https://www.yammer.com/api/v1/export?model=Message&model=User&model=Group&model=Topic&model=UploadedFileVersion&model=DocumentVersion&access_token={0}&include=csv&include_ens=false&since=" + exportStartDate.ToString("yyyy-MM-dd") + "T00%3A00%3A00%2B00%3A00", this.accessToken);
HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
request.Headers.Add("Authorization", "Bearer" + " " + this.accessToken);
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
using (var stream = response.GetResponseStream())
{
return new ZipArchive(stream);
}
}
}
Notes:
- Since we're going to run the job everyday, the exportStartDate will be DateTime.Now.AddDays(-1);
- The access token will be defined in the App.config file, as shown below
<appSettings>
<add key="Token" value="19094-23I3k4uZtdgXXXXXXXXXX" />
</appSettings>
In order not to make this post too big, I will only demonstrate how to process messages, which is the "messages.csv" file entry within the export zip folder.
So here's how our code will start:
//Date to export from
DateTime exportSince = DateTime.Now.AddDays(-1);
//Get util methods and classes
YammerUtil util = new YammerUtil(token, permalink);
ZipArchive zip = null;
//DB Context
AzureContext dbContext = new AzureContext();
try
{
zip = util.GetExport(exportSince);
//Proceed only if there's data to import
if (zip != null)
{
//Zip archive entries
ZipArchiveEntry msgsCSV = null;
foreach (ZipArchiveEntry entry in zip.Entries)
{
if (entry.Name.Equals("Messages.csv", StringComparison.CurrentCultureIgnoreCase))
msgsCSV = entry;
}
Some of the code above was already explained, so I'm going to detail the rest of it:
- YammerUtil is the class where I've created some general methods (like the export one)
- AzureContext is my database context to use Entity Framework with the Azure DB.
- We will declare a ZipArchiveEntry object to get the "messages.csv" file entry, by iterating through all the entries inside the retrieved zip folder.
Now, considering that we've successfully retrieved our messages, let's declare the lists and methods' classes. These methods will help us to store our objects in Azure and also to perform some complementary calls to retrieve some relevant information.
if (msgsCSV != null)
{
//Lists
List<Message> messages = new List<Message>();
List<Like> likes = new List<Like>();
List<Share> shares = new List<Share>();
List<Mention> userMentions = new List<Mention>();
List<Mention> tagMentions = new List<Mention>();
List<Praise> praises = new List<Praise>();
List<string> threadIDs = new List<string>();
//Methods
MessageMethods messageMethods = new MessageMethods(util, dbContext);
MentionMethods mentionMethods = new MentionMethods(util, dbContext);
LikeMethods likeMethods = new LikeMethods(util, dbContext);
PraiseMethods praiseMethods = new PraiseMethods(util, dbContext);
ShareMethods shareMethods = new ShareMethods(util, dbContext);
From "messages.csv" we can automatically get values like "message_id", "replied_to_id", "group_id", "user_id", etc. Although, we might need some more information, such as, how many likes does this message have? how many shares? Does it mention or praise any user? Does it contain any relevant tags?
In order to retrieve all this information, complementary calls have to be made.
Let's see some methods...
messageMethods.GetMessageDetails(message);
We will need the message attachments, so let's start by appending the attachments to the current message object:
public Message GetMessageDetails(Message msg)
{
try
{
var url = String.Format("https://www.yammer.com/api/v1/messages/{0}.json?access_token={1}", msg.id.ToString(), util.accessToken);
var json = util.GetYammerJson(url);
Message apiStats = JsonConvert.DeserializeObject<Message>(json);
if (apiStats != null && apiStats.attachments != null)
msg.attachments = apiStats.attachments;
}
catch (Exception ex)
{
Console.WriteLine("Message ID: " + msg.id + ". Error getting message details: " + ex.Message);
}
return msg;
}
Once done, we're going to process our messages:
//process messages
util.ProcessMessages(messageMethods, messages, likes, shares, userMentions, tagMentions, praises);
Here's the code...
public void ProcessMessages(MessageMethods messageMethods, List<Message> messages, List<Like> likes, List<Share> shares, List<Mention> userMentions, List<Mention> tagMentions, List<Praise> praises)
{
//do processing based on processing selections
foreach(var message in messages)
{
try
{
//get praises
if (message.attachments != null)
{
foreach (attachment attch in message.attachments)
{
if (attch.type.Equals("praise"))
praises.AddRange(GetUserPraises(attch,message));
}
}
//get mentions
userMentions.AddRange(GetUserMentions(message.bodyText, message.id));
tagMentions.AddRange(GetTagMentions(message.bodyText, message.id));
//get likes
likes.AddRange(messageMethods.GetMessageLikes(message.id.ToString()).Select(x => new Like() { messageId = message.id, userId = x.id }));
//get shares
shares.AddRange(messageMethods.GetMessageShares(message.id.ToString()).Select(x => new Share() { messageId = message.id, userId = x.sender_id, created_at = x.created_at }));
}
catch (Exception)
{
//ignored in the blog's post
}
}
}
... and here's the remaining code for some of the called functions
public static List<Praise> GetUserPraises(attachment attachment,Message msg)
{
List<Praise> praisedUsers = new List<Praise>();
if (attachment.praised_user_ids != null)
{
foreach(int userId in attachment.praised_user_ids)
{
Praise praise = new Praise
{
praisedUserId = userId,
praisorUserId = Convert.ToInt32(msg.user_id),
description = attachment.description,
icon = attachment.icon,
praised_at = msg.created_at
};
praisedUsers.Add(praise);
}
}
return praisedUsers;
}
public static List<Mention> GetUserMentions(string body, int msgId)
{
List<Mention> userMentions = new List<Mention>();
if (!String.IsNullOrEmpty(body))
{
if (!String.IsNullOrEmpty(body))
{
//check for user mentions
var body2 = body;
while (body2.IndexOf("[User:") != -1)
{
var m = body2.Substring(body2.IndexOf("[User:"));
m = m.Substring(0, m.IndexOf("]") + 1);
if (m.Length == 0)
body2 = "";
else
{
var id = m.Substring(6);
id = id.Substring(0, id.IndexOf(':'));
var mention = m.Substring(m.IndexOf(id) + id.Length + 1);
mention = mention.Substring(0, mention.Length - 1);
body2 = body2.Substring(body2.IndexOf(m) + m.Length);
userMentions.Add(new Mention() { messageId = msgId.ToString(), mentionId = id, mentionName = mention });
}
}
}
}
return userMentions;
}
And finally, we add/update our objects in the database:
//Update messages in DB foreach (Message msg in messages) { messageMethods.UpdateMessage(msg); }//Here's the method (Using EF)public bool UpdateMessage(Message message) { Message target = dbContext.Messages.Where(entity => entity.id == message.id).AsQueryable().FirstOrDefault(); if (target == null) { CreateMessage(message); } else { dbContext.Entry(target).CurrentValues.SetValues(message); } return dbContext.SaveChanges() > 0; }
After updating our data, we can then create some Power BI reports and add individual components to our Dashboards, to create something like the dashboard below:
Notes:
- Some information had to be omitted, as this is a solution in production.
- Most part of the code was based on Richard diZerega's Yammer Analytics post.
Let me know if you have any doubts... happy coding =)
