Thursday, 21 March 2013

Writing RazorSharp script to import TeamMentor users

Now that we are in the final stages of publishing TM 3.3. out of the door, one of the tasks I had to do is to import the old user schema into the new format.

To to that I wrote this RazorSharp script (which can be coded in a REPL like environment) to do the import:

@using System.Xml.Linq
@using TeamMentor.CoreLib
@using O2.DotNetWrappers.ExtensionMethods
@{
    var userData = TM_UserData.Current;
    var userFolder = userData.getTmUsersFolder();
    var tmUsersXml = userFolder.pathCombine("..\\tmusers.xml");
    var importUsers = HttpContextFactory.Request.QueryString["ImportUsers"] == "yes";
    var creationDate = DateTime.Parse("10/08/2012");
    
    Func<TMUser, bool> userExists =
        (tmUser) =>
            {
                return tmUser.UserName.tmUser().notNull() ||
                       tmUser.UserID.tmUser().notNull() ||
                       (tmUser.EMail.valid() && tmUser.EMail.tmUser_FromEmail().notNull());
            };
    Func<XElement, TMUser> getUserFromXElement =
        (user) =>
            {
                var userID = user.element("{https://TeamMentor.securityinnovation.com:13415/}UserID").Value;
                var userName = user.element("{https://TeamMentor.securityinnovation.com:13415/}UserName").Value;
                var firstName = user.element("{https://TeamMentor.securityinnovation.com:13415/}FirstName").Value;
                var lastName = user.element("{https://TeamMentor.securityinnovation.com:13415/}LastName").Value;
                var title = user.element("{https://TeamMentor.securityinnovation.com:13415/}Title").Value;
                var company = user.element("{https://TeamMentor.securityinnovation.com:13415/}Company").Value;
                var eMail = user.element("{https://TeamMentor.securityinnovation.com:13415/}EMail").Value;
                var groupID = user.element("{https://TeamMentor.securityinnovation.com:13415/}GroupID").Value;

                var tmUser = new TMUser
                    {
                        UserID = userID.toInt(),
                        UserName = userName,
                        FirstName = firstName,
                        LastName = lastName,
                        Title = title,
                        Company = company,
                        EMail = eMail,
                        GroupID = groupID.toInt()                        
                    };
                tmUser.Stats.CreationDate = creationDate;
                return tmUser;
            };
            
        
}
<h4>Importing Users From Previous TMUsers</h4>

@if(tmUsersXml.fileExists())
{
    <span>
        <b>Found tmusers.xml</b> at : @tmUsersXml
       
        <br/>
        <br/>
        <p>
            <a class="btn btn-inverse" href="Import Legacy Users?ImportUsers=yes">Import Users</a>
            
            @if (importUsers)
            {
                <br /><br /><span>IMPORTING USERS.... using creationDate : @creationDate</span>
            }
        </p>
        
        <br/>
    
        <table class="table table-striped table-condensed">
            <tr>
                <th>Exists?</th>   
                <th>Imported</th>   
                <th>userID</th>            
                <th>userName</th>
                <th>firstName</th>            
                <th>lastName</th>   
                <th>title</th>
                <th>company</th>
                <th>eMail</th>
                <th>groupID</th>                
            </tr>   

    @{
        var users = tmUsersXml.xRoot().elements().take(12);
        foreach (var user in users)
        {
            var tmUser = getUserFromXElement(user);
            var existInTM = userExists(tmUser);
            
            var trClass = (existInTM) ? "success" : "error";
            
            var imported = false;
            if (existInTM.isFalse() && importUsers)
            {
                userData.TMUsers.add(tmUser);
                tmUser.saveTmUser(); 
                if (userExists(tmUser))
                {
                    imported = true;
                    trClass = "info";  
                }
                else
                {
                    trClass = "error";  
                }
                                
            }
            <tr class="@trClass">
                <td>@existInTM</td>
                <td>@imported</td>
                <td>@tmUser.UserID</td>            
                <td>@tmUser.UserName</td>
                <td>@tmUser.FirstName</td>            
                <td>@tmUser.LastName</td>   
                <td>@tmUser.Title</td>
                <td>@tmUser.Company</td>
                <td>@tmUser.EMail</td>
                <td>@tmUser.GroupID</td>                
            </tr>
        }
    }

        </table>

    </span>
}
else
{
    <span>
    <hr/>

    <b>Did not Found tmusers.xml</b> at location: @userFolder
    </span>
}


The script will look for a tmUsers.xml file (the old format xml file which contained all user info)

 image

and will convert it into the new {username:first 10 chars}-UserID_GUID.xml format

image 

Here is what this RazorSharp script looks like when executed via the TBot control panel (this is before the import is triggered, and filtered to only show SI accounts)

image

And like this after clicking the import button:

image

(note that the 2nd Karen’s account was not imported, since TM now required unique emails)

If we increase the sample size to import (note the take() filter in the users variable used in the foreach loop), we can see which accounts exist (and which ones need to be imported)

image

To make it easier to spot the duplications, I added an 'Exact Match?’ column.

Here is Karen’s duplicate account

image

Here is Fred’s duplicate account (which will not be imported)

image

Here is the same screen after the import:

image

Note that the search for ‘User Exists’ is based on the Username, UserID or EMail. So in the screenshot above , it shows that the last user already exists (UserName = “alistair”) but that it was not imported (note that the email address is mine).

We can confirm that this user doesn’t exist by looking at TBot’s users page:

image

Adding users to GitHub

Next step into add the users to the Git Repository which at the moment looks like this at GitHub

image

and like this locally:

image

We will need to do a manual commit since git commits on UserData are disabled for localhost sites:

image

followed by a push:

image

and once the data is on GitHub:

image

We go to the Live QA server and reload the UserData (note that we are not in an localhost server anymore)

image

And  our (locally imported users) are now in the live TeamMentor site:

image

Final version of the code:

@using System.Xml.Linq
@using TeamMentor.CoreLib
@using O2.DotNetWrappers.ExtensionMethods
@{
    var userData = TM_UserData.Current;
    var userFolder = userData.getTmUsersFolder();
    var tmUsersXml = userFolder.pathCombine("..\\tmusers.xml");
    var importUsers = HttpContextFactory.Request.QueryString["ImportUsers"] == "yes";
    var creationDate = DateTime.Parse("10/08/2012");
    
    Func<TMUser, bool> userExists =
        (tmUser) =>
            {
                return tmUser.UserName.tmUser().notNull() ||
                       tmUser.UserID.tmUser().notNull() ||
                       (tmUser.EMail.valid() && tmUser.EMail.tmUser_FromEmail().notNull());
            };
            
   Func<TMUser, bool> isExactMatch =
        (tmUser) =>
            {
                return tmUser.UserName.tmUser().notNull() &&
                       tmUser.UserID.tmUser().notNull() &&
                       (tmUser.EMail.notValid() || tmUser.EMail.tmUser_FromEmail().notNull());
            };         
    Func<XElement, TMUser> getUserFromXElement =
        (user) =>
            {
                var userID = user.element("{https://TeamMentor.securityinnovation.com:13415/}UserID").Value;
                var userName = user.element("{https://TeamMentor.securityinnovation.com:13415/}UserName").Value;
                var firstName = user.element("{https://TeamMentor.securityinnovation.com:13415/}FirstName").Value;
                var lastName = user.element("{https://TeamMentor.securityinnovation.com:13415/}LastName").Value;
                var title = user.element("{https://TeamMentor.securityinnovation.com:13415/}Title").Value;
                var company = user.element("{https://TeamMentor.securityinnovation.com:13415/}Company").Value;
                var eMail = user.element("{https://TeamMentor.securityinnovation.com:13415/}EMail").Value;
                var groupID = user.element("{https://TeamMentor.securityinnovation.com:13415/}GroupID").Value;

                var tmUser = new TMUser
                    {
                        UserID = userID.toInt(),
                        UserName = userName,
                        FirstName = firstName,
                        LastName = lastName,
                        Title = title,
                        Company = company,
                        EMail = eMail,
                        GroupID = groupID.toInt()                        
                    };
                tmUser.Stats.CreationDate = creationDate;
                return tmUser;
            };
            
        
}
<h4>Importing Users From Previous TMUsers</h4>

@if(tmUsersXml.fileExists())
{
    <span>
        <b>Found tmusers.xml</b> at : @tmUsersXml
       
        <br/>
        <br/>
        <p>
            <a class="btn btn-inverse" href="Import Legacy Users?ImportUsers=yes">Import Users</a>
            
            @if (importUsers)
            {
                <br /><br /><span>IMPORTING USERS.... using creationDate : @creationDate</span>
            }
        </p>
        
        <br/>
    
        <table class="table table-striped table-condensed">
            <tr>
                <th>Exists?</th>   
                <th>Exact Match?</th>
                <th>Imported?</th>   
                <th>userID</th>            
                <th>userName</th>
                <th>firstName</th>            
                <th>lastName</th>   
                <th>title</th>
                <th>company</th>
                <th>eMail</th>
                <th>groupID</th>                
            </tr>   

    @{
        var users = tmUsersXml.xRoot().elements();//.take(100);
        foreach (var user in users)
        {
            var tmUser = getUserFromXElement(user);
            if (tmUser.EMail.contains("securityinnovation.com").isFalse() && tmUser.UserName != "admin")
            {
                continue;
            }
            var existInTM = userExists(tmUser);
            var exactMatch = isExactMatch(tmUser);
            var trClass = (existInTM) 
                                ?  (exactMatch) 
                                        ? "success" : "info"
                                : "error";
            
            var imported = false;
            if (existInTM.isFalse() && importUsers)
            {
                userData.TMUsers.add(tmUser);
                tmUser.saveTmUser(); 
                if (userExists(tmUser))
                {
                    imported = true;
                    trClass = "info";  
                }
                else
                {
                    trClass = "error";  
                }
                                
            }
            <tr class="@trClass">
                <td>@existInTM</td>
                <td>@exactMatch</td>
                <td>@imported</td>                
                <td>@tmUser.UserID</td>            
                <td>@tmUser.UserName</td>
                <td>@tmUser.FirstName</td>            
                <td>@tmUser.LastName</td>   
                <td>@tmUser.Title</td>
                <td>@tmUser.Company</td>
                <td>@tmUser.EMail</td>
                <td>@tmUser.GroupID</td>                
            </tr>
        }
    }

        </table>

    </span>
}
else
{
    <span>
    <hr/>

    <b>Did not Found tmusers.xml</b> at location: @userFolder
    </span>
}


Post a Comment