Forum Discussion
User login time: using functions across multiple rows
Forum,
I would like to create some visuals that show user login time that can be sliced by the date and the type of software. I have log files that record date/time, user id, and software id but do not differentiate between login and logout events (example data shown below). I am thinking that I need to assign each row to be either login or logout with a calculated column, then write a measure that can tally up login times. This poses some challenges though. Am I on the right track? Does anyone have an idea as to how to perform this (what kinds of functions)?
example data:
| User ID | Software ID | Date-Timestamp (sorted chronologically) |
| 2 | 7 | xxx |
| 2 | 10 | xxx |
| 10 | 7 | xxx |
| 10 | 7 | xxx |
| 2 | 55 | xxx |
| 2 | 55 | xxx |
| 2 | 10 | xxx |
| 2 | 7 | xxx |
5 Replies
generally you are on the right track. Let me engage in some whataboutism.
what if the logout event is missing?
what if you have two consecutive login events?
what if the logout event comes before he login event?
Do you have plans how to handle these situations?
Thanks for the reply,
I have had these issues in my head the whole time. Assigning a login or logout to each log with an "every other in chronological order" type of way is just way too prone to errors. One thing goes wrong and the whole column is bad. I also have some validation issues with this method. I could do a bunch of logins and logouts myself, but that sample size is just too small when I am working with a couple years of data. There is simply no way to tell if the output is accurate.
I don't want to accept defeat because this data would be very useful, but the fact that the logs don't discern what event took place kind of puts me and my skillset in a bind.
Do you have any other ideas that could help, save from asking the vendor to overhaul the entire software license manager?
I just thought of something else. Can't believe I didn't think of this before.
I do have a column that contains the total amount of logins for whatever software the line references as well as the max available logins. I could probably use that info to help with assigning what even took place (login or logout).
Any ideas for what functions can help me do that?
Anonymous , You have to do something like this.
Assumptions are
1. Work finish with a date
2. User login once on one software in a day. (Not two login in a day)
Create a new new date column
date = [Date-Timestamp].date
Duration in minute
login time = datediff(maxx(filter(table,[User]=earlier([User]) && [Date] = earlier([Date]) && [Software] = earlier([Software ]) ),[Date-Timestamp]),[Date-Timestamp] ,minute)For other scenarios
Can you share sample data and sample output in a table format?
