google / sa360-webquery-bigquery Goto Github PK

is there a way to authenticate a web query report using a service account for this sort of report :
https://searchads.google.com/ds/reports/download?ay

Users may want to run the tool using a scheduler (cron) on GCE or other such servers, storing user credentials on such devices is risky and error prone.

It seems like there is a limit of rows you can process with this tool. During my test, web queries that exceed 500k row will fail most of the time, but all of our sku based reporting is more than a million rows per day. I set up a vm that has 96vCPU and 360gb of ram, and it was able to process almost 1 million row sometimes, still failed most of the time.

When it failed, this is what I get in Big Query, an empty table.

I used your code but with error

SEVERE: [Report 366300] Error Processing
java.io.IOException: Server returned HTTP response code: 403 for URL: https://
at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1924)
at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1520)
at java.base/sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:250)
at dswebquerytobigquery.WebQuery$WebQueryReader.getStream(WebQuery.java:100)
at dswebquerytobigquery.WebQuery$WebQueryReader.writeAsCsv(WebQuery.java:107)
at dswebquerytobigquery.TransferRunner.run(TransferRunner.java:64)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)

• I have
  • agency admin access to SA360
  • owner access to the project

This is the account I authenticated Cloud SDK with on VM build from cloud shell

• The service account is designated to the VM and has
  • advertiser access to SA360
  • editor access to bq & gcs

Is there a step missing on configuring ssh key for service account?

SEVERE: [Report 352726] Error Processing
java.io.IOException: Server returned HTTP response code: 401 for URL: https://searchads.google.com/ds/reports/download?ay=2...&av=2...&rid=3...&of=webqueryphtml&authuser=0
        at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1924)
        at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1520)
        at java.base/sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:250)
        at dswebquerytobigquery.WebQuery$WebQueryReader.getStream(WebQuery.java:97)
        at dswebquerytobigquery.WebQuery$WebQueryReader.writeAsCsv(WebQuery.java:104)
        at dswebquerytobigquery.TransferRunner.run(TransferRunner.java:64)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)```

Hi,

I'm following the YouTube guide and stuck at 8m:55s in the video after getting an error when compiling the code:

mvn clean compile assembly:single

Info/Error:

[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 0.090 s
[INFO] Finished at: 2022-04-23T16:30:31Z
[INFO] ------------------------------------------------------------------------
[ERROR] The goal you specified requires a project to execute but there is no POM in this directory (/home/tom_sharon). Please verify you invoked Maven from the correct directory. -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MissingProjectException

I'm assuming it's because there's an issue with the directory. Here is my code (line 52-54 in the TransferRunner.Java file):

var webQuery = new WebQuery(xferConfig.getWebQueryUrl("this is where I pasted the web query URL"), credential);
// local file to store the report as CSV
var tempCsvFile = createCsvFile("C:\Users\toms\Documents\");

The code is a bit different and the resources folder with the client_secrets.json file aren't on github so I created them and copied the tutorial. I created a Desktop type (couldn't find "other") Credential with the SA360 API to generate the Client ID and Secret.

Also, I've not set up the sample-config.csv file as I didn't think it's required for the build to be successful?

Thanks,
Tom

Dear,
How to contribute to this project?

The sa360-webquery-biggquery Java program requests authorization from the end user and caches their OAuth2 Access Token and Refresh Token in the StoredCredential File.
Permissions for this file are: -rw-r--r-- (744)
This set of file permissions allows the Owner of the file to read and write. Additionally it allows any other user on the system to read the file.

A file on a local system which stores credentials (even short-term credentials), is a target for malware on the local system. Access should be restricted to only those users and groups requiring access. Malware known as 'TeamTNT' is known to target endpoints for locally cached credentials for cloud infrastructure.

Due to the sensitive nature of the StoredCredentials File, the file permissions should be changed to: -rw-------. This would restrict Read and Write operations to the Owner of the File.
Permissions for the 'StoredCredentials' File should match those of the ~/.config/gcloud/access_tokens.db file.
The access_tokens.db file holds the cached access tokens and refresh tokens utilized by gcloud. The file permission for the access_tokens.db sqlite file is: -rw-------

XOXO,
_kat

Hello,

I'm an independent security researcher performing security research under the GitHub Security Lab Bug Bounty Program. I believe I may have found a security vulnerability in this project.

Please open a security advisory against this repository so we can privately discuss the details. This advisory can be opened by a user with admin permissions on this repository.

https://github.com/google/sa360-webquery-bigquery/security/advisories

I am getting this error when I rerun the script a few hours after the first successful attempt. I deleted the SotredCredential file and ran again, it prompt me to login again, and everything was fine again. However, in order to schedule to run it automatically daily, it needs to be able to stored the credential and use it in the future. Could I have done something wrong here?

Much appreciated in advance.

Jun 12, 2020 5:25:36 PM dswebquerytobigquery.Main main
INFO: config file: sample-config-4.csv
Jun 12, 2020 5:25:36 PM dswebquerytobigquery.Main main
INFO: access_token: ya29.a0AfH6SMDEzSxMd3X-jiHY4r2OZSraeGoRXtnECFK_i8BrMDDwwXjwImFQA2p_rDRf7RtUIxZWUDJiIgyktvtfjtBsW-dhrLb9djE-2ngooKmC9oiCALd7cr-aaw-3YIJFUmdgIq9Kw3Wq97x2Bq1KIyYVXvV_O-kAyPM
Jun 12, 2020 5:25:36 PM dswebquerytobigquery.TransferRunner run
INFO: Processing: TransferConfig{bigQueryConfig=BigQueryConfig{projectId=marketing-database-276519, datasetId=sa360_reports, tableId=webquery_report_test_4}, tempGcsBucketName=wq-to-bq, webQueryUrl=https://searchads.google.com/ds/reports/download?ay=20700000001032621&av=21700000001165309&rid=318110&of=webqueryphtml&authuser=0}
Jun 12, 2020 5:25:36 PM dswebquerytobigquery.TransferRunner run
INFO: [Report 318110] starting: url: https://searchads.google.com/ds/reports/download?ay=20700000001032621&av=21700000001165309&rid=318110&of=webqueryphtml&authuser=0
Jun 12, 2020 5:25:36 PM dswebquerytobigquery.TransferRunner run
INFO: [Report 318110] localFile: /tmp/dswq_9286139848404184152.csv
Jun 12, 2020 5:25:37 PM dswebquerytobigquery.TransferRunner run
SEVERE: [Report 318110] Error Processing
java.io.IOException: Server returned HTTP response code: 401 for URL: https://searchads.google.com/ds/reports/download?ay=20700000001032621&av=21700000001165309&rid=318110&of=webqueryphtml&authuser=0
at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1919)
at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1515)
at java.base/sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:250)
at dswebquerytobigquery.WebQuery$WebQueryReader.getStream(WebQuery.java:95)
at dswebquerytobigquery.WebQuery$WebQueryReader.writeAsCsv(WebQuery.java:102)
at dswebquerytobigquery.TransferRunner.run(TransferRunner.java:62)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)

Hi,
I have followed step-by-step instructions mentioned in the video but unfortunately I am getting the following error mentioned below. Can you please help me with this?

Exception in thread "main" java.lang.NullPointerException
at dswebquerytobigquery.Authorizer.refreshAndStoreCredential(Authorizer.java:58)
at dswebquerytobigquery.Authorizer.authorize(Authorizer.java:51)
at dswebquerytobigquery.Main.main(Main.java:45)

Thanks for your help in advance!