Determining User Last Login Date

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!

  Discussion posts and replies are publicly visible

Parents
  • 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
        )
      )
    )

  • Hi,

    We have installed the Log Reader Plugin and used your code.
    In some cases some lines are returned without the entire log date.

    I already checked the entire code but i couldn't find the root cause for the issue.

    Do you have any suggestion on how to fix this?

    Thank you!

Reply Children
  • Have you looked at the raw output in the Expression Rule designer? Just wondering if Appian is rendering the time when the date happens to be today...if the raw data is the same then it must be the Log Reader doing the same...in which case you can infer that the date is today if you only have the time 

  • I'm not having this issue. If you're using the same local variable names as me can you check your raw output from one of the valid files to retrieve? See the below code and look in the "rows" index at the left hand side (before the first comma) to check the format of the dates that are in your login audit files.

    Preferably make it so you can look at one of the entries where it's only giving the time!

    a!localVariables(
      local!numberOfDaysToRetrieve: 1 + enumerate(90),
      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,
          {}
        )
      )[1], /*Manipulate this index to try and get an instance where only a time is showing*/
      local!loginAuditAsText: reject(
        fn!isnull,
        a!flatten(
          a!forEach(
            items: local!validFilesToRetrieve,
            expression: a!localVariables(
              local!loginAuditFromCsv: readcsvlog(
                csvPath: fv!item
              ),
              local!loginAuditFromCsv
            )
          )
        )
      ),
      local!loginAuditAsText
    )