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

About The Author

Hi there!
Thanks for visiting my blog. My name is Buyan Thyagarajan (Buyan47) . I am a Salesforce consultant specializing in higher education and helping businesses to measure business results , be proactive with their customers , prevent problems before hand and make the right decisions using Salesforce CRM. This blog would help you in the following way.
a. For Universities, my articles and tips would help you to increase your student recruitment, get better insights on your marketing campaigns and ensure student success by maximizing the features available in your salesforce org, integrate with your student information systems ( Banner, Ancor etc) and create a connected campus on the digital world.
b. For Salesforce admins, my articles would help you to be proactive with your salesforce crm, prevent problems and make the right decisions on different problems with salesforce.
c. For Business executives, my articles would help to measure business results with your salesforce crm, increase adoption and provide solutions for your business problems.
d. For IT executives and developers, my articles would help you on best practices on change management, provide insights on increasing your performance with your current development teams and guide you to make better architectural decisions with salesforce and salesforce1.
I am passionate about the following.
a. Software is meant to automate work but in reality IT is the most manual industry where programmers have to type those 100,000 lines of code everyday to make things work. Programmers get lost in the code and business folks wonder why the programmers are not getting it. I have some insights on this and in the process of publishing a book which should help in the following.
a. Help IT teams to be innovative and offer business value
b. Career pathway for programmers
c. Relevant things a business user can provide to IT to get things done quick.
Please feel free to post your comments on the site on my articles and any questions you have and I would be glad to answer it for you.
To reach me
Ph: 302-438-4097
m: buyan47@gmail.com