Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Script automating dynamic queries in a remote host

Status
Not open for further replies.

pax94

Systems Engineer
Jun 19, 2021
1
0
0
CH
Hi everyone,

Since very long time, at work I need to repeat the same operations every time.
I've therefore decided to automate via script the following instructions, costs what it may:

[ol 1]
[li]ask ID from prompt and store it locally[/li]
[li]ssh to remote host[/li]
[li]enter docker container[/li]
[li]launch DML queries using the previously stored ID[/li]
[li]this returns one row with another ID from the column 'id' which I need to use in the next command[/li]
[li]store result of a DML including the new ID into a csv[/li]
[li]copy the csv into a different path[/li]
[li]exit the docker container[/li]
[li]change permission on that file[/li]
[li]exit the ssh[/li]
[li]secure copy of the file from the remote host to the initial/current folder[/li]
[/ol]

It might look kind of tricky but the only point I see as problematic (on which I'm actually stuck) are 2, 3 and 5.
Here is what I've done so far:

Code:
#!/bin/bash
read -p "Enter the ID you would like to process: " my_id
ssh 10.10.10.10
sudo docker exec -it psql-db /bin/sh
psql -U postgres -d MY-DATABASE -c "SELECT * FROM all_cases WHERE id='my_id'";
#get the ID from the 'id' column and save it as 'new_id'
Copy (Select one, two, three FROM table WHERE id='new_id') To '/tmp/'new_id'.csv' With CSV DELIMITER '|' HEADER;
cp /tmp/'new_id'.csv /var/tmp/
exit
sudo chown user:user /opt/tmp/'new_id'.csv
exit
scp 10.10.10.10:/opt/tmp/'new_id'.csv .

Any suggestion is welcome, feel free to change the logic and thank you in advance.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top