Track your force.com site logins by Hour and day

If you have a force.com site and would like to track the user login history report by hour, you would find the login history report is in adequate to meet your needs. If you run the login history report , you have to painfully click more and more to get the entire list and copy to excel to get a report done. If you try downloading the report, it would only report on existing salesforce users only.So how would we track login activity by hour on a force.com site?
The solution is a bit messy and manual and you need to follow some hoops to get it done. So here is the approach.
1. Create a soql query to query the login history object with a filter by login type customer service portal to query all logins by your webusers. and export it in a csv format using a query tool like force.com explorer or workbench.
2. Import it in a custom object with the right time zone format with needed fields.
3. Create a formula field which would extract the hours from the date time field.
4. Create a report querying the custom object and display custom reports for AM and PM hours.

So here are the details of the approach which would work for you guys.
1. Create a soql query to query the login history object with a filter by login type customer service portal to query all logins by your webusers.
You just need a soql query which would query the login history object for a filter and run the soql query using a tool like work bench or force.com ide to export all the data.

SELECT ApiType, ApiVersion, Application, Browser, ClientVersion, Id, LoginTime, LoginType, LoginUrl, Platform, SourceIp, Status, UserId FROM LoginHistory WHERE LoginType = 'Customer Service Portal'

Once you execute this query, you can export the results to a csv file to be loaded to a custom object.
2. Import it in a custom object with the right time zone format with needed fields.
Now import the csv file using Jitterbit or data loader to a custom object. Now in the custom object which i called LogHistory, i just added LoginTime, status, ip and userids as custom fields. You can add more fields which you want on the report. Now here is the time saver tip for the day.
a. You could use excel functions to extract the hour from the timezone but you have to reformat the timezone field which has yyyy-mm-dd hh:mm:ss.00z format in it. This would be a pain to format in excel. So the better approach is use a custom object which understands the time format and loads it in nicely!! An approach which saves time
b. If your org uses east, west or mountain standard time, pay attention to the time zone which you are importing to salesforce. If you use dataloader, make sure you go to settings and set the time zone to the format in your org . Or otherwise you are going to get weird times in the data. If you use jitterbit, again check settings and set the timezone format to the format of your org.
3.Create a formula field which would extract the hours from the date time field.
Now once the custom object is loaded, now all you need is a formula field to extract the hours. The text function is handy to format date time fields to a text and then you need to subtract GMT hours to get to the correct timezone .
Here is the formula example

Text(CASE(VALUE(MID(TEXT( LoginTime__c ) , 12, 2))
, 0, 6,
1, 7,
2, 8,
3, 9,
4, 10,
5, 11,
6, 12,
7, 1,
8, 2,
9, 3,
10, 4,
11, 5,
12, 6,
13, 7,
14,8,
15, 9,
16, 10,
17, 11,
18, 12,
19, 1,
20, 2,
21, 3,
22, 4,
23, 5, 24))

& IF(AND(VALUE(MID(TEXT( LoginTime__c ) , 12, 2)) >= 6,
VALUE(MID(TEXT( LoginTime__c ) , 12, 2)) <= 17), " AM", " PM") //If you use Mountain standard time, GMT time is 6 hours ahead and for East coast it is 5 and central time is 7 hours.

This formula field with return type text would extract the hours from the date time stamp and format it to your time zone and add AM or PM to it. Now this will work for Mountain standard time only and you need to offset it for your timezone.

3.Create a report querying the custom object and display custom reports for AM and PM hours.
Now with the formula field in place, you can just generate a custom report with summary report type where you can group by the Log Hours and display the count of users logged in per month on different times like 5AM, 6 AM, 7 AM, etc.

So here is the time saver for you.
a. Use custom object to load the soql query output of the login history
b. Use timezone of the org before you load data to custom object.
Please feel free to comment on this and I would be glad to answer any questions for you.
Thanks
Buyan

Leave a Reply

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

Get free tips on Salesforce
Get free tips on Salesforce
We respect your privacy.