Joining two keys with the same subkey value

cancel
Showing results for 
Search instead for 
Did you mean: 

Joining two keys with the same subkey value

L1 Bithead

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. :

unnamed (1).png

 

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

 

Any ideas?

Thank you,

Antanas

 

 

Senior Security Officer
1 ACCEPTED SOLUTION

Accepted Solutions

L3 Networker

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: {}

  

View solution in original post

4 REPLIES 4

L3 Networker

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)

 

 

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)

 

 

Senior Security Officer

L3 Networker

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: {}

  

L1 Bithead

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!

 

 

Senior Security Officer
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!