vteams helped out a client to fetch the data from Untangle Server through PHP. The client wanted to achieve this by creating a crawler which could gather the data from the server. However, vteams engineer knew that this is not a viable solution and hence an alternative solution was proposed.

Solution

The first step was to analyze the PSQL database structure to figure out how Untangle Server saves its data. To do this, vteams engineer enabled a port in Untangle unit to access the database remotely (Read the reference link to learn how to enable ports in Untangle unit).

Enabled port allowed PSQL database to connect remotely with pgAdmin. PSQL database was then installed in the system using SSH tunnel. Example given below:

In order to fetch the data from PSQL and push it to the web server, vteams engineers created the application in Java. Java was the preferred choice for this because Java JRE (Java Runtime Environment) already comes with Untangle unit which meant that no further installations were required on the system.

Timer functionality was then implemented in Java application so that the data could be pushed to the web service according to the configurable time.  Once the data was received by the web service, it was then saved in the client’s database for future processing.

Following are the sample codes:

1- For Connecting Database

connection = DriverManager.getConnection("jdbc:postgresql://"+hostname+"/"+db, user,pass);

2- For Fetching the Data from Table

ResultSet rs = st.executeQuery("SELECT * FROM reports.http_events where time_stamp between '"+previousTime+"' and  '"+currentTime+"' ");
                
Statement stCount = (Statement) connection.createStatement();
ResultSet countQuery = stCount.executeQuery("SELECT count(*) FROM reports.http_events where  time_stamp between '"+previousTime+".0' and  '"+currentTime+".0' ");
countQuery.next();
int totalRows = countQuery.getInt(1);
System.out.println("Total Rcords = "+totalRows+" Application Running Last update on "+currentTime);
String data = "";                
WebEvents events = new WebEvents();
while ( rs.next() ){
	events.setuserID(userID);
      events.setTimeStamp(rs.getTimestamp("time_stamp"));
      events.setCClientAddr(rs.getString("c_client_addr"));
      events.setUsername(rs.getString("username"));
      events.setHost(rs.getString("host"));
      events.setHostName(rs.getString("hostname"));
      events.setRequestID(rs.getLong("request_id"));
      events.setUri(rs.getString("uri"));
      Gson gson = new Gson();  
      data+= gson.toJson(events)+",,";
}

3- For Sending Data to Web Services

private void sendPost(String data) throws Exception {
        
        
        String url = webUrl;
        URL obj = new URL(url);
        HttpURLConnection con = (HttpURLConnection) obj.openConnection();

        //add reuqest header
        con.setRequestMethod("POST");
        con.setRequestProperty("User-Agent", USER_AGENT);
        con.setRequestProperty("Accept-Language", "en-US,en;q=0.5");

        String urlParameters = "data="+data;

        // Send post request
        con.setDoOutput(true);
        DataOutputStream wr = new DataOutputStream(con.getOutputStream());
        wr.writeBytes(urlParameters);
        wr.flush();
        wr.close();

        int responseCode = con.getResponseCode();
        
        if(responseCode == 404){
            System.out.println("Web Url is not correct");
        }

        BufferedReader in = new BufferedReader(
                new InputStreamReader(con.getInputStream()));
        String inputLine = "";
        String result = "";
        StringBuffer response = new StringBuffer();

        while ((inputLine = in.readLine()) != null) {
            response.append(inputLine);
        }
        result = response.toString().trim();
        
        if(!result.equals("ok")){
            System.out.println("Web Url is not correct");
            
        }
        in.close();

    }