Footprints 12 How to extract all users details from the database ?

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    FootPrints


    COMPONENT:

    FootPrints


    APPLIES TO:

    Footprints 12.1.X all versions



    QUESTION:

    I want to report on users, agents and customer, and I need to query the database to view all agents, customers and their assigned containers and workspaces.
    Extract all users details from the Footprints 12 database.
    Display all users and containers with roles.


    ANSWER:

                            select a.app_user_id as "app_user_id",b.user_prof_id as "user_prof_id",
                            case when c.string_val is null then '' else a.user_login end as "User ID",
                            case when c.string_val is null then '' else c.string_val end as "User Name",
                            case when c.string_val is null then '' else a.pwd end as "Password",
                            case when e.int_val is null and c.string_val is not null then 'Customer' when e.int_val is not null and c.string_val is not null then 'Agent' else '' end as "User Type",
                            case when x.string_val is null then '||||SYSTEM||||' else x.string_val end as "Containers",
                            v.app_role_name as "System/Container Role",
                            case when ab.string_val is null then '' else ab.string_val end as "Supervisor User Name",
                            case when d.long_val = 1 then 'FootPrints' when d.long_val = 2 then 'Web Server' when d.long_val is null then '' else 'LDAP' end as "Authentication Type",
                            case when e.int_val = 0 then 'Concurrent' when e.int_val = 1 then 'Fixed' else '' end as "Licence Type",
                            case when f.string_val is null then '' else f.string_val end as "Language",
                            case when g.string_val is null then '' else g.string_val end as "Date Format",
                            case when h.string_val is null then '' else h.string_val end as "Time Zone",
                            case when i.string_val is null then '' else i.string_val end as "Primary Email",case when j.boolean_val = 1 then 'TRUE' else '' end as "Primary Email Notify",
                            case when k.string_val is null then '' else k.string_val end as "Second Email",case when k.boolean_val = 1 then 'TRUE' else '' end as "Second Email Notify",
                            case when m.string_val is null then '' else m.string_val end as "Third Email",case when n.boolean_val = 1 then 'TRUE' else '' end as "Third Email Notify",
                            o.boolean_val as "Use Mobile Tool",
                            p.boolean_val as "Change approver",
                            q.boolean_val as "Access to CMDB",
                            case when r.string_val is null then '' else r.string_val end as "BCM User Name",
                            case when s.string_val is null then '' else s.string_val end as "BCM Password",
                            t.int_val as "Items Per Page"
                            from fpscdb001_system.app_user a
                            left join fpscdb001_system.app_user_prof_link b on a.app_user_id = b.app_user_id --User Profile
                            left join fpscdb001_system.cfg_setting_val c on b.user_prof_id = c.owner_id and c.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='username') --User Name
                            left join fpscdb001_system.cfg_setting_val d on b.user_prof_id = d.owner_id and d.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='userAuthenticationMethodId') --Authentication Type
                            left join fpscdb001_system.cfg_setting_val e on b.user_prof_id = e.owner_id and e.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='licenceType') --Licence Type
                            left join fpscdb001_system.cfg_setting_val f on b.user_prof_id = f.owner_id and f.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='language') --Language
                            left join fpscdb001_system.cfg_setting_val g on b.user_prof_id = g.owner_id and g.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='dateFormat') --Date Format
                            left join fpscdb001_system.cfg_setting_val h on b.user_prof_id = h.owner_id and h.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='timezone') --Time Zone
                            left join fpscdb001_system.cfg_setting_val i on b.user_prof_id = i.owner_id and i.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='primaryEmail') --Primary Email
                            left join fpscdb001_system.cfg_setting_val j on b.user_prof_id = j.owner_id and j.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='primaryEmailNotify') --Primary Email Notify
                            left join fpscdb001_system.cfg_setting_val k on b.user_prof_id = k.owner_id and k.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='secondEmail') --Second Email
                            left join fpscdb001_system.cfg_setting_val l on b.user_prof_id = l.owner_id and l.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='secondEmailNotify') --Second Email Notify
                            left join fpscdb001_system.cfg_setting_val m on b.user_prof_id = m.owner_id and m.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='thirdEmail') --Third Email
                            left join fpscdb001_system.cfg_setting_val n on b.user_prof_id = n.owner_id and n.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='thirdEmailNotify') --Third Email Notify
                            left join fpscdb001_system.cfg_setting_val o on b.user_prof_id = o.owner_id and o.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='License_UseMobileAllowed') --Use mobile tool
                            left join fpscdb001_system.cfg_setting_val p on b.user_prof_id = p.owner_id and p.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='License_ChangeApprover') --Change approver
                            left join fpscdb001_system.cfg_setting_val q on b.user_prof_id = q.owner_id and q.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='License_CMDB') --Access to CMDB
                            left join fpscdb001_system.cfg_setting_val r on b.user_prof_id = r.owner_id and r.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='assetCoreUser') --BCM User Name
                            left join fpscdb001_system.cfg_setting_val s on b.user_prof_id = s.owner_id and s.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='assetCorePassword') --BCM Password
                            left join fpscdb001_system.cfg_setting_val t on b.user_prof_id = t.owner_id and t.cfg_setting_id in
                            (select cfg_setting_id from fpscdb001_system.cfg_setting where cfg_setting_const='itemsPerPage') --Items Per Page
                            left join fpscdb001_system.user_prof u on b.user_prof_id = u.user_prof_id
                            left join fpscdb001_system.app_role v on u.app_role_id = v.app_role_id --System Role
                            left join fpscdb001_system.defn_ver w on u.defn_id = w.defn_id and w.defn_ver_status_id = 0 --This line and one below for containers
                            left join fpscdb001_system.cfg_setting_val x on w.defn_ver_id = x.owner_id and x.cfg_setting_id = 80 -- Container Name
                            left join fpscdb001_system.cfg_setting_val y on b.user_prof_id = y.owner_id and y.cfg_setting_id = 456 --Supervisor App User ID
                            left join fpscdb001_system.app_user z on y.long_val = z.app_user_id --Supervisor User ID
                            outer apply (select top 1 aa.user_prof_id from fpscdb001_system.app_user_prof_link aa where aa.app_user_id = z.app_user_id) aa2
                            left join fpscdb001_system.cfg_setting_val ab on aa2.user_prof_id = ab.owner_id and ab.cfg_setting_id = 462 --Supervisor User Name
                            where a.delete_date is null
                            order by app_user_id,user_prof_id


    Article Number:

    000143851


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles