Determining User Last Login Date

Question

Hi, 
 I am managing an Appian app across multiple environments and need to monitor licensing usage. I need to be able to identify users who have not logged in the past 90 days and need to do this analysis by user group. However, I cannot find any 'Last Login Date' field anywhere. 
 Interested to know how other people have approached this. One very manual solution would be to process the audit log file daily to compute and maintain manually a 'Last Login Date' field, but I am hoping to find a more elegant/sustainable solution. 
 Additionally, the audit log file does not indicate the user group(s). How can I include / Where can I find this information? 
 Thanks for your suggestion. Much appreciated!

ajhick · Answer

Hi Barthe, 
 The below code does required the Log Reader plugin but it produces a list of logins for the login files. I don't know if this is the best way but this is what I've been using. I do a fair amount of transformation on this data but this is the core. 
 https://community.appian.com/b/appmarket/posts/log-reader 
 a!localVariables(
 local!loginAuditDaysToRetrieve: 90,
 local!numberOfDaysToRetrieve: 1 + enumerate(local!loginAuditDaysToRetrieve),
 local!datesToRetrieve: a!forEach(
 items: local!numberOfDaysToRetrieve,
 expression: text(
 todate(local(now())) - fv!index,
 "yyyy-mm-dd"
 )
 ),
 local!filesToRetrieve: a!forEach(
 items: local!datesToRetrieve,
 expression: concat(
 "login-audit.csv.",
 fv!item
 )
 ),
 local!validFilesToRetrieve: a!forEach(
 items: local!filesToRetrieve,
 expression: if(
 todate(
 index(
 getloginfo(
 logPath: fv!item
 ),
 "lastModified",
 {}
 )
 ) > date(2000,1,1),
 fv!item,
 {}
 )
 ),
 local!loginAuditAsText: reject(
 fn!isnull,
 a!flatten(
 a!forEach(
 items: local!validFilesToRetrieve,
 expression: a!localVariables(
 local!loginAuditFromCsv: readcsvlog(
 csvPath: fv!item
 ),
 local!headers: index(
 local!loginAuditFromCsv,
 "headers",
 {}
 ),
 local!rows: index(
 local!loginAuditFromCsv,
 "rows",
 {}
 ),
 {
 joinarray(local!headers,","),
 local!rows
 }
 )
 )
 )
 ),
 local!loginAudit: a!forEach(
 items: local!loginAuditAsText,
 expression: a!localVariables(
 local!loginAuditArray: split(fv!item, ","),
 local!rawLoggedIn: index(
 local!loginAuditArray,
 1,
 {}
 ),
 local!rawLoggedInSplit: split(
 local!rawLoggedIn,
 " "
 ),
 local!rawLoggedInDate: index(
 local!rawLoggedInSplit,
 1,
 {}
 ),
 local!convertedLoggedInDate: joinarray(
 reverse(
 split(
 local!rawLoggedInDate,
 "-"
 )
 ),
 "/"
 ),
 local!rawLoggedInTime: split(
 index(
 local!rawLoggedInSplit,
 2,
 {}
 ),
 ":"
 ),
 local!convertedLoggedInTime: time(
 index(
 local!rawLoggedInTime,
 1,
 {}
 ),
 index(
 local!rawLoggedInTime,
 2,
 {}
 ),
 index(
 local!rawLoggedInTime,
 3,
 {}
 )
 ),
 local!finalLoggedIn: local!convertedLoggedInDate + local!convertedLoggedInTime,
 {
 username: index(
 local!loginAuditArray,
 2,
 {}
 ),
 loggedIn: local!finalLoggedIn,
 outcome: index(
 local!loginAuditArray,
 3,
 {}
 )
 }
 )
 ),
 reject(
 fn!isnull,
 union(
 local!loginAudit,
 local!loginAudit
 )
 )
)