How to populate a Custom 'Last Logged in User' Field using the JAMF Service Graph Connector

 

JAMF Service Graph connector Version : 2.15

 

This Article describes how to populate a Custom  Last Logged in User  Reference Field in the  Computer CI  Class using the  JAMF Service Graph Connector  by f o llowing the below steps:

 

1. Create a JAMF Extension Attribute(EA) for capturing  Last logged in User  in the J AMF Backend
2. Ensure that the  SG-JAMF Computers Transformer Definition  is associated with the Active  SG-JAMF Computers  Data Source
3. Ensure that a Valid  SG-JAMF Computers Import Set  is selected in the the  IntegrationHub ETL  Tool
4. Create a new  Extension Attributes JSON At Hardware Level   Transform  in the  Nested Hardware Child Object  using the  IntegrationHub ETL  Tool
5. Create a new  Last Logged in User Transform  in the  Nested Hardware Child Object  using the  IntegrationHub ETL  Tool
6. Create a new  Last Logged in User Sys ID   Transform  in the  Nested Hardware Child Object  using the  IntegrationHub ETL  Tool
7. Map the new  Last Logged in User Sys ID Transform Output  Field to your  Custom Last Logged in User  Reference Field in the  Target Computer CI  using the  IntegrationHub ETL  Tool

 

JAMF Backend

 

1. Create a* JAMF Extension Attribute(EA) for capturing * Last logged in User**  in the JAMF Backend

 

Create a* JAMF Extension Attribute(EA) for capturing * Last logged in User**  in the  JAMF  Backend. (Please refer to this Last Logged in User Extension Attribute Community Blog for a sample BASH Script for creating this  Last logged in User  EA in the JAMF Backend)

 

An e.g.  Last User  Extension Attribute will be added to the List of  Extension Attributes  that are included in the  SG-JAMF Computers  Source Feed.  The below  IntegrationHub ETL  Screenshot shows 1 of the  SG-JAMF Objects  (object[1]) that are ingested by the  JAMF Service Graph Connector  via the  SG-JAMF Computers Scheduled Import  Job. The Nested  extension_attributes  Child Object in the Parent object[1] Object  is expanded to show the List of  Extension Attributes  (10 in total) that is included in this Nested  extension_attributes  Child Object. An e.g.  Last User  Extension Attribute will be included in this List of  Extension Attributes  (To bring the Number of  Extension Attributes  to be 11 in total) when a JAMF Extension Attribute(EA) for capturing  Last logged in User  in the  JAMF  Backend is enabled.

 

 

These  Extension Attributes  are also included in a single  extension_attributes_blob  field in the  Parent  Object where all  Extension Attributes  are flattened out into a single String Value.  The screenshot below shows this single  extension_attributes_blob  field in the Parent object (as well as a neighboring  extension_attributes_json  field that contains the same content without the "\"'s included in the String).

 

 

There is a limitation with the  IntegrationHub ETL  Tool where, in the  3. Map Data to CMDB and Add Relationships  IH-ETL Step, it is not possible to Map from a  Transform  Field in the  Parent Object  Level to a  Target Field  in a  Nested Child Object  like e.g.  Hardware.

 

To get around this limitation, we will be using a  Copy Transform  to Copy the Contents of the  extension_attributes_json  field at the  Parent  Level to a new  extension_attributes_json_at_hardware_level  field in the  Nested Child Hardware Object  Level. We will then extract the  Last Logged in User  data from  this   extension_attributes_json_at_hardware_level  field to a new  Last Logged in User Field in the  Nested Child Hardware Object Level.

 

Note: With Prior Versions of the  JAMF Service Graph Connector , i.e. Versions 2.14 & below, there was a limitation with not being able to use the Copy Transform to Copy the Contents of a field at the Parent Level to a Target Field in a Nested Child Object like e.g. Hardware. This necessitated alternative work around steps to compensate for this as outlined in the Populate Custom 'Last Logged in User' Field with JAMF Service Graph Connector Version 2.14 & below Article. This limitation does not exist with the newer Version 2.15

 

1. Ensure that the  SG-JAMF Computers Transformer Definition  is associated with the Active  SG-JAMF Computers  Data Source

 

The CMDB Integration Studio Application Data Sources[cmdb_inst_application_feed] Table contains all  Service Graph Connector Transformer Definition  Records. The  IntegrationHub ETL  Tool obtains  Transformer Definition  Metadata from the  Transformer Definition  Records in this Table. Each  Transformer Definition  Record in this table is associated with a  Data Source. The  SG-JAMF Computers Transformer Definition  Record in this table is associated with the  OOTB SG-JAMF Computers Data Source  by Default.

 

This step involves checking the  SG-JAMF Computers Transformer Definition  Record in the CMDB Integration Studio Application Data Sources[cmdb_inst_application_feed] Table and ensuring it's Data Source is the  Data Source  associated with your Active  SG-JAMF Computers Scheduled Import  Job.

 

(i)   Navigate to the CMDB Integration Studio Application Data Sources[cmdb_inst_application_feed] Table

(ii)  Navigate to the  SG-JAMF Computers Transform Definition  Record

(iii) Update it's  Data Source  to the  Data Source  associated with your Active  SG-JAMF Computers Scheduled Import  Job if it is different from the  OOTB SG-JAMF Computers Data Source

 

The below Screenshot shows the  SG-JAMF Computers   Transformer Definition  Record in the CMDB Integration Studio Application Data Sources[cmdb_inst_application_feed] Table after it was updated with the  SG-Jamf Computers - USGOV   Data Source  that is associated with our Active  USGOV - SG-JAMF Computers Scheduled Import Job.

 

 

IntegrationHub ETL Tool

 

1. Ensure that a Valid  SG-JAMF Computers  Import Set is selected in the the I ntegrationHub ETL  Tool

 

(i)  Launch the I ntegrationHub ETL  Tool

(ii) Navigate to the  SG-Jamf  CMDB Application shown in the  CMDB Application  list

(iii) Open the  SG-JAMF Computers  Transform Definition displayed under the  SG-Jamf  CMDB Application

(iv) Navigate to the 1st 1 . Specify Basic Details  step and click on the  Import Source Data and Provide Basic Details  link to bring up the  Provide Basic Information for the ETL Transform Map  Screen. Populate the following Fields on this Screen:

 

Data Source:  Data Source  associated your Active  SG-JAMF Computers Scheduled Import Job  (prepopulated with the  Data Source  associated with the  SG-JAMF Computers Transform Definition  in the CMDB Integration Studio Application Data Sources[cmdb_inst_application_feed] Table)

 

Sample Import Set:  SG-JAMF Computers Import Set  associated with your Active  SG-JAMF Computers Scheduled Import Job  (_prepopulated with the  Most Recent Import Set  that was processed by your Active  SG-JAMF Computers Scheduled Import Job _)

 

The screenshot below shows the  Provide Basic Information for the ETL Transform Map  Screen with the fields on this Screen being populated with values associated with our Active  SG-JAMF Computers - USGOV Scheduled Import job :

 

Data Source:   SG-JAMF Computers - USGOV  Data Source associated with our Active  S** G-JAMF Computers-USGOV** Scheduled Import Job

Sample Import Set:  ISET0010771 Import Set  from our Active  S** G-JAMF Computers-USGOV** Scheduled Import Job.

 

 

(vi) Click on the  Save  Pushbutton on this Screen

 

N.B.  It is important to click on the  Save  Pushbutton on this Screen. This populates the  Import Set  Field in the  SG-JAMF Computers   Application Feed  Record in the Template States[sn_int_studio_template_state] ** Table. The data from the  Import Set  associated with the  SG-JAMF Computers   Application Feed  Record is the data that is used in the follow on  2. Prepare Source Data for Mapping  &  3. Map Data to CMDB and Add Relationships** IH-ETL Steps.

 

Note:  If for some reason you get an error message when you click on the  Save  Pushbutton use the below Steps for populating this  SG-JAMF Computers   Application Feed  Record  Import Set  Field:

 

(i)    Navigate to the Concurrent Import Sets[sys_concurrent_import_set] Table

(ii)   Navigate to your Most Recent  SG-Jamf Computers Concurrent Import Set  in this Table

(iii)  Expand this Most Recent  SG-Jamf Computers Concurrent Import Set  to display the list of I mport Sets  underneath

(iv)  Make note of 1 of these  Import Set Numbers  (The example  ISET0010771**  Import Set Number  shown in the above Screen was the  Import Set Number  associated with our  Most Recent SG-Jamf Computers Concurrent Import Set**)_. _You will be using this Import Set Number in the next step

(v)   Navigate to the Template States[sn_int_studio_template_state] Table

(vi)  Navigate to the  SG-JAMF Computers   Application Feed  Record in this Table

(vii) Populate the  Import Set Field  in this Record with the  Import Set Number  that you made a note of in the above (iv) step

 

1. Create a new  Extension Attributes JSON at Hardware Level  Transform in the  Child Hardware Object  using the  IntegrationHub ETL  Tool

 

In this step we will be using a Copy Transform to copy the contents of the  extension_attributes_json  field at the  Parent  Level to a new  extension_attributes_json_at_hardware_level  field in the  Nested Child Hardware Object  Level. This  extension_attributes_json_at_hardware_level  JSON String Field will be passed to the global.JSON.parse(input)Javascript Command for deserializing the  Extension Attributes  into a Collection of  Extension Attribute  Objects in a follow on  Last Logged in User   Script Transform  Operation.

 

(i)  Navigate to the 2nd  2. Prepare Source Data for Mapping  Step

(ii) Click on the  Preview and Prepare Data  link to bring up the  Preview and Prepare Data  Screen populated with the 1st 10 Rows of data from the  Import Set  specified in the  Sample Import Set  Field in (v) of the previous 4. Ensure that a Valid  SG-JAMF Computers  Import Set is selected in the the I ntegrationHub ETL  Tool step.

(iii) Click on 1 of the  Objects  e.g. Object[1] in the Left Hand  Nested Data Structure  Pane of the  Preview and Prepare Data  Screen to show the below list of  Nested Child Objects  that it contains:

 

- general

- software

- extension attributes

- location

- hardware

 

(iv) Click on the  Nested Hardware Child Object  Pill to display the  Hardware Child Object  Fields. 

 

The screenshot below shows the  Preview and Prepare Data  Screen with  Nested Hardware Child Object  data from the  SG-JAMF Computers ISET0010771 Import Set  with the new  extension_attributes_blob_at_hardware_level  Field being visible as an available Field at the  Hardware Child Object  Level.

 

Notice how object->object[1]->hardware is shown as the breadcrumb above the displayed  Transforms

 

 

(v)  Click on the  New Transform  Pushbutton on the  Preview and Prepare Data  screen and select the  Use Source Column  option from the  Pulldown Menu  that is displayed.

(vi)  Populate the following fields in the  New Transform  Screen that gets displayed:

 

Transform Type:    Copy Operation

Input Column 1:  extension_attributes_blob

Output Column Name:  extension_attributes_json_at_hardware_level

Script:

 

(vii) Click on the  Apply  Pushbutton on the  New Transform  Screen to save your new  extension_attributes_json_at_hardware_level**  Transform . You should see a new  extension_attributes_json_at_hardware_level    Transform  Column being displayed in the  Preview and Prepare Data** Screen like the one shown in the below screenshot. 

 

 

 

5.  Create a new  Last Logged in User  Transform in the  Child Hardware Object  using the I ntegrationHub ETL  Tool

 

This step involves creating a new  Last Logged in User  Transform in the  Child  Hardware  Object  for extracting the data from the e.g.  Last User  Extension Attribute that will have been included in the List of  Extension Attributes  referenced in the 1. Create a* JAMF Extension Attribute(EA) for capturing * Last logged in User**  in the JAMF Backend step and also in the  extension_attributes_blob_at_hardware_level  field referenced in the above step.

 

(i) Click on the  New Transform  Pushbutton on the  Preview and Prepare Data  screen (You should still in the  Child  Hardware  Object) and select the  Use Source Column  option from the  Pulldown Menu  that is displayed.

(ii)  Populate the following fields in the  New Transform  Screen that gets displayed:

 

Transform Type:   Script Operation

Input Column 1:  extension_attributes_json_at_hardware_level

Output Column Name:  last_logged_in_user

Script:

 

(function(batch, output) {

   for (var i = 0; i < batch.length; i++) {

      var input = batch[i].input; // Value of the input columns concatenated with '|'.

    // Access input column values by their names, ex: batch[i].column_name

    // In order to access columns containing multiple parts concatenated by a dot, use bracket notation ex. batch[i]['prefix_name.column_name']

    // Add your code here.

    // Set each output element below

    //output[i] = output_variable_name;

    var obj=global.JSON.parse(input);

    var outputValue = "";

    var ExtAttrName = "";

    for (var j=0; j < obj.length; j++){

       ExtAttrName = obj[j].name;

       if (ExtAttrName == " Last User") {

           outputValue = obj[j].values;

       }

   }

   output[i] = outputValue;

   }

})(batch, output);

 

Note: The var obj=global.JSON.parse(input); line of code deserializes the  extension_attributes_json  JSON string into a  JavaScript  object that is a collection of  Extension Attribute  Objects. The  for loop  is iterating over this collection until you get to the  Last Logged in User Extension Attribute e.g. Last User. If the  Last Logged in User Extension Attribute  is found then its value is saved to  last_logged_in_user Output Column  (outputValue = obj[j].values;)

 

(iii) Click on the  Apply  Pushbutton on the  New Transform  Screen to save your new  last_logged_in_user Transform. You should see a new  last_logged_in_user Transform  Column being displayed in the  Preview and Prepare Data  Screen like the one shown in the below screenshot.

 

 

 

You should see  Last Logged In User  data displayed in the  last_logged_in_user Transform  Column for  Computers  whose  Last User  Attribute is populated in the  JAMF Backend. (The above screenshot shows all Blanks in the  last_logged_in_user Transform  Column because a JAMF Extension Attribute(EA) for capturing  Last User  was not setup in the  JAMF  Backend that I was testing with).

 

 All  Reference  Type Fields in the ServiceNow CMDB contain  Sys ID's  of Records in the Relevant CMDB Lookup Table. All  User  Type Reference Fields like e.g.  Assigned To  User contain the  Sys ID's  of the  User  Records from the Users[sys_user] Table that represent the  Users  that the CMDB CI's are  Assigned To.

 

Making the assumption that you will be mapping  Last Logged in User  data to a Custom  Last Logged in User Reference  Field, the next 7.  Create a new  Last Logged in User ** Sys ID  Transform in the  Child Hardware Object  using the I ntegrationHub ETL  Tool step covers how to obtain the  Sys ID  associated with this  Last Logged In User  from the  Users[sys_user]  Table using an OOTB  User Lookup** Transform Operation.

 

6.  Create a new  Last Logged in User   Sys ID  Transform in the  Child Hardware Object  using the I ntegrationHub ETL  Tool

 

This step involves creating a new  Last Logged in User   Sys ID  Transform in the  Child  Hardware  Object  for

doing a  User Lookup  Transform Operation on the extracted  Last Logged in User  data from the above step. This  User Lookup  Transform Operation queries the Users[sys_user] Table using the  Last Logged in User  input Field provided and returns the  Sys ID  for that User if found in the Table.

 

(i) Click on the  New Transform  Pushbutton on the  Preview and Prepare Data  screen (You should still in the  Child  Hardware  Object) and select the  Use Source Column  option from the  Pulldown Menu  that is displayed.

(ii)  Populate the following fields in the  New Transform  Screen that gets displayed:

 

Transform Type:   User Lookup

User Name:   hardware.last_logged_in_user

Output Column Name:  last_logged_in_user_sys_id

 

(iii) Click on the  Apply  Pushbutton on the  New Transform  Screen to save your new  last_logged_in_user_sys_id  Transform. You should see a new  last_logged_in_user_sys_id  Transform Column being displayed in the Preview and Prepare Data Screen like the one shown in the below screenshot. 

 

 

 

You should see  Last Logged In User   Sys ID  data displayed in the  last_logged_in_user_sys_id Transform  Column for  Computers  whose  Last User  Attribute is populated in the  JAMF Backend  with a  User  that exists in the Users[sys_user] CMDB Lookup Table. (The above screenshot shows all Blanks in the  last_logged_in_user _sys_id Transform  Column because a JAMF Extension Attribute(EA) for capturing  Last User  was not setup in the  JAMF  Backend that I was testing with).

 

7.  Map the new  Last Logged in User   Sys ID  Transform Output Field to your Custom  Last Logged in User  Reference Field in the  Target Computer  CI using the I ntegrationHub ETL  Tool

 

You will be mapping the new  Last Logged in User   Sys ID  Transform Output Field that you created in the above Step to your Custom  Last Logged in User  Reference Field in your Target  Computer CI. 

 

(i)  Navigate to the 3rd  3. Map Data to CMDB and Add Relationships  Step in the  IntegrationHub ETL  Tool

(ii) Click on the  Select CMDB Classes to Map Source Data  link to bring up the  Select CMDB Classes to Map Source Data  Screen like the one shown in the below screenshot. You will be editing the  Computer 1 Class Mapping.

 

 

 

 

(iii) Click on the  Edit Mapping  Pushbutton shown on the  Select CMDB Classes to Map Source Data**   Screen to bring up the  Map to Computer 1 - Map to CMDB** screen like the one shown in the below screenshot.

 

 

 

(iv) Click on the  Add Attribute  Pushbutton to bring up the  Add Attribute  Screen

(v) Select your Custom  Last Logged in User  Attribute and click on the  Save  Pushbutton to be brought back to the  Map to Computer 1 - Map to CMDB  screen. You should see your Custom  Last Logged in User  Attribute displayed on this screen.  The screenshot below shows our Custom  Last Logged in User Attribute  being displayed on this screen after it was added.

 

 

 

(vi) Navigate to the  Nested hardware Child Object  in the Right Hand  Data  Pane

(vii) Drag the  last_logged_in_user_sys_id Transform  Output Field listed under the  hardware Child Object  to the Custom  Last Logged in User  Target Field that you added in the above step.

 

The screenshot below shows our Custom  Last Logged in User  Field being populated with the  last_logged_in_user_sys_id  Transform Output Field that we created in the above 7.  Create a new  Last Logged in User   Sys ID  Transform in the  Child Hardware Object  using the I ntegrationHub ETL  Tool Step.

 

 

 

Notice how  From collection:object.hardware  is displayed under the Mapping to Computer 1 (cmdb_ci_computer) Heading on the Screen. This is letting you know that the Computer 1 (cmdb_ci_computer) Mapping is encapsulated in the  Nested Hardware Child Object  contained in the  Parent Object.  You are mapping from the  last_logged_in_user_sys_id  Transform Output Field in the  Nested Hardware Child Object  to the Custom  Last Logged In User  Attribute in the same  Hardware Child Object.