topic Re: Joining two keys with the same subkey value in Cortex XSOAR Discussions

Joining two keys with the same subkey value

Antanas — Sun, 15 May 2022 07:40:22 GMT

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

Re: Joining two keys with the same subkey value

jfernandes1 — Sun, 15 May 2022 22:22:57 GMT

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)

Re: Joining two keys with the same subkey value

Antanas — Thu, 19 May 2022 11:42:23 GMT

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)

Re: Joining two keys with the same subkey value

jfernandes1 — Fri, 20 May 2022 02:14:23 GMT

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

Re: Joining two keys with the same subkey value

Antanas — Sun, 22 May 2022 11:22:37 GMT

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!