Sockets vs Process

#!/bin/bash
function TEMPFILE() {
	case $1 in
	create)
		mktemp -p /tmp --suffix tmpdoc
		;;
	delete)
		rm  -f $2
		;;
	*)
		EXITNOW "could not create temporary file"
		;;
	esac
}
function ALLTOCSV() {
  INPUT=$1
  cat *.csv > $INPUT
}
function PARSE() {
  export INPUT=$1
  export LOCAL_PORT_LISTENING=$(TEMPFILE create)

  #Get the ports for the listening services
  cat $INPUT | grep -v ^\# | \
  while IFS=',' read HOSTNAME PUBLIC_IP STATE LOCAL_ADDRESS LOCAL_PORT REMOTE_ADDRESS REMOTE_PORT PROCESS_ID PROCESS_NAME OS_VERSION PROCESSOR_COUNT RAM_MEMORY_KB
  do
    echo $HOSTNAME
  done | sort -u | \
  while IFS=',' read SERVERNAME
  do
    cat $1 | grep -v ^\# | \
    while IFS=',' read HOSTNAME PUBLIC_IP STATE LOCAL_ADDRESS LOCAL_PORT REMOTE_ADDRESS REMOTE_PORT PROCESS_ID PROCESS_NAME OS_VERSION PROCESSOR_COUNT RAM_MEMORY_KB
    do
      if [[ ( "$SERVERNAME" == "$HOSTNAME" ) && ( "$STATE" == "LISTENING" )  && ( "$LOCAL_ADDRESS" != "127.0.0.1" ) ]]
      then
        echo $HOSTNAME $LOCAL_PORT
      fi
    done | sort -u -n -k2
  done > $LOCAL_PORT_LISTENING

  # Show only established sockets
  cat $INPUT | grep -v ^\# | \
  while IFS=',' read HOSTNAME PUBLIC_IP STATE LOCAL_ADDRESS LOCAL_PORT REMOTE_ADDRESS REMOTE_PORT PROCESS_ID PROCESS_NAME OS_VERSION PROCESSOR_COUNT RAM_MEMORY_KB
  do
    while read SERVERNAME PORT
    do
      #Show established sockets with listener process on the server, server side
      if [[ ( "$SERVERNAME" == "$HOSTNAME" ) && ( $PORT -eq $LOCAL_PORT ) && ( "$STATE" == "ESTABLISHED" ) && ( "$LOCAL_ADDRESS" != "$REMOTE_ADDRESS" ) && ( "$PUBLIC_IP" != "$REMOTE_ADDRESS" ) ]]
      then
        echo $HOSTNAME LOCAL $LOCAL_PORT $REMOTE_ADDRESS $PROCESS_NAME
      fi
      #Sow eatablished sockets withot listener, client side
      #PORT diferente local_port
      RETORNO=$(grep -w $LOCAL_PORT $LOCAL_PORT_LISTENING | wc -l)
      if [[ ( "$SERVERNAME" == "$HOSTNAME" ) && ( $RETORNO == 0 ) && ( "$STATE" == "ESTABLISHED" ) && ( "$LOCAL_ADDRESS" != "$REMOTE_ADDRESS" )  && ( "$PUBLIC_IP" == "$REMOTE_ADDRESS" )  ]]
      then

        echo $HOSTNAME REMOTE $REMOTE_PORT $REMOTE_ADDRESS $PROCESS_NAME
      fi
    done < $LOCAL_PORT_LISTENING
  done | sort -u
  # cat $LOCAL_PORT_LISTENING
  TEMPFILE delete $LOCAL_PORT_LISTENING
}
function SQLITE_START() {
  #HOSTNAME PUBLIC_IP STATE LOCAL_ADDRESS LOCAL_PORT REMOTE_ADDRESS REMOTE_PORT PROCESS_ID PROCESS_NAME OS_VERSION PROCESSOR_COUNT RAM_MEMORY_KB
  sqlite3 $BASE "CREATE TABLE IF NOT EXISTS arquitetura(
    HOSTNAME text,
    PUBLIC_IP text,
    STATE text,
    LOCAL_ADDRESS text,
    LOCAL_PORT integer,
    REMOTE_ADDRESS text,
    REMOTE_PORT integer,
    PROCESS_ID integer,
    PROCESS_NAME text,
    OS_VERSION text,
    PROCESSOR_COUNT integer,
    RAM_MEMORY_KB real)"
  # sqlite3 base.sb "create table IF NOT EXISTS hosts(endereco varchar(15) , nome varchar(60) primary key,TTL integer, tipo varchar(30))"
}
function SQLITE_INSERT() {
  INPUT=$1
  export COUNTER=0
  wc -l $INPUT
  cat $INPUT | grep -v ^\# | \
  while IFS=',' read HOSTNAME PUBLIC_IP STATE LOCAL_ADDRESS LOCAL_PORT REMOTE_ADDRESS REMOTE_PORT PROCESS_ID PROCESS_NAME OS_VERSION PROCESSOR_COUNT RAM_MEMORY_KB
  do
    echo -ne "\rInserted $COUNTER Record(s)"
    export COUNTER=$(expr $COUNTER + 1)
    sqlite3 $BASE "INSERT INTO arquitetura(HOSTNAME,PUBLIC_IP,STATE,LOCAL_ADDRESS,LOCAL_PORT,REMOTE_ADDRESS,REMOTE_PORT,PROCESS_ID,PROCESS_NAME,OS_VERSION,PROCESSOR_COUNT,RAM_MEMORY_KB)  VALUES('$HOSTNAME', '$PUBLIC_IP', '$STATE', '$LOCAL_ADDRESS', $LOCAL_PORT, '$REMOTE_ADDRESS', $REMOTE_PORT, $PROCESS_ID, '$PROCESS_NAME', '$OS_VERSION', $PROCESSOR_COUNT, $RAM_MEMORY_KB)"
  done
}
function SQLITE_SELECT() {
  # WHERE STATE == 'ESTABLIHED' AND LOCAL_ADDRESS <> '127.0.0.1'
  sqlite3 $BASE -csv "$1"
}
function SQLITE_PURGE() {
  rm -fr $BASE
}
function SERVER() {
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      REMOTE_ADDRESS,
      LOCAL_ADDRESS
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT NOT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'REMOTE_ADDRESS'"
}
function CLIENT() {
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      LOCAL_ADDRESS,
      REMOTE_ADDRESS
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'REMOTE_ADDRESS'"
}
function ESTABLISHED() {
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      'SERVER',
      PROCESS_NAME,
      PUBLIC_IP,
      LOCAL_ADDRESS,
      LOCAL_PORT,
      REMOTE_ADDRESS,
      REMOTE_PORT
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'REMOTE_PORT'"
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      'CLIENT',
      PROCESS_NAME,
      PUBLIC_IP,
      LOCAL_ADDRESS,
      LOCAL_PORT,
      REMOTE_ADDRESS,
      REMOTE_PORT
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT NOT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'LOCAL_PORT'"
}
function SERVER_PORT() {
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      REMOTE_ADDRESS,
      REMOTE_PORT,
      PROCESS_NAME
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT NOT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'REMOTE_PORT'"
}
function CLIENT_PORT() {
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      REMOTE_ADDRESS,
      LOCAL_PORT,
      PROCESS_NAME
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'REMOTE_ADDRESS'"
}
function INVENTORY() {
  SQLITE_SELECT "
    SELECT DISTINCT
      HOSTNAME,
      PUBLIC_IP,
      PROCESSOR_COUNT,
      CAST((RAM_MEMORY_KB/1024/1024) AS INT),
      OS_VERSION
    FROM
      arquitetura
    ORDER BY
      HOSTNAME"
}
function UNMAPPED_HOSTS() {
  SQLITE_SELECT "
    SELECT DISTINCT
      REMOTE_ADDRESS
    FROM
      arquitetura
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    AND
      REMOTE_ADDRESS <> '0.0.0.0'
    AND
      REMOTE_ADDRESS NOT IN (
        SELECT DISTINCT
          PUBLIC_IP
        FROM
          arquitetura
      )"
}
function PREGRAPH() {
  SQLITE_SELECT "
  SELECT DISTINCT
      c.HOSTNAME,
      c.PUBLIC_IP,
      c.PROCESS_NAME,
      (select HOSTNAME from arquitetura as d where c.REMOTE_ADDRESS == d.PUBLIC_IP),
      c.REMOTE_ADDRESS
    FROM
      arquitetura as c
    WHERE
      STATE == 'ESTABLISHED'
    AND
      LOCAL_PORT NOT IN (
        SELECT DISTINCT
          LOCAL_PORT
        FROM
          arquitetura
        WHERE
          STATE == 'LISTENING'
        AND
          LOCAL_ADDRESS <> '127.0.0.1'
          )
    AND
      LOCAL_ADDRESS <> '127.0.0.1'
    ORDER BY
      'LOCAL_PORT'"
}
if [ $# -ne 1 ]
then
  exit 1
else
  export INPUT=$(TEMPFILE create)
  export BASE=base.db
  ALLTOCSV $INPUT
  dos2unix $INPUT >&2
  case $1 in
    zerado)
      SQLITE_PURGE
      SQLITE_START
      SQLITE_INSERT $INPUT
      ;;
    coletar)
      SQLITE_START
      SQLITE_INSERT $INPUT
      ;;
    established)
      ESTABLISHED
      ;;
    server_port)
      SERVER_PORT
      ;;
    server)
      SERVER
      ;;
    client)
      CLIENT
      ;;
    client_port)
      CLIENT_PORT
      ;;
    server_client)
      SERVER
      CLIENT
      ;;
    inventario)
      INVENTORY
      ;;
    unmapped_hosts)
      UNMAPPED_HOSTS
      ;;
    pregraph)
      PREGRAPH
      ;;
    *)

      exit 1
      ;;
  esac
  # PARSE $INPUT
  # SQLITE_START
  # SQLITE_INSERT $INPUT
  TEMPFILE delete $INPUT
fi