I am trying to reference only Active accounts in Service Desk as a part of a project I am doing to create users via the web services. I am trying to leverage the "Inactive" column in the ca_contact table for some reporting. Going through the available objects see that the Contact Extended has Contact Extended Filters which reference this as "Contact Status". How do I make either that filter a regular object that I can use in the reporting and select only Active Users? I didn't see it in the schema designer despite it being in an existing table in MDB. From the data dictionary I see it references the boolean table but don't see it anywhere to add to the Universe in the Universe Design Tool. I was hesitant to create a new column knowing that it exists already.
0 = Active 1 = Inactive
0 = Active
1 = Inactive
So you can add the relevant 'where clause' in your query to filter out the inactive records (1) and only return the active (0) records.
My apologies Brian, it is an automated report that is run at 5am and the filters are user prompt, preventing it from running automatically. I tried to find another object that would pull all data to put the Where clause in but there are none available which made me look in the database Universe Design Tool to see how its pulled and to modify the object to make it not a filter.
I still don't get why you would want an automated report, but design the report with user prompt? Also you mentioned creating users via web services. How does all this fit together?
The current report I use does not use the filter, however, I need the data in the report. I had to automate it because it has to be as current as possible. The report is ran at 5am everyday as a .csv. The file is saved to an SFG mailbox and then the file is picked up by our provisioning application First Access. First Access uses this report to identify all users' existing accesses (groups, roles, etc.) and then take information from requests by users from First Access to add and/or modifications accounts. First Access will use web services to modify these accounts.
The issue is that the filter is the only reference (from what I could see) to the Inactive column in the mdb. To make the report the most useful, I need to be able to identify if users are Active or Inactive. I didn't see the Inactive column in the ca_contact table referenced anywhere in the schema designer, Universe Design Tool, etc. I didn't want to make a new field (column) when there is an existing column that provides the information I need (did SQL queries on the database to confirm).
As you're hitting the Object Layer of SDM, on the SDM contact table, cnt, its the delete_flag attribute that contains the user's active/inactive status.
select userid,delete_flag from cnt;
if its a 1, that means the record is inactive, and a 0 indicates an active record
Hope this helps.
That is it! Thank you very much!
Field Level Reference Mapping for ca_contact - CA Service Management - 17.0 - CA Technologies Documentation