📠[Long list] Account Names to Facility Match using PeeringDB

This playbook uses Google Search, PeeringDB and TableOps

Business question: which organizations have data centers located in a given list of locations?

Playbook

Link to the playbook

Please pay attention to the spaces between words in each query. There must be only 1 blank space.

1. Search Google to get the correct organization names of the accounts

Cell 2 - Tool: TableOps

Drop rows with duplication in @1.AccountName

Cell 3 - Tool: Google Search

Search @2.AccountName on site peeringdb.com/org. If "organic" field has more than one item, then look at the first result. If the first result's title has " - " then return the text before " - ". Otherwise return empty.

Cell 4 - Tool: TableOps

Drop rows where @3.search_account_on_peeringdb is duplicated or empty. Change column name @3.search_account_on_peeringdb to corrected_name and return the table.

Note that in the table generated by the previous cell, the name of the column might be different, not "search_account_on_peeringdb". Use @ to get the right name.

2. Get organization id of the accounts

Cell 5 - Tool: PeeringDB

Get organization with name contains @4.corrected_name. sleep 3s between requests.

Cell 6 - Tool: TableOps

Return a table with @5.AccountName , @5.corrected_name , org name, org id with 1 org id per row. Drop rows where @5.corrected_name is not org name.

3. Get network id of the accounts

Cell 7 - Tool: PeeringDB

Get networks with org id is @6.org_id. Sleep 3s between requests.

Cell 8 - Tool: TableOps

Return a table with @7.AccountName, @7.corrected_name, @7.org_id, and network id with one network id per row.

4. Get facilities of the networks

Cell 9 - Tool: PeeringDB

Get facility of network with id @8.network_id. Return data. Sleep 3s between requests. 

Cell 10 - Tool: TableOps

Return a table with @9.AccountName , @9.corrected_name , @9.org_id , @9.network_id , facility name with 1 facility name per row.  

5. Match the facility name with some facilities

Cell 11 - Tool: TableOps

Add a column with value is True if @10.facility_name match any of the following names. 
AIMS CJ1 Cyberjaya
AT TOKYO (CC1/CC2)
Chief HD Building Taipei
Chief LY Building Taipei
CoreSite - Los Angeles (LA1) One Wilshire
CoreSite - Los Angeles (LA2)
CoreSite - Secaucus (NY2)
Cyber Data Center International Jakarta
datamena DXA - Dubai
Equinix AE1 - Adelaide
Equinix BR1 - Brisbane (formerly Metronode Brisbane 1)
Equinix CA1 - Canberra
Equinix DC1-DC15, DC21 - Ashburn
Equinix DX1 - Dubai
Equinix DX2 - Dubai
Equinix HK1 - Hong Kong
Equinix HK2 - Hong Kong
Equinix LA1 - Los Angeles
Equinix LA1 - Los Angeles
Equinix MB1 - Mumbai (GPX Mumbai 1)
Equinix MB2 - Mumbai (GPX Mumbai 2)
Equinix ME1/ME2 - Melbourne
Equinix ME4 - Melbourne, Derrimut (formerly Metronode Melbourne 2)
Equinix ME5 - Melbourne (formerly Metronode Melbourne 1)
Equinix OS1 - Osaka
Equinix OS1 - Osaka
Equinix PE1 - Perth
Equinix PE2/PE3 - Perth
Equinix SG1 - Singapore
Equinix SG3 - Singapore
Equinix SG4 - Singapore
Equinix SG5 - Singapore
Equinix SL1 - Seoul
Equinix SV1/SV5/SV10 - Silicon Valley, San Jose
Equinix SV8 - Silicon Valley, Palo Alto
Equinix SY1/SY2 - Sydney
Equinix SY3 - Sydney
Equinix SY4 - Sydney
Equinix SY5 - Sydney
Equinix SY6 - Sydney (formerly Metronode Sydney 2)
Equinix SY7 - Sydney (formerly Metronode Illawara 1)
Equinix TY1 - Tokyo
Equinix TY10 - Tokyo
Equinix TY11 - Tokyo
Equinix TY2 - Tokyo
Equinix TY3 - Tokyo
Equinix TY4 - Tokyo
Equinix TY5 - Tokyo
Equinix TY6 - Tokyo
Equinix TY7 - Tokyo
Equinix TY8 - Tokyo
Equinix TY9 - Tokyo
Global Switch Singapore
Global Switch Sydney
KINX Dogok
KINX Gasan
London Hosting Centre
MEGA-i (iAdvantage Hong Kong)
Netmagic Vikhroli
NEXTDC B1
NEXTDC B2
NEXTDC C1
NEXTDC M1
NEXTDC M2
NEXTDC M3
NEXTDC P1
NEXTDC P2
NEXTDC S1
NEXTDC S2
NEXTDC S3
NEXTDC SC1
Shin Nikko Bldg
SmartHub Fujairah
STT Chennai 1
STT Chennai 2
Telehouse - London (Docklands East)
Telehouse - London (Docklands North)
Telehouse – TOKYO Otemachi (KDDI Otemachi)
VITRO Makati Data Center 2 - VM2
VITRO Pasig Data Center - VPS
Westin Building Seattle
YourDC Pty Ltd - Hawthorn
Interxion Marseille (MRS1, MRS2, MRS3) 

Cell 12 - Tool: TableOps

Return a table with @11.match_facility is true. Drop rows with duplications in @11.AccountName. Return a json table.

Notes

When the number of rows is large, add this prompt to the query to avoid PeeringDB throttling error message.

"Sleep 3s between requests."