New tool: Login Audit Log GUI (Appian interface)

Hey all!

I'm sharing the Appian interface I recently built to give better visibility into the system's recent internal Login Audit files (instead of constantly downloading and opening CSV files) - particularly helpful if you're an O&M user and/or diagnosing login issues for any other reason.

This interface relies on the Log Reader plug-in (and also uses the Date and Time Utilites plug-in for a certain transform on timestamps).  That said, I can't say how it'll perform on multi-server environments, since I don't have one to try it on.

The entire interface is self-contained, with the sole exception of a custom rule I wrote that utilizes the internal "User" record to take an entered username and get the system's properly-cased username (since there is some inconsistent case sensitivity around usernames and there's no other way I could come up with to tell what the 'real' version is).  I'll include that expression rule code after the main interface code.

a!localVariables(
  local!relevantDay: today(),
  local!isViewingToday: local!relevantDay = today(),

  local!dayString: if(
    local!isViewingToday,
    "",
    "." & text(local!relevantDay, "yyyy-mm-dd")
  ),
  local!refreshCounter: 0,
  
  /* use this (optionally) to filter out a service account that might get a disproportionate number of hits per day.  if none, set it to a nonsense string. */
  local!usernameToFilterOut: "api.test.account.name.asdf",

  local!currentLog: a!refreshVariable(
    value: readcsvlogpagingwithheaders(
      csvPath: "login-audit.csv" & local!dayString,
      startIndex: 1,
      batchSize: 1000,
      headers: {"timestamp", "username", "success", "ip", "mode", "agent", "uuid", "secondaryUuid"},
      filterColumName: "username",
      filterOperator: "!=",
      filterValue: local!usernameToFilterOut
    ),
    refreshOnVarChange: {local!refreshCounter}
  ),
  local!backgroundAutoRefreshedTotalCount: a!refreshVariable(
    value: if(
      local!isViewingToday,
      readcsvlogpagingwithheaders(
        csvPath: "login-audit.csv", startIndex: 1, batchSize: 1,
        headers: {"timestamp", "username", "success", "ip", "mode", "agent", "uuid", "something else"},
        filterColumName: "username", filterOperator: "!=", filterValue: local!usernameToFilterOut
      ).totalCount,
      null()
    ),
    refreshInterval: 0.5
  ),
  local!isTotalCountStale: and(
    local!isViewingToday,
    local!backgroundAutoRefreshedTotalCount <> local!currentLog.totalCount
  ),

  /* using brute force a bit here to tell if any logs exist for any of the past 4 days */
  local!hasPreviousDay: or(
    readcsvlog( csvPath: "login-audit.csv." & text(local!relevantDay-1, "yyyy-mm-dd") ).totalCount > -1,
    readcsvlog( csvPath: "login-audit.csv." & text(local!relevantDay-2, "yyyy-mm-dd") ).totalCount > -1,
    readcsvlog( csvPath: "login-audit.csv." & text(local!relevantDay-3, "yyyy-mm-dd") ).totalCount > -1,
    readcsvlog( csvPath: "login-audit.csv." & text(local!relevantDay-4, "yyyy-mm-dd") ).totalCount > -1
  ),

  local!powerBiCounter: a!refreshVariable(
    value: readcsvlogpagingwithheaders(
      csvPath: "login-audit.csv" & local!dayString,
      startIndex: 1,
      batchSize: 1,
      headers: {"timestamp", "username", "success", "ip", "mode", "agent", "uuid", "something else"},
      filterColumName: "username",
      filterOperator: "=",
      filterValue: local!usernameToFilterOut
    ).totalCount,
    refreshOnVarChange: {local!refreshCounter}
  ),

  local!splitVals: a!forEach(
    local!currentLog.rows,
    split(fv!item, ",")
  ),

  local!values: a!forEach(
    local!splitVals,

    a!localVariables(
      local!usernameAttempted: fv!item[2],  /* represents what the user typed in the login screen - causes complications... */
      local!username: lower(local!usernameAttempted),

      a!map(
        username: local!username,
        usernameAttempted: local!usernameAttempted,
        gmtTimestampString: fv!item[1],
        convertedTimeValue: local(gmt(parsedate(fv!item[1]))),  /* parseDate() is from "date and time utilities" plug-in */
        success: fv!item[3],
        ipAddress: fv!item[4],
        mode: fv!item[5]
      )
    )
  ),
  local!filteredUsernames: {"internal.service.account2", "service.account.3"},  /* list any other API / service accounts here (if any) when they may have a lot of routine hits not relevant for your use of this tool */

  local!filteredValues: a!forEach(
    local!values,
    if(contains(local!filteredUsernames, fv!item.username), {}, fv!item)
  ),

  local!usernameFilter: null(),
  local!successFilter: "either",

  local!filteringActive: or(
    a!isNotNullOrEmpty(local!usernameFilter),
    local!successFilter <> "either"
  ),

  local!reFilteredValues: if(
    not(local!filteringActive),
    local!filteredValues,

    a!flatten(a!forEach(
      local!filteredValues,
      if(
        and(
          or(
            a!isNullOrEmpty(local!usernameFilter),
            find(local!usernameFilter, fv!item.username) > 0
          ),
          or(
            local!successFilter = "either",
            and(
              local!successFilter = "success",
              fv!item.success = "Succeeded"
            ),
            and(
              local!successFilter = "fail",
              fv!item.success = "Failed"
            )
          )
        ),
        fv!item,
        {}
      )
    ))
  ),

  local!pagingInfo: a!pagingInfo(1, 20, a!sortInfo(field: "convertedTimeValue", ascending: false())),

  local!dataSubset: todatasubset(
    arrayToPage: local!reFilteredValues,
    pagingConfiguration: local!pagingInfo
  ),


  /* bending over backwards here to do the (somewhat costly) username-status-checking query (hitting query record type for user record for each line-item) just for the current page, post-filtering, etc. */
  local!updatedDataSubset: a!update(
    local!dataSubset,
    "data",
    a!forEach(
      local!dataSubset.data,
      a!update(
        data: fv!item,
        index: {"actualUsername", "usernameExists", "accountActive"},
        value: {
          a!localVariables(
            local!currentUsername: fv!item.username,
            local!currentUserStatus: rule!MIKE_UTIL_getCaseSensitiveUsernameAccountInfo(username: local!currentUsername),
            local!isActive: and(local!currentUserStatus.exists, a!defaultValue(local!currentUserStatus.isActive, false())),
            {
              local!currentUserStatus.username,
              local!currentUserStatus.exists,
              local!isActive
            }
          )
        }
      )
    )
  ),
  
  local!positiveColor: "#28724F", /* darker green */
  local!warningColor: "#E29262", /* warning color */
  local!negativeColor: "#BA0C2F", /* darker red */
  local!inactiveColor: "#bcbdc0", /* lighter grey */


  a!sectionLayout(
    label: "Login Audit Log Reader",
    contents: {
      a!columnsLayout(
        columns: {
          a!columnLayout(
            contents: {
              a!textField(
                labelPosition: "COLLAPSED",
                placeholder: "(filter by username)",
                value: local!usernameFilter,
                saveInto: local!usernameFilter,
                refreshAfter: "KEYPRESS"
              ),
              a!richTextDisplayField(
                labelPosition: "COLLAPSED",
                value: {
                  a!richTextItem(
                    text: {
                      a!richTextIcon(
                        icon: "refresh",
                        caption: if(local!isTotalCountStale, "Refresh Needed (new info found)", "Refresh"),
                        link: a!dynamicLink(
                          saveInto: {
                            a!save(local!refreshCounter, local!refreshCounter + 1)
                          },
                          showWhen: local!isViewingToday
                        ),
                        linkStyle: "STANDALONE",
                        showWhen: local!isViewingToday,
                        color: if(local!isTotalCountStale, local!warningColor, "ACCENT"),
                        size: if(local!isTotalCountStale, "LARGE", null())
                      ),
                      a!richTextIcon(
                        icon: "refresh",
                        caption: "Currently viewing a previous day's log file, so 'Refresh' is not really relevant here.",
                        showWhen: not(local!isViewingToday),
                        color: local!inactiveColor,
                        size: "SMALL"
                      ),
                      a!richTextItem(
                        text: {
                          "           ",
                          a!richTextIcon(
                            icon: "remove",
                            caption: "Reset Filter(s)",
                            link: {
                              a!dynamicLink(
                                saveInto: {
                                  a!save(local!usernameFilter, ""),
                                  a!save(local!successFilter, "either"),
                                  a!save(local!pagingInfo.startIndex, 1),
                                  a!save(local!refreshCounter, local!refreshCounter + 1)
                                },
                                showWhen: local!filteringActive
                              )
                            },
                            linkStyle: "STANDALONE",
                            color: if(
                              local!filteringActive,
                              "NEGATIVE",
                              local!inactiveColor
                            ),
                            size: "MEDIUM_PLUS"
                          )
                        }
                      )
                    }
                  )
                }
              )
            },
            width: "9X"
          ),
          a!columnLayout(
            contents: {

              a!radioButtonField(
                choiceLabels: {"Success", "Failure", "Both"},
                choiceValues: {"success", "fail", "either"},
                labelPosition: "COLLAPSED",
                value: local!successFilter,
                saveInto: local!successFilter,
                choiceLayout: "COMPACT",
                marginBelow: "MORE"
              ),
              
              a!richTextDisplayField(
                labelPosition: "COLLAPSED",
                value: {
                  a!richTextItem(
                    text: {
                      a!richTextIcon(
                        icon: "backward",
                        caption: "Back 1 Day",
                        color: if(
                          local!hasPreviousDay,
                          "",
                          local!inactiveColor
                        )
                      )
                    },
                    link: a!dynamicLink(
                      saveInto: {
                        a!save(local!relevantDay, local!relevantDay - 1),
                        a!save(local!pagingInfo.startIndex, 1)
                      },
                      showWhen: local!hasPreviousDay
                    ),
                    linkStyle: "STANDALONE"
                  ),
                  "  ",
                  a!richTextItem(
                    text: text(local!relevantDay, "yyyy-mm-dd")
                  ),

                  "  ",
                  a!richTextItem(
                    text: {
                      a!richTextIcon(
                        icon: "forward",
                        caption: "Forward 1 Day",
                        color: if(local!isViewingToday, local!inactiveColor, "")
                      )
                    },
                    link: a!dynamicLink(
                      saveInto: {
                        a!save(local!relevantDay, local!relevantDay + 1),
                        a!save(local!pagingInfo.startIndex, 1)
                      },
                      showWhen: not(local!isViewingToday)
                    ),
                    linkStyle: "STANDALONE"
                  ),
                  " ",
                  a!richTextItem(
                    text: {
                      a!richTextIcon(
                        icon: "fast-forward",
                        caption: "Jump to Today"
                      )
                    },
                    link: a!dynamicLink(
                      saveInto: {
                        a!save(local!relevantDay, today()),
                        a!save(local!pagingInfo.startIndex, 1)
                      }
                    ),
                    linkStyle: "STANDALONE",
                    showWhen: local!relevantDay < today()-1
                  )
                },
                marginAbove: "LESS"
              )
            },
            width: "4X"
          )
        }
      ),

      a!gridField(
        data: local!updatedDataSubset,
        columns: {
          a!gridColumn(
            label: "Username",
            sortField: "username",
            value: a!richTextDisplayField(
              value: {
                a!richTextItem(
                  text: {
                    fv!row.username,
                    " ",
                    a!richTextIcon(
                      icon: "check-circle-o",
                      caption: "Account Active",
                      showWhen: and(fv!row.usernameExists, fv!row.accountActive),
                      color: local!positiveColor,
                      size: "SMALL"
                    ),
                    a!richTextIcon(
                      icon: "remove",
                      caption: "Account Deactivated",
                      showWhen: and(fv!row.usernameExists, not(a!defaultValue(fv!row.accountActive, true()))),
                      color: local!warningColor,
                      size: "STANDARD"
                    ),
                    a!richTextIcon(
                      icon: "question-circle-o",
                      caption: "Unknown Username",
                      showWhen: not(a!defaultValue(fv!row.usernameExists, false())),
                      color: local!negativeColor,
                      size: "SMALL"
                    )
                  }
                )
              }
            )
          ),
          a!gridColumn(
            label: "Timestamp",
            sortField: "convertedTimeValue",
            value: a!richTextDisplayField(
              value: {
                a!richTextItem(
                  text: text(fv!row.convertedTimeValue, "yyyy-mm-dd HH:mm:ss")
                ),
                char(10),
                a!richTextItem(
                  text: "(GMT:  " & fv!row.gmtTimestampString & ")",
                  color: "SECONDARY",
                  size: "SMALL",
                  style: "EMPHASIS"
                )
              }
            )
          ),
          a!gridColumn(
            label: "Success",
            value: fv!row.success
          ),
          a!gridColumn(
            label: "Mode",
            value: fv!row.mode
          ),
          a!gridColumn(
            label: "IP",
            value: fv!row.ipAddress
          )
        },
        pagingSaveInto: local!pagingInfo,
        borderStyle: "STANDARD",
        shadeAlternateRows: true()
      ),

      a!richTextDisplayField(
        label: "PowerBI hits filtered out:",
        labelPosition: "ADJACENT",
        value: local!powerBiCounter
      )
    }
  )
/* by: Mike Schmitt */
)

and here's the "MIKE_UTIL_getCaseSensitiveUsernameAccountInfo()" code:

a!localVariables(
  local!username: a!defaultValue(ri!username, "-=-=-"),
  local!userQuery: a!refreshVariable(
    refreshAlways: true(),
    value: a!queryRecordType(
      recordType: 'recordType!{SYSTEM_RECORD_TYPE_USER}User',
      pagingInfo: a!pagingInfo(1, 1),
      fetchTotalCount: true(),
      fields: {'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username', 'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_active}active', 'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_uuid}uuid'},
      filters: a!queryLogicalExpression(
        operator: "OR",
        filters: {
          /* doing this to rule out lowercase username first, as it's the normal way we try to initialize them */
          a!queryFilter(
            field: 'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username',
            operator: "=",
            value: lower(local!username)
          ),
          a!queryFilter(
            field: 'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username',
            operator: "=",
            value: local!username
          )
        },
        logicalExpressions: {
          a!queryLogicalExpression(
            operator: "AND",
    
            /* these will hopefully require the username to match exactly except for casing */
            filters: {
              a!queryFilter(
                field: 'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username',
                operator: "starts with",
                value: local!username
              ),
              a!queryFilter(
                field: 'recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username',
                operator: "ends with",
                value: local!username
              )
            }
          )
        }
      )
    )
  ),
  
  if(
    local!userQuery.totalCount = 0,
    a!map(
      exists: false()
    ),
    a!map(
      exists: true(),
      username: tostring(local!userQuery.data['recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_username}username']),
      uuid: local!userQuery.data[1]['recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_uuid}uuid'],
      isActive: local!userQuery.data[1]['recordType!{SYSTEM_RECORD_TYPE_USER}User.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_active}active']
    )
  )
)

If there are any questions or suggestions, please let me know!