0

I'm searching for a new options all days and I'm making a new Word file with all the information I get but I can't reach a conclusion.

I'll explain the main idea.

My idea:
User (Employee, client) => Firewall (VPN or Proxy) => Office Network

What should I use?
PFSense, Proxie, Ubuntu + OpenVPN or ??

What I want to track/monitor?
CLOUD - Local Machine with Address IP: 192.168.x.x
Database

I want to monitor all the things they have done at our cloud and the querys or changes on database. And I want this changes and logs in one file or database.

Currently using AZURE and its a little bit hard for me to configure it right with pfsense.

Is it possible to structure something like this?

Dave M
  • 4,494
  • 21
  • 30
  • 30
Henrique Mota
  • 1

1 Answers1

0

Use tcpdump, and store what you need in some log files. To monitor queries to database you can use for example this script.
source -> https://liferay.dev/blogs/-/blogs/how-to-catch-mysql-sql-with-tcpdump-in-linux/maximized

#!/bin/bash
tcpdump -i your_network_interface_name -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
{
    if (defined $q) { print "$q\n"; }
    $q=$_;
} else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
}

}'

You can even specify source and destination IP which you need to monitor.

user556886
  • 111
  • 2
  • Thats nice!! What about cloud? – Henrique Mota Jan 23 '20 at 15:06
  • Is there any option that I can add to that tcpdump to check what ip did what? Or name? – Henrique Mota Jan 23 '20 at 16:21
  • You can modify tcpdump command to listen only for specific IP address, like this tcpdump -i your_interface -s 0 -l -w - host ip_address and dst port 3306 – user556886 Jan 24 '20 at 07:21
  • Hmm.. thats not bad. But I don't want a single address I want to monitor atleast 2 people that do querys everyday, how should I do that? And i'll run a script to keep executing that tcpdump every 24h – Henrique Mota Jan 24 '20 at 09:51
  • You can give them access via openvpn for example , where you can define for this two account specific local IP address on server. Then you will be able to identify them, and monitor this Ip . Openvpn can also be a trigger , if they will connect successful , then your script should start automatically, and keep logs peer account=Ip . :) – user556886 Jan 24 '20 at 09:58