Joining two keys with the same subkey value

[Antanas]

Hi,

I need to join 2 context keys that match value of the same subkey, however I can't find a proper automation or transformation. In context I have 2 keys. One key contains IP's and Hostnames, the other contains IP's and Email Admins. I want to join those that match same IP. E.g. :

In the above scenario, as a result I want to have a key, that would hold: IP,Hostname,AdminEmail

Any ideas?

Thank you,

Antanas

[jfernandes1]

Hi @Antanas, not sure if there is a system transformer that can do a complex operation like that. You'll need to write a custom automation. Maybe something like this... 

data = demisto.context()['Data']
newList = []
for item in data['Emails']:
    for x in data['Hostnames']:
        if x['IP'] == item['IP']:
            item['Hostname'] = x['Hostname'] 
            newList.append(item)
demisto.results(newList)

[Antanas]

Hi @jfernandes1 , thanks, your POC code actually works! I would like to make a generic case from this. You specifically use

item['Hostname'] = x['Hostname'] 

How can I get and use this field, if it is different next time; or there are more than 1  subkeys under Hostnames? So far I came with below code, but not sure how to handle Hostname.

args = demisto.args()
parent = args.get('parent')
key1 = args.get('key1')
key2 = args.get('key2')
index1 = args.get('index1')
index2 = args.get('index2')

data = demisto.context()[parent]
newList = []
for item in data[key1]:
    for x in data[key2]:
        if x[index2] == item[index1]:
            item['Hostname'] = x['Hostname']
            newList.append(item)


results = CommandResults(
    outputs_prefix='Output',
    outputs_key_field='Result',
    outputs=newList
)

return_results(results)

[jfernandes1 · Accepted solution]

Hi @Antanas, could you try this. Save the text as yml file and upload it to the automations page. Its more flexible. you can issue the command like below.

!joinListWithLookup list1=${data.data.Emails} list2=${data.data.Hostnames} matchingKey=IP

commonfields:
  id: cfc605f7-6d30-4ea0-8330-dacb34e60397
  version: 7
vcShouldKeepItemLegacyProdMachine: false
name: joinListWithLookup
script: |-
  # Accept the input as arguments
  list1 = demisto.args()['list1']
  list2 = demisto.args()['list2']

  # Key that should be tested for matches. Since the order is unknown
  keyMatch = demisto.args()['matchingKey']

  # Validate the input
  error = 1
  if isinstance(list1, list):
      if isinstance(list2, list):
          if isinstance(keyMatch, str):
              error = 0
  if error == 1:
      return_error("Invalid inputs")

  newList = []
  # Loop primary list
  for item in list1:
      # Loop secondary list
      for x in list2:
          # Check if there is a matching key between the lists
          if x[keyMatch] == item[keyMatch]:
              # Add new keys from secondary list to primary
              for key in x.keys():
                  # Only add if they key does not exist, prevent existing data from being overwritten
                  if key not in item.keys():
                      item[key] = x[key]
              # Join data to new list
              newList.append(item)
  # In the last example I included a simple output.
  # The below example add data to the context and the warroom (A nice table)
  demisto.results({
      'Type' : entryTypes['note'],
      'Contents': newList,
      'ContentsFormat' : formats['json'],
      'HumanReadable': tableToMarkdown('New Table', newList),
      'ReadableContentsFormat' : formats['markdown'],
      'EntryContext' : {'newList' : newList }
  })
type: python
tags: []
enabled: true
args:
- name: list1
  required: true
  isArray: true
- name: list2
  required: true
  isArray: true
- name: matchingKey
  required: true
scripttarget: 0
subtype: python3
pswd: ""
runonce: false
dockerimage: demisto/python3:3.10.4.29342
runas: DBotWeakRole
engineinfo: {}
mainengineinfo: {}

[Antanas]

Hi @jfernandes1 , this worked like charm! I tested this with a bigger set of real life data, all appears as expected. For the record, I made some changes to be even more generic: added matchingKey2 in case the key name is not the same in both lists; added a "parent" key so I could define each time where the results would be stored in. The final code is as follows:

commonfields:
  id: cfc605f7-6d30-4ea0-8330-dacb34e60397
  version: 10
vcShouldKeepItemLegacyProdMachine: false
name: joinListWithLookup
script: |
  # Accept the input as arguments
  list1 = demisto.args()['list1']
  list2 = demisto.args()['list2']

  # Key that should be tested for matches. Since the order is unknown
  keyMatch1 = demisto.args()['matchingKey1']
  keyMatch2 = demisto.args()['matchingKey2']
  parent = demisto.args()['parent']

  # Validate the input
  error = 1
  if isinstance(list1, list):
      if isinstance(list2, list):
          if isinstance(keyMatch1, str):
              if isinstance(keyMatch2, str):
                  error = 0
  if error == 1:
      return_error("Invalid inputs")

  newList = []
  # Loop primary list
  for item in list1:
      # Loop secondary list
      for x in list2:
          # Check if there is a matching key between the lists
          if x[keyMatch2] == item[keyMatch1]:
              # Add new keys from secondary list to primary
              for key in x.keys():
                  # Only add if they key does not exist, prevent existing data from being overwritten
                  if key not in item.keys():
                      item[key] = x[key]
              # Join data to new list
              newList.append(item)
  # In the last example I included a simple output.
  # The below example add data to the context and the warroom (A nice table)
  demisto.results({
      'Type' : entryTypes['note'],
      'Contents': newList,
      'ContentsFormat' : formats['json'],
      'HumanReadable': tableToMarkdown('New Table', newList),
      'ReadableContentsFormat' : formats['markdown'],
      'EntryContext' : {parent : newList }
  })
type: python
tags: []
comment: joins to context entries, where matchingKey1=matchingKey2, e.g. !joinListWithLookup
  list1=${data.list1} list2=${list2} matchingKey1=key1 matchingKey2=key2 parent=results
enabled: true
args:
- name: list1
  required: true
  description: first list, e.g. ${list1}
  isArray: true
- name: list2
  required: true
  description: second list, e.g. ${list2}
  isArray: true
- name: matchingKey1
  required: true
  description: key from 1st list to match
- name: matchingKey2
  required: true
  description: key from 2nd list to match
- name: parent
  required: true
  description: new key to put joined results in
scripttarget: 0
subtype: python3
pswd: ""
runonce: false
dockerimage: demisto/python3:3.10.4.29342
runas: DBotWeakRole
engineinfo: {}
mainengineinfo: {}

Thanks a lot for your help!