Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Need help getting my output from MYSQL query into right format
Top Forums Shell Programming and Scripting Need help getting my output from MYSQL query into right format Post 302635307 by brianjb on Friday 4th of May 2012 03:22:58 PM
Old 05-04-2012
Need help getting my output from MYSQL query into right format
Good afternoon!

I have been lurking in this forum for awhile now. I have just recently started posting. I think this is a really good site. With that being said, I don't like to just run and get an answer before I try my best first.

I have poured some blood, sweat and tears into this....well at least tears anyway.

I am doing queries in MYSQL, and I need to have the output in a certain format.

Here is what I am trying to accomplish:

Code:
Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers
test.com,21600,604800,3600,86400,86400,dns@test.com,,,,,,False,False,Use Server Value,Use Server Value,Use Server Value,Use Server Value,No,,,Use Server Value,Use Server Value,Use Server Value,Warn,No,,,False,Use Server Value,Any,Any,Any,No,,,False,Any,Any,Any,Warn,No,,,False,False,Use Server Value,Any,Any,Any,No,,,False,False,Use Server Value,Any,Any,Any,No,,,False,Any,No,0,No,0,,,,ns1.test.com,P,0,ns2.test.com,S,0,ns3.test.com,S,0,

That is two lines. I need it to be like this:
header line
output line
blank line
header line
output line
blank line
etc, etc

Here is my script, thus far:

Code:
#!/usr/bin/bash
#
echo -n "Enter the DNS server name (ns1.blah.com, for example) and press [ENTER]: "
read dnsservername

echo -n "Enter the domain type (Public, for example) and press [ENTER]: "
read dnsdomaintype

echo -n "Enter the zone type (master, for example) and press [ENTER]: "
read dnszonetype

dnsserverid=`/opt/incontrol/mysql/bin/mysql -u username -ppassword instancename --skip-column-names -e \
"select id from dnsserver where name like '%$dnsservername%';"`

dnsdomaintypeid=`mysql -u username -ppassword instancename --skip-column-names -e \
"select id from dnsdomaintype where name like '$dnsdomaintype%';"`

header=`echo "Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,\
Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,\
Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,\
DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,\
allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,\
allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,\
BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,\
allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,\
Import External Updates,allow-query,allow-transfer,allow-update,check-names,\
notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,\
Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,\
notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,\
Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,\
notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,\
allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,\
Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,\
DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,\
Sec DNS Update Flag,End DNS Servers"`
#
#
#
query=`/opt/incontrol/mysql/bin/mysql -u username -ppassword instancename -e \
"select DISTINCT dnsdomain.name as 'Domain Name', \
dnsdomain.refresh as 'Refresh Time', \
dnsdomain.expire as 'Expire Time', \
dnsdomain.retry as 'Retry Time', \
dnsdomain.DEFAULTTTL as 'Default TTL', \
dnsdomain.negcachettl as 'Negative Cache TTL' \
from dnsdomain \
left join dnsdomaintype \
on dnsdomain.DNSDOMAINTYPEID=DNSDOMAINTYPE.id \
left join dnsview \
on dnsview.id=dnsdomain.DNSDOMAINTYPEID \
left join dnszone \
on dnszone.dnsdomainid=dnsdomain.id \
left join dnsserver \
on dnsview.dnsserverid=dnsserver.id \
left join zonestatus \
on zonestatus.dnsdomainid=dnsdomain.id \
where zonestatus.dnsserverid='$dnsserverid' \
and dnsdomaintype.id='$dnsdomaintypeid' \
and dnszone.type = '$dnszonetype'"`

echo "$header"
echo "$query"
echo ""
echo "$header"
echo "$query"

Here is what I get when I run that:

Code:
Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers

255.in-addr.arpa.       3600    604800  900     14400   300
28.in-addr.arpa.   7200    1209600 900     14400   300
10.in-addr.arpa.        10800   604800  3600    86400   600
time.com.  10800   604800  3600    3600    3600
news.com.   3600    86400   3600    3600    3600

Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers

How can I get it to it this way:
Code:
Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers
255.in-addr.arpa.       3600    604800  900     14400   300

Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers
28.in-addr.arpa.   7200    1209600 900     14400   300

Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers
10.in-addr.arpa.        10800   604800  3600    86400   600

Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers
time.com.  10800   604800  3600    3600    3600

Domain Name,Refresh Time,Expire Time,Retry Time,Default TTL,Negative Cache TTL,Zone e-mail address,Start Zone Options,Extensions,Prefix of zone db file,Postfix of zone db file,ALU DNS 6.0 Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,BIND-8.X Options,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,BIND-9.X Options,DNSSEC enabled zone,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 3.X Options,Import External Updates,allow-query,allow-transfer,allow-update,check-names,notify,zone block of named.conf,LUCENT DNS 4.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,LUCENT DNS 5.X Options,DNSSEC enabled zone,Import External Updates,allow-notify,allow-query,allow-transfer,allow-update,notify,zone block of named.conf,WINDOWS 2000 DNS Options,aging,allow-transfer,allow-update,no-refresh-interval,notify,refresh-interval,zone-options,End Zone Options,Start DNS Servers,Primary DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,DNS Server Name,DNS Server Type,Sec DNS Update Flag,End DNS Servers
news.com.   3600    86400   3600    3600    3600

I know that I still need to change the zone line, for example:
Code:
news.com.   3600    86400   3600    3600    3600

I plan on running that through awk, since most of the data that is usable is the first 6 sections anyway, and the rest at the end is pretty much the same.

Can someone please help?

If I need to further explain, please let me know if I didn't make any sense.

Thanks again!!!!
Last edited by brianjb; 05-04-2012 at 04:26 PM.. Reason: getting rid of company name
 

8 More Discussions You Might Find Interesting

1. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies

2. Shell Programming and Scripting

add the output of a query to a variable to be used in another query

I would like to use the result of a query in another query. How do I redirect/add the output to another variable? $result = odbc_exec($connect, $query); while ($row = odbc_fetch_array($result)) { echo $row,"\n"; } odbc_close($connect); ?> This will output hostnames: host1... (0 Replies)
Discussion started by: hazno
0 Replies

3. Shell Programming and Scripting

Help needed to format mysql output

Hi all, Does anyone know how to format the output from mysql from within a shell script? i.e. RESULT=`mysql command` echo ${RESULT} the ${RESULT} only displays the output on one line instead of how mysql would display it as columns etc (3 Replies)
Discussion started by: muay_tb
3 Replies

4. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

5. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

6. UNIX and Linux Applications

Please help: Oracle gqsql or sqlplus output format like mysql

On psql select titolo,lingua from titolo where titolo ~* 'brivid'; titolo | lingua ------- + ------ Brivido | 1 On Sqlplus/gqsql SQL> select titolo,genere,anno,lingua from titolo where titolo like '%rivid%'; TITOLO... (6 Replies)
Discussion started by: Linusolaradm1
6 Replies

7. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies

8. Shell Programming and Scripting

How to create a file from output of vertica table query in UTF-8 format?

Hello, In my shell script, I extract table data from HP Vertica DB into a csv file using vsql -c command. But the problem is the file getting created is in binary format and hence some of the data becomes unreadable which has chinese characters as part of data. file -i filename.csv - gives... (2 Replies)
Discussion started by: Dharmatheja
2 Replies
All times are GMT -4. The time now is 07:49 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy