Goolge Sheets: le funzioni utili alla Open Source Intelligence

Quando si lavora su progetti OSINT c’è l’esigenza di condividere i risultati delle vostre ricerche. In questo articolo Tom Jarvis spiega come utilizzare lo strumento Google Sheets non solo per la condivisione, ma illustra anche una serie di funzioni che ritornano utili quando si fa Open Source Intelligence.

«Quando conduci un grande progetto di ricerca open source, vuoi un modo per archiviare i tuoi dati e condividerli con i collaboratori.

Il mio strumento preferito per questo è Fogli Google (Google Sheets) perché consente una collaborazione in tempo reale e un facile monitoraggio delle modifiche. Fogli Google funziona in modo molto simile a Excel di Microsoft.

Gran parte di questa guida esaminerà gli strumenti utilizzati nel Tibet Research Project, un progetto di ricerca open source che documenta le prigioni e i centri di detenzione in Tibet e nelle regioni circostanti per fornire una base per la futura ricerca sui diritti umani.

Questa ricerca coinvolge l’ubicazione, l’analisi, la classificazione e l’identificazione delle carceri nelle regioni di interesse e quindi viene generata una grande quantità di dati.

Google Translate

Uno dei migliori strumenti per la ricerca in paesi stranieri è la funzione Google Translate in Fogli Google. Ciò consente di generare traduzioni per il testo nelle celle che possono essere utili durante la ricerca di informazioni.

Nel mio caso, avevo un elenco di contee da cercare e volevo generare i nomi in cinese e generare collegamenti di ricerca Baidu per un facile accesso alle informazioni. (Per quelli di voi che non hanno familiarità con Internet cinese, Baidu è l’equivalente di Google).

Per utilizzare la funzione Google Translate, devi sapere diverse cose. Innanzitutto la funzione:

= GOOGLETRANSLATE ()

Quindi all’interno di ciò, vuoi la cella che deve essere tradotta, la lingua che usi e la lingua in cui stai traducendo. Per indicare a Fogli quali lingue stai utilizzando, inserisci i codici di due lettere tra parentesi. Puoi trovare i codici di due lettere con questo link .

= GOOGLETRANSLATE (C3, “en”, “zh”)

La funzione precedente tradurrà il contenuto della cella C3 dall’inglese (“en”) al cinese (“zh”).

Collegamento ipertestuale

La generazione di collegamenti ipertestuali varia da facile a difficile a seconda di come i siti web utilizzano i loro collegamenti. Alcuni siti sono abbastanza semplici, altri sono molto più complicati.

Il sito Web Satellites.Pro ha una struttura URL sensibile, ad esempio.

https://satellites.pro/China_map#29.691272,91.157717,19

Ha il suo collegamento web di base, seguito dalla regione e dalle coordinate decimali , quindi un fattore di zoom.

Se conosci il paese e le coordinate puoi generare il link:

satellites.pro/China_map#[LATITUDE”,[LONGITUDE”,19

Come visto sopra, vogliamo inserire la latitudine e la longitudine in questo collegamento web. Potremmo anche cambiare il numero finale, 19, se vogliamo cambiare il fattore di zoom.

Per generare questo collegamento dalle coordinate, abbiamo solo bisogno di due funzioni:

HYPERLINK e CONCATENATE

HYPERLINK ti consente di creare un collegamento da un URL in una cella e definisce il testo che viene visualizzato come collegamento cliccabile.

CONCATENATE consente di unire le celle tra loro, utile quando si unisce la struttura di collegamento con le coordinate.

Per prima cosa, generiamo l’URL che vogliamo.

= CONCATENA (“https://satellites.pro/#”, N7, “, 18”)

Questo collega il valore della cella N7 che è la nostra cella delle coordinate alla fine dell’URL, quindi aggiunge la stringa “, 18” alla fine per definire il fattore di zoom.

il risultato sarebbe:

https://satellites.pro/#[COORDINATES[”,18

Possiamo quindi collegarlo ipertestuale con la funzione = HYPERLINK. Questa funzione utilizza il formato = HYPERLINK (URL, “Link Text”). Per il testo del collegamento, generalmente uso solo la parola “Link”.

La combinazione di questi ci dà la formula:

= HYPERLINK (CONCATENATE (“https://satellites.pro/#”, N7, “, 18”), “Link”)

Questo può essere utilizzato anche per il primo esempio in cui abbiamo tradotto i nomi in termini di ricerca in un motore di ricerca come Baidu.

Suddivisione delle coordinate in latitudine e longitudine

Per separare le coordinate da un valore decimale in valori di latitudine e longitudine in Fogli Google, puoi utilizzare le funzioni TRIM, INDEX e SPLIT. Ciò che fanno è dividere la stringa in un punto definito, in questo caso la virgola. Quindi prende la prima o la seconda parte come definito dall’utente, quindi si sbarazza del resto.

Per Latitude

= SE.ERRORE (TRIM (INDEX (split (N7, “,”), 1)))

Per longitudine

= SE.ERRORE (TRIM (INDEX (split (N7, “,”), 2)))

(Dove N7 è la cella contenente le coordinate decimali)

Come funziona? Analizziamo la formula. Le sezioni in grassetto sono quelle che guardiamo.

= SE.ERRORE (TRIM (INDEX (split (N7, “,”) , 1)))

Per prima cosa alla funzione SPLIT viene detto quale cella guardare, quindi la divide in corrispondenza della parte tra virgolette. In questo caso, stiamo dicendo a Fogli di dividere alla virgola.

= SE.ERRORE (TRIM (INDEX (split (N7, “,”), 1)) )

Quindi la funzione INDICE lo utilizza e indicizza la prima o la seconda parte. Qui puoi vedere che il numero è 1, ovvero tutto ciò che precede la virgola. Se volessi tutto dopo la virgola, useresti il ​​numero 2.

= SE.ERRORE (TRIM (INDEX (split (N7, “,”), 1)))

Infine, la funzione TRIM elimina il resto. Vedrai anche una funzione SE.ERRORE. Questo è solo uno strumento utile per avvolgere altre funzioni per consentire una cella vuota o un messaggio di errore se qualcosa non funziona.

Scraping dei dati

Lo scraping dei dati è un ottimo modo per copiare i dati nel tuo foglio di lavoro dalle pagine web. È un po’ più complicato di alcune delle opzioni precedenti ed è un po’ più complicato, quindi per una panoramica completa, consulta la guida completa qui .

Come si vede nell’immagine sopra, un utile punto di partenza sono le tabelle di Wikipedia che sono spesso le più facili da raschiare. Questa tabella è delle basi dell’aeronautica cinese ed è un ottimo esempio perché contiene coordinate e può essere importata in una mappa di Google da Fogli Google per mappare visivamente le posizioni.

Il codice necessario per estrarre i dati dalla pagina delle basi aeree PLA è una semplice formula di importazione HTML .

= ImportHtml (“https://en.wikipedia.org/wiki/List_of_People%27s_Liberation_Army_Air_Force_airbases”, “table”, 6)

Funziona definendo l’URL da scrape, quindi il tipo di dati (tabella), quindi quale tabella utilizzare (6).

Nell’esempio sopra, la tabella che volevamo era la sesta nella pagina, quindi abbiamo aggiunto il valore 6. A volte questo può essere un po ‘difficile da determinare, quindi è spesso necessario giocare.

Ancora una volta, il consiglio è di consultare la guida completa, poiché ci sono molte informazioni utili.

Estrazione di unità dai dati

A volte hai valori aggiunti che hanno unità. Un esempio di ciò è la colonna del perimetro della prigione nel set di dati dei ricercatori del Tibet Research Project.

I valori qui sono seguiti da una “m” per i metri, ad esempio “500 m”. Questo può essere un problema se si desidera calcolare valori o generare un rapporto, ad esempio, sulla distanza perimetrale totale di tutte le carceri trovate. Per risolvere questo problema abbiamo bisogno per eliminare i caratteri delle unità.

https://tompatrickjarvis.medium.com/useful-google-sheets-functions-for-osint-research-71337f1b5407

Twitter: @tomtomjarvis