#Last Change: July 8th, 2010

###########################################################################
# Command to access the database.
# ARGS: <SQL STATEMENT>
###########################################################################
sql () {
        psql -t -q -d opennms -U postgres -c "$1" | grep -v '^$'
}

###########################################################################
# Command to access the database.
# ARGS: <SQL STATEMENT>
###########################################################################
getNodes () {
        sql "select distinct node.nodeid,node.nodelabel from node,ipinterface where nodetype!='D' and \
        node.nodeid=ipinterface.nodeid and ipinterface.ipaddr != '0.0.0.0' $1 order by node.nodelabel;" | \
        sed -e 's/^\(.*\)|.*$/\1/'
}

###########################################################################
# Returns the IPs from an ips-to-monitor file in the current directory.
# It removes any comments and blank lines.
###########################################################################

getIPsFromList () {
        if [ -f ./ips-to-monitor ]; then
                #The next line could also do tr -d "\015" if you wanted to automatically convert Windows txt files to UNIX
                cat ips-to-monitor | grep -v '^#' | grep -v '^$' | sed -e 's/#.*$//'
        else
                cat ${NOCBASE}/views/*/ips-to-monitor | grep -v '^#' | grep -v '^$' | sed -e 's/#.*$//' | sort -u
        fi
}


###########################################################################
# The following function returns a list of nodeids given a list of IPs
# in a file called ips-to-monitor in the current directory.
###########################################################################

#WARNING if this func is run from a directory without an ips-to-moninitor file, it will return all Nodes!
getNodesByIPs () {
	WHERECLAUSE=""
	if [ -f ./ips-to-monitor ]; then
        	IPLIST=`getIPsFromList`
        	if [ "$IPLIST" != "" ]; then
                	WHERECLAUSE="$WHERECLAUSE and ("
                	OR=""
                	for IP in $IPLIST; do
                        	#WHERECLAUSE="$WHERECLAUSE $OR iplike(ipinterface.ipaddr,'$IP')"
                        	WHERECLAUSE="$WHERECLAUSE $OR ipinterface.ipaddr='$IP'"
                        	OR="or"
                	done
                	WHERECLAUSE="$WHERECLAUSE)"
			getNodes "$WHERECLAUSE"
        	fi
		#if there is an ips-to-monitor file but it is empty then return no nodes
	else
		#return all nodes if no ips-to-monitor file is present
		getNodes "$WHERECLAUSE"
	fi
}

###########################################################################
# The following functions returns a list of nodeids given a list of
# surveillance category(s). 
###########################################################################

getWhereForCats () {
	w=""
	if [ "$1" != "" ]; then
        	w="$w and ("
        	o=""
        	for c in $1; do
                	w="$w $o categories.categoryname='$c'"
                	o="or"
        	done
        	w="$w)"
	fi
	echo "$w"
}

#ARGS: "<CAT CAT CAT>"
#In almost all cases only one CAT is given.
getNodesByCat () {
	sql "select distinct node.nodeid,node.nodelabel from node,categories,category_node where nodetype!='D' \
        and node.nodeid=category_node.nodeid and category_node.categoryid=categories.categoryid `getWhereForCats \"$1\"` \
        order by node.nodelabel;" | \
        sed -e 's/^\(.*\)|.*$/\1/'
}

###########################################################################
# Returns node(s) label directly from an IP rather than a nodeid. In 
# most cases only one node label will be returned but it is possisble 
# for multiple if more than one node has the same IP address.
# ARGS: <IP>
###########################################################################
getLabelByIP () {
	sql "select node.nodeLabel from node,ipinterface where ipinterface.ipaddr != '0.0.0.0' \
                        and nodetype!='D' and node.nodeid=ipinterface.nodeid and ipinterface.ipaddr='${1}';" | \
		sed -e 's/^ *//' -e  's/ *$//'
}


###########################################################################
# The following functions take one argument, <nodeid>, and return
# various data about the node.
# ARGS: <nodeid>
###########################################################################

getLabel () { 	
	sql "select node.nodeLabel from node where node.nodeid='${1}';" | \
		sed -e 's/^ *//' -e  's/ *$//'
}

getLabelSrc () {
	sql "select node.nodelabelsource from node where node.nodeid='${1}';" | \
		sed -e 's/^ *\([^ ]*\) *$/\1/' \
		    -e 's/^H$/DNS/' \
		    -e 's/^U$/<b>Manual<\/b>/'  \
		    -e 's/^S$/SNMP/' \
		    -e 's/^A$/IP/'  \
		    -e 's/^N$/NETBIOS/' 
}

getCats () {
	sql "select categories.categoryname from category_node,categories where category_node.nodeid='${1}' and \
		categories.categoryid=category_node.categoryid order by categories.categoryname;" 
}

getCatsCount () {
	sql "select count(categories.categoryname) from category_node,categories where category_node.nodeid='${1}' \
		and categories.categoryid=category_node.categoryid;" 
}

getIPsWithP () {
	sql "select ipinterface.ipaddr,ipinterface.issnmpprimary from node,ipinterface where node.nodeid='${1}' and \
		node.nodeid = ipinterface.nodeid and ipinterface.ipaddr != '0.0.0.0';"
}

getIPs () {
        #sql "select ipinterface.ipaddr from node,ipinterface where node.nodeid='${1}' and \
        #        node.nodeid = ipinterface.nodeid and ipinterface.ipaddr != '0.0.0.0';"
        sql "select ipaddr from ipinterface where \
                nodeid='$1' and ipinterface.ipaddr != '0.0.0.0';" | \
		sed -e 's/^ *\([^ ]*\) *$/\1/'
}

getSrv () {
	s=`sql "select DISTINCT service.servicename from service,ifservices,node where ifservices.status = 'A' and \
		service.serviceid = ifservices.serviceid and node.nodeid = ifservices.nodeid and node.nodeid='${1}';" `

                if [ "$s" = "" ]; then
                        echo "<b>Can not ping.</b>"
		else
			echo "$s"
                fi
}

getLinks () {
	sql "select DISTINCT node.nodelabel from node,datalinkinterface where (datalinkinterface.nodeid='${1}' and \
		node.nodeid=datalinkinterface.nodeparentid) or (datalinkinterface.nodeparentid='${1}' and \
		datalinkinterface.nodeid=node.nodeid);"
}

getCriticalPathIP () {
	sql "select criticalpathip from pathoutage where nodeid='${1}';" 
}

getCriticalPathLabel () {
	sql "select distinct node.nodelabel from node,ipinterface where nodetype!='D' and \
		node.nodeid=ipinterface.nodeid and ipinterface.ipaddr != '0.0.0.0' and \
		iplike(ipinterface.ipaddr,'`getCriticalPathIP ${1}`');" 
}

getSNMPData () {
	sql "select nodesysname,nodesyslocation,nodesyscontact,nodesysdescription from node \
		where node.nodeid='${1}'" | \
		sed -e 's/|/<font color="red">|<\/font>/g'
}

#Counts the number of snmp jrb files that have been updated (modified) for a given node in the last 7 days.
getSNMPFiles () {
	snmpfilescount=`find ${OPENNMSBASE}/share/rrd/snmp/${1}  -mtime -7 -name '*.jrb' | wc -l`
	if [ $snmpfilescount = 0 ]; then
		echo "<b>0</b> (warning! No snmp data from last 7 days.)"
	else
		echo $snmpfilescount
	fi
}