Authlog

view: authlog {
  sql_table_name: public.authlog ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: akey {
    sql: ${TABLE}.akey ;;
  }

  dimension: ator_ip {
    sql: ${TABLE}.ator_ip ;;
  }

  dimension: ator_first_octet {
    sql: SPLIT_PART(${ator_ip}, '.',1) ;;
  }

  dimension: ator_ip_v4bin {
    type: number
    sql: ${TABLE}.ator_ip_v4bin ;;
  }

  dimension: ator_ip_v6bin {
    type: number
    hidden: yes
    sql: ${TABLE}.ator_ip_v6bin ;;
  }

  dimension: enroll {
    type: yesno
    sql: ${TABLE}.enroll ;;
  }

  dimension: factor {
    sql: ${TABLE}.factor ;;
  }

  dimension: is_human_factor {
    type: yesno
    sql: ${factor} IN ('App Push','Passcode','Phone Call','SMS Passcode','U2F Token') ;;
  }

  dimension: flash_version {
    sql: ${TABLE}.flash_version ;;
  }

  dimension: condensed_flash_version {
    sql: CASE WHEN ${flash_version} IN
  (SELECT * FROM
    (
      SELECT
        authlog.flash_version AS flash_version
      FROM public.authlog AS authlog
      WHERE {% condition authlog.ts_date %} authlog.ts {% endcondition %}
      AND flash_version <> ''
      GROUP BY 1
      ORDER BY COUNT(*) DESC
      LIMIT 10
    ) sub
  )
THEN ${flash_version}
ELSE 'Other'
END ;;
  }

  dimension: has_flash {
    type: yesno
    sql: ${flash_version} != '' AND ${flash_version} != 'uninstalled' ;;
  }

  dimension: iname {
    sql: ${TABLE}.iname ;;
  }

  dimension: ipaddr {
    sql: ${TABLE}.ipaddr ;;
  }

  dimension: ipaddr_v4bin {
    type: number
    sql: ${TABLE}.ipaddr_v4bin ;;
  }

  dimension: ipaddr_v6bin {
    type: number
    sql: ${TABLE}.ipaddr_v6bin ;;
  }

  dimension: java_version {
    sql: ${TABLE}.java_version ;;
  }

  dimension: has_java {
    type: yesno
    sql: ${java_version} != '' AND ${java_version} != 'uninstalled' ;;
  }

  dimension: result {
    sql: ${TABLE}.result ;;
  }

  dimension: uname {
    sql: ${TABLE}.uname ;;
  }

  dimension_group: ts {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.ts ;;
  }

  measure: count {
    type: count
    drill_fields: [id, iname, uname]
  }

  measure: column_percent {
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: count_unique_users {
    type: count_distinct
    sql: ${uname} ;;
  }

  measure: percent_of_unique_users {
    type: percent_of_total
    sql: ${count_unique_users} ;;
  }
}

- view: authlog
  sql_table_name: public.authlog
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: akey
    sql: ${TABLE}.akey
  - dimension: ator_ip
    sql: ${TABLE}.ator_ip
  - dimension: ator_first_octet
    sql: SPLIT_PART(${ator_ip}, '.',1)
  - dimension: ator_ip_v4bin
    type: number
    sql: ${TABLE}.ator_ip_v4bin
  - dimension: ator_ip_v6bin
    type: number
    hidden: true
    sql: ${TABLE}.ator_ip_v6bin
  - dimension: enroll
    type: yesno
    sql: ${TABLE}.enroll
  - dimension: factor
    sql: ${TABLE}.factor
  - dimension: is_human_factor
    type: yesno
    sql: ${factor} IN ('App Push','Passcode','Phone Call','SMS Passcode','U2F Token')
  - dimension: flash_version
    sql: ${TABLE}.flash_version
  - dimension: condensed_flash_version
    sql: CASE WHEN ${flash_version} IN
  (SELECT * FROM
    (
      SELECT
        authlog.flash_version AS flash_version
      FROM public.authlog AS authlog
      WHERE {% condition authlog.ts_date %} authlog.ts {% endcondition %}
      AND flash_version <> ''
      GROUP BY 1
      ORDER BY COUNT(*) DESC
      LIMIT 10
    ) sub
  )
THEN ${flash_version}
ELSE 'Other'
END
  - dimension: has_flash
    type: yesno
    sql: ${flash_version} != '' AND ${flash_version} != 'uninstalled'
  - dimension: iname
    sql: ${TABLE}.iname
  - dimension: ipaddr
    sql: ${TABLE}.ipaddr
  - dimension: ipaddr_v4bin
    type: number
    sql: ${TABLE}.ipaddr_v4bin
  - dimension: ipaddr_v6bin
    type: number
    sql: ${TABLE}.ipaddr_v6bin
  - dimension: java_version
    sql: ${TABLE}.java_version
  - dimension: has_java
    type: yesno
    sql: ${java_version} != '' AND ${java_version} != 'uninstalled'
  - dimension: result
    sql: ${TABLE}.result
  - dimension: uname
    sql: ${TABLE}.uname
  - dimension_group: ts
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.ts
  - measure: count
    type: count
    drill_fields:
    - id
    - iname
    - uname
  - measure: column_percent
    type: percent_of_total
    sql: ${count}
  - measure: count_unique_users
    type: count_distinct
    sql: ${uname}
  - measure: percent_of_unique_users
    type: percent_of_total
    sql: ${count_unique_users}