Copy link to clipboard
Copied
Hi all,
I have been asked to develop something in CF. I am new to the world of CF development and trying to learn as best I can.
I am using CF8 as that is all that is available in my work environment.
I have posted my requested here in the hope someone can assist and give some pointers.
We have a system which records login and logouts for computers on our campus. I need to work out how long each computer has been used for and we can't change what the program logs as it is a third party tool used for managing the network.
I have got to a point where I can get the time calculations if a user logs in and logs out once a day or multiple times on different computers. I do this by creating two queries one with retrieves the logins and one which retrieves the logouts. I then do an if statement which matches the MacStudent and if the ID for the logout is greater than the ID of the login which works although it may be overkill and probably not best practice!
However my main issue is when a user logs in and out of the SAME computer multiple times in a a single day. I cant figure out a way of identifying which login relates to which logout programatically.
So for testing purposes say I have the following records retrieved:
I need to be able to loop through all the records and then calculate time between a login event and only the NEXT logout event.
So for the example recordset above - I would expect to calculate 25 mins use recorded. but at the moment i cant figure out how to ignore the rest of the logouts as when i do my loop it takes the first login and calculates the time against ALL the logouts, then moves to the second login event and calculates the time against ALL the logouts below that and it carries on until the loop finishes.
I hope that makes sense to someone and any help appreciated!
Thanks in advance!
Copy link to clipboard
Copied
Hello, blackburncollege​, and welcome to the world of coding CF. I hope you enjoy it as much as I and others here do.
I would like to mention that you don't need to do two separate queries and compare. You can do this with one query. As far as which record is login and which is logout, if your database is as per the image provided, then you're already registering the login / logout, you just need to sort by user then timestamp (I hope you didn't really name the column 'timestamp') as shown in the image. This helps to assure that all odd-numbered rows will be login and all the even numbered rows will be logoff.
I recently learned a neat trick from BKBK using an array to calculate differences that might help.
V/r,
^ _ ^
Copy link to clipboard
Copied
Hi,
Thanks for your response, I am enjoying my time CF so far!
I did try the one query and sorting by User/Timestamp but it doesn't necessarily mean that odd numbers are logins and even numbers are logouts as a single user can login to multiple computers, so by doing user/timestamp I can have several rows together showing as login.
I originally did it as two queries as i nested the loops like:
set totaltime used variable
loop login
loop logout
if login.macstudent eq logout macstudent and login.id LT logout.id
do calculations between login.timestamp and logout.timestamp and append to totaltime used variable
close if
close logout loop
close login loop
like i mentioned, this approach works fine if you assume that a user logs into a computer once a day or logins to multiple computers in the same day as the macstudent variable is a combination of the computerid-loginid-dd-mm-yyyy.
My only issue is if the user logs in and out of the same computer many times in a day as the loop above will always tally up the time difference between all logout events taken place after the initial login. i need some way to identify the next logout event only.
hope that makes sense!
thanks again!
Copy link to clipboard
Copied
It does. I'll have to give that some thought.
V/r,
^ _ ^
Copy link to clipboard
Copied
Hypothetical situation: User1 logs on to computer1, then computer1 locks hard requiring a hard shut down, then is powered back on. User1 logs back on to computer1. Does the database detect the shutdown as a logoff? Or is anything done to indicate that user1 was forced off the system? Or will the database show two logins with one logoff?
V/r,
^ _ ^
Copy link to clipboard
Copied
Hi,
Thanks again,
In this case yes, the database will show a login without a corresponding logout event.
so in this example applecentre logged in, then the machine crashed, when it came back tempmac0 logged in and the machine crashed, when it came back online again, tempmac0 logged in and had a successful logout, following that applecentre had a succesfully login/logout.
so for instances like this we will just ignore the first login that doesn't have a corresponding logout.
Hope that helps!
Copy link to clipboard
Copied
Yes, and thanks for the clarification. I'll give this some thought and report if I can think of a solution.
V/r,
^ _ ^
Copy link to clipboard
Copied
Okay.. so I think I came up with something. But keep in mind that what you requested is fairly complex, so my suggestion is fairly complex. If you need clarification, just ask. (I haven't tested this.)
TIMESTAMP ACTION USERNAME MACSTUDENT
2018-03-23 13:01:56.598 login applecenter c1applecenter20180322
2018-03-23 13:02:33.129 login testmac c1testmac20180322
2018-03-23 13:04:11.581 login testmac c1testmac20180322
2018-03-22 13:04:22.302 logout testmac c1testmac20180322
2018-03-22 13:04:45.997 login applecenter c1applecenter20190322
2018-03-22 13:05:22.024 logout applecenter c1applecenter20180322
<cfquery name="logDiff" datasource="#request.dsn#">
SELECT a.timestamp, a.action, a.username, a.macstudent, 0 as total
from tableA a
ORDER BY a.macstudent, a.timestamp, a.action
</cfquery>
RESULTS
TIMESTAMP ACTION USERNAME MACSTUDENT total (ROW)
2018-03-23 13:01:56.598 login applecenter c1applecenter20180322 0 1
2018-03-22 13:04:45.997 login applecenter c1applecenter20190322 0 2
2018-03-22 13:05:22.024 logout applecenter c1applecenter20180322 0 3
2018-03-23 13:02:33.129 login testmac c1testmac20180322 0 4
2018-03-23 13:04:11.581 login testmac c1testmac20180322 0 5
2018-03-22 13:04:22.302 logout testmac c1testmac20180322 0 6
<cfset timeDiff = arrayNew(1) />
<cfset loginRow = 0 />
<cfoutput query="logDiff">
<!--- Get difference in seconds between this row and previous row, IF the action is not the same as the previous row. --->
<cfif logDiff.action[logDiff.currentrow] eq "login">
<cfset timeDiff[logDiff.currentrow] = 0 />
<!--- This is a login row; gets reset if there are more than one login actions in a row. You indicated that previous logins without a logout will be ignored. --->
<cfset loginRow = logDiff.currentrow />
<cfelse>
<cfset timeDiff[logDiff.currentrow] = DateDiff("s",logDiff.timestamp[loginRow],logDiff.timestamp[logDiff.currentrow]) />
</cfif>
<!--- Set total (logged in seconds) to the difference between last login and current logout. (Will be 0 for all logins) --->
<cfset logDiff.total[logDiff.currentrow] = timeDiff[logDiff.currentrow] />
</cfoutput>
<cfdump var="#logDiff#" />
Of course, adjust time units as needed. I used seconds just because.
V/r,
^ _ ^
Copy link to clipboard
Copied
Hi,
Thanks so much for taking the time to look at this with me! I really appreciate it.
I have just executed the code, not changed anything apart from the query statement to retrieve the records from my earlier test.
This is the output I get and without looking at it in too much detail it does look promising!
Unfortunately I won't be able to do further testing or development with this until Monday now as it is the weekend with no remote access to the dev server! I will pick this up again when I return to the office and let you know the outcome.
But once again I really appreciate the help.
Copy link to clipboard
Copied
Glad I could be of help. And, yes, please let me know how it went after you try it on Monday.
Have a great weekend.
V/r,
^ _ ^
Copy link to clipboard
Copied
Hi,
Just checking to see if this worked, for you. Or if you have other requirements that might necessitate any alterations.
V/r,
^ _ ^
Copy link to clipboard
Copied
Hi,
Thanks for keeping in touch, I will be picking this back up again later this afternoon as I had some other work which needed attention first.
I will let you know how I get on once I've started concentrating on this again.
Kind Regards
Copy link to clipboard
Copied
Hi,
Just checking to see if this worked, or if you're having any issues with it that I might be able to help with.
V/r,
^ _ ^