Checkpoint -- queryDB_util to query Smartcenter from command line

A few days ago I looked at the licenseDataCollector.sh: script and found that it was creating a lot of files in a temporary directory. these files were the network objects in the Smartcenter DB.
inside the script I found several calls to the command queryDB_util
some investigation on Google returned a few hits on this interesting tool

here is the help from the queryDB_util

U s a g e:
==========

queryDB interactive mode:
----------------------
[-t <table_name> [-s <query_str>]]  # name of requested table. 'query_str' is valid CPMI query string
[-o <object_name>]                  # name of requested object
[-a]                                # prints details of all objects

[-mu <modified_by>]                 # administrator who last modified the object
[-mh <modified_from>]               # host from which object was last modified
[-ma <modified_after>]              # minimum last modification date <[hh:mm:ss][-ddmmmyyyy]>
[-mb <modified_before>]             # maximum last modification date <[hh:mm:ss][-ddmmmyyyy]>

[-p<m|u|h|t|f>]                     # special print options: 
                                    #    m - last_modification details
                                    #    u - administrator name modification 
                                    #    h - host name modification
                                    #    t - last modification time 
                                    #    f - fields details

[-f <file_name>]                    # output file name for results
[-h]                                # get this usage info
[-q]                                # quit

Arguments can appear in any order.
If an argument appears more than once, the last one counts.


To get one of 'Objects Query' or 'Rules Query' modes:
----------------------------------------------------
   Phase I - connection arguments:
----------------------------------
-s <server_name> [-u <username> -p <password> | -c <certificate_name> -p <password>]

   Phase II - feature-mode argument:
------------------------------------
-objectsquery | -rulesquery             #'objectsquery' for Objects Query, 'rulesquery' for Rules Query

   Phase III - specific mode arguments:
---------------------------------------

Objects Query Usage:
====================
<ip address(es)>        #one or more ip addresses to be query for their referencing objects

Rules Query Usage:
==================

-ob <network_objects name(s) or ip(s)>          #a mix of objects names and ip addresses is valid
[-c <column_name>]                                              #specify the query on one column
[-op]                                   #search of ALL given objects under sub-query (default: AT LEAST ONE)
[-e]                                    #force explicit search in rules (default: implicit search)
[-n]                                    #for search of the negation of sub-query

[-r]                                    #defines an OR relation on all sub-queries. (default: AND relation)


Notes:
======
1.      Only the -ob argument has to be present. This argument informs the utility that a new sub-query is to be parsed.
2.      Due to the former note, the -ob argument have to be the first argument of each sub-query input.
3.      The -r argument applies to all of the sub-queries set and defines there relation. Since the number of sub-queries is unlimited, this argument must come last!
4.      All other arguments can appear in any order.

What can you do with this utility?

You can query the Smartcenter database for all kind of objects ( as long as you understand the CPMI syntax).

here are a few examples to give you an idea taken from the licenseDataCollector.sh script

return number of smartcenter objects:

echo -e "localhost\n-t network_objects -s management='true'\n-q" | queryDB_util 

Enter Server name: 

Please enter a query, -h for help or -q to quit: 

query> 

Q u e r y i n g   D B

=====================

Object Name: Management1

Table Name: network_objects

Last Modified by: Security Management Server

Last Modified from: localhost

Last Modification time: Tue Sep  9 09:26:09 2014

Object Name: Management2

Table Name: network_objects

Last Modified by: Security Management Server

Last Modified from: localhost

Last Modification time: Tue Sep  9 09:26:08 2014

Object Name: Management3

Table Name: network_objects

Last Modified by: Security Management Server

Last Modified from: localhost

Last Modification time: Tue Sep  9 09:26:06 2014

A total of 3 objects match the query.

query all network objects of type host, then extract with grep name and ip address

echo -e "localhost\n-t network_objects -s type='host' -pf\n-q" | queryDB_util | egrep 'Name Object|ipaddr:'

Object Name: Node1
    ipaddr: 10.1.2.3
Object Name: Node2
    ipaddr: 10.4.5.6

Query to find all firewalls modules (both managed and externally managed:

echo -e "localhost\n-t network_objects -s type='gateway' -s cp_products_installed='true' \n-q" | queryDB_util  | grep Object


Object Name: FW1
Object Name: FW2
Object Name: Fw3
Object Name: FW4
Object Name: Fw5


If you want to go deeper you change the greps

echo -e "localhost\n-t network_objects -s type='gateway' -s cp_products_installed='true' -pf \n-q" | queryDB_util  | egrep 'Object Name|location:|osName' | grep -v ip_pool | grep -v phone_directo

Object Name: FW1
    location:internal
    osName=Gaia
Object Name: FW2
    location:external
Object Name: Fw3

    location:internal
    osName=Gaia

Furthering analyzing the text based objects_5.0.C file provides info about the query that should be performed to obtain specific objects. ClassName attribute provides the type that should be queried.
For example here is a list of possible types:

• gateway
• host
• cluster_member
• gateway_cluster
• network
• group_with_exception