Newly added users on a site collection

Question

I want help in writing a SQL Query or a custom code or any tool which can provide me with a list of users added in a site collection in MOSS 2007 after a particular date only.

This is urgent .

Thanks,
Deepika

Answer 1

Hi Deepika,

There is a hidden list on SharePoint sites that stores this info. You can query that using standard CAML queries, like shown below (assuming your code runs in SharePoint context):

SPList siteUserInfoList = SPContext.Current.Web.SiteUserInfoList;
DateTime fromDate = DateTime.Now.AddDays(-5); // you can set any date you wish here
String fromDateString = SPUtility.CreateISO8601DateTimeFromSystemDateTime(fromDate);
String query = String.Format("<Where><Gt><FieldRef Name='Created' IncludeTimeValue='TRUE' /><Value Type='DateTime'>{0}</Value></Gt></Where>", fromDateString);
SPQuery userQuery = new SPQuery();
userQuery.Query = query;
SPListItemCollection userItems = siteUserInfoList.GetItems(userQuery);

Hope that helps.

Peter

Answer 2

Thanks Peter.

The User List here is for site collection or for a particular site within a site collection?
Where am i giving the address of my sitecollection or site?

Deepika

Answer 3

Hi,

Users are stored on the site collection level. The SPWeb.SiteUserInfoList property (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spweb.siteuserinfolist.aspx) returns you the correct list for the web.If you need info for other site, not the one in the current context, you can get it like shown below:

using(SPSite site = new SPSite("http://yoursite"))
{
  using(SPWeb web = site.OpenWeb())
   {
      SPList siteUserInfoList = web.SiteUserInfoList;
   }
}


Peter

Answer 4

Thanks Peter.

I will try with this one.
Is there any SQL query also for the same thing wherein it queries the database and gets the user added after a particular date?


Thanks,
Deepika

Answer 5

Hi ,

Please help me with the following code:

I am using Visual Studio 2005 and i wrote the following code in Console Application .
Please let me know whether this code will give me the list of users added just five days back from today?

using

 

System;

using

 

System.Collections.Generic;

using

 

System.Text;

using

 

Microsoft.SharePoint;

using

 

Microsoft.SharePoint.Utilities;

namespace

 

MOSSUsers123

 

class Program

{

 

static void Main(string[] args)

 

using (SPSite site = new SPSite("http://usspdev03:12888/sites/GlobalMarketAndSalesSupport/"))

 

using (SPWeb web = site.OpenWeb())

 

SPList siteUserInfoList = web.SiteUserInfoList;

 

 

DateTime fromDate = DateTime.Now.AddDays(-5);

 

 

String fromDateString = SPUtility.CreateISO8601DateTimeFromSystemDateTime(fromDate);

 

 

String query = String.Format("<Where><Gt><FieldRef Name='Created' IncludeTimeValue='TRUE'/><Value Type='DateTime'>{0}</Value></Gt></Where>", fromDateString);

 

 

SPQuery userQuery = new SPQuery();

 

SPListItemCollection userItems = siteUserInfoList.GetItems(userQuery);

}

}

 

}

}

}

Answer 6

Hi Deepika,

Yes, you can do that from SQL too, but you should be warned, that it is highly unsupported. The SQL query looks like this (use the content database of the site):

select nvarchar3, nvarchar1, * from AllUserData where tp_ContentType = 'Person' and tp_Created > '2008.01.01' and tp_SiteId = '1A2ECCAB-2735-45B1-8119-C69BFDA33319'

You should replace the date, and the site id (or remove the latter filter if you don't need that). If you need to filter for groups too, use the tp_ContentType = 'SharePointGroup' condition.

If you need the last modified time, use tp_Modified instead of tp_Created.

Hope that helps, but please tell it to nobody that you have heard that from me! :-)

Peter

Answer 7

Hi,

"Please let me know whether this code will give me the list of users added just five days back from today?"

No, your code will return all of the users since an important line of code (setting the filter) is missing after creating the SPQuery object:
userQuery.Query = query;

And probably you would like to print out the results:
foreach (SPListItem user in userItems)
{
       Console.WriteLine("Login: {0}; created: {1}", user["Title"], user["Created"]);
}

Peter

Answer 8

Thank u so much Peter. This query worked for me . it was so much useful.

I was not able to get the groups in which these users are added.
i gave this condition tp_ContentType='GlobalMarketAndSalesSupportVisitors'

Please help..

Thanks,
Deepika

Answer 9

Hi,

As I wrote, you should use the condition:
tp_ContentType = 'SharePointGroup'
Do not replace the word 'SharePointGroup' to the actual name of your SharePoint group. It will be included in the result set.

If one (or more) of the replies is helpful or answers your question, please mark it as such. If you need more help on the topic, please, let us know how we can help you! ;-)

Thanks!

Peter

Answer 10

Actually i think you didn't understand my query.
I want the groups where the newly added users are present.
I am getting the list of users added after a particular date but i also want their groups in which they are present.

I tried with your above query but it does not return me any result .But if i do not put the group condition  i get the result.

Please help.

Thanks,
Deepika

Answer 11

There are users and groups. You can query for users or for groups but a record may contain only one of them, so if your condition is something liek tp_ContentType = 'SharePointGroup' AND tp_ContentType = 'Person' then the result will be empty. You can query only for new users (that is your original request) or for new groups but the query is not for getting info about to which group the user has been added.

"i also want their groups in which they are present"
That is a new requirement. Using the object model (see the CAML query above) you can get the group membership of the users, but you cannot get info about what group membership a user got or had at a given time.

Peter

Answer 12

Is it not possible to get the newly added users after a partiuclar date along with their groups in which they are added?
Probably a join on 2 or 3 tables could be used.

Please help..

Thanks,
Deepika

Answer 13

Hi,

"Probably a join on 2 or 3 tables could be used."

I don't think so. If you need such details, then probably you are on the wrong track. Hopefully you can get more results using the audit log.

For more info:
Reading Entries from the Audit Log in Windows SharePoint Services 3.0
http://msdn.microsoft.com/en-us/library/bb466223.aspx

Peter

Answer 14

Got your point Peter.
 Thank you so much.
I faced an issue while running that query. I added a user on my site collection and when i executed that query with tp_created i got the desired result.
But when i deleted that user from the site collection i can still the same result.
Ideally the row for that user should not have come when queried.

Please help and correct me if i am wrong.

Thanks,
Deepika

Answer 15

Hi,

Yes, you might be right, as far as I remember there is a flag column in the table that shows if the record is active or not. You can find that yourself and include that filter in the query, or I will do that later when I will be nearby a SharePoint content database.

Peter

Answer 16

I think i am not sure. It is either bit1 or bit3 . Please confirm and let me know. Also provide me the full query with filters. Thanks, Deepika