Highlighted

Process Rows if condition is met

New Here ,
Mar 23, 2018

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:

logins.JPG

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!

Views

246

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Process Rows if condition is met

New Here ,
Mar 23, 2018

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:

logins.JPG

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!

Views

247

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 23, 2018 0
LEGEND ,
Mar 23, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
New Here ,
Mar 23, 2018

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
LEGEND ,
Mar 23, 2018

Copy link to clipboard

Copied

It does.  I'll have to give that some thought.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
LEGEND ,
Mar 23, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
New Here ,
Mar 23, 2018

Copy link to clipboard

Copied

Hi,

Thanks again,

In this case yes, the database will show a login without a corresponding logout event.

logins2.JPG

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
LEGEND ,
Mar 23, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
LEGEND ,
Mar 23, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
New Here ,
Mar 23, 2018

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!

output.JPG

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
LEGEND ,
Mar 23, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2018 0
LEGEND ,
Mar 26, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 26, 2018 0
New Here ,
Mar 27, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 27, 2018 0
LEGEND ,
Apr 03, 2018

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 03, 2018 0