Merge branch 'master' of github.com:tech-server/tgmanage

2 files changed, 144 insertions, 42 deletions

diff --git a/tools/cubemap-stats.pl b/tools/cubemap-stats.pl
index c6241ab..401424a 100755
--- a/tools/cubemap-stats.pl
+++ b/tools/cubemap-stats.pl
@@ -1,63 +1,130 @@
-#! /usr/bin/perl
+#!/usr/bin/perl
 use strict;
 use warnings;
 use POSIX qw(strftime);
+use NetAddr::IP;
+use Net::IP;
 
-my $stats_filename = "/Users/jocke/Desktop/cubemap-tg15-access.log";
+my (%streams, %ips, %total);
+$total{count}{c} = 0;
+$total{unique_count}{c} = 0;
+$total{count}{int} = 0;
+$total{unique_count}{int} = 0;
+$total{count}{ext} = 0;
+$total{unique_count}{ext} = 0;
 
-my (%streams, %ips);
-my $total = 0;
-my $unique = 1;
+sub stream_name {
+	my $stream = shift;
+	$stream =~ s/\///g;
+	return $stream;
+}
+
+# Is client in the network?
+sub is_in_network{
+	my ($ip, $ipv4, $ipv6) = @_;
+	my $in_scope = 0;
+	my $ipv4_range = NetAddr::IP->new($ipv4);
+	my $ipv6_range = NetAddr::IP->new($ipv6);
+	
+	if (Net::IP->new($ip)->ip_is_ipv4()){
+		if (NetAddr::IP->new($ip)->within($ipv4_range)){
+			$in_scope = 1;
+		}
+	} else {
+		if (NetAddr::IP->new($ip)->within($ipv6_range)){
+			$in_scope = 1;
+		}
+	}
+	
+	return $in_scope;
+}
+
+# add count
+sub add_count{
+	my ($date, $stream_name, $count_name, $count_type) = @_;
+
+	if($streams{$date}{$stream_name}{$count_name}{$count_type}){
+		$streams{$date}{$stream_name}{$count_name}{$count_type}++;
+	} else {
+		$streams{$date}{$stream_name}{$count_name}{$count_type} = 1;
+	}
+}
 
-open my $stats, "<", $stats_filename
-	or die "$stats_filename: $!";
-while (<$stats>) {
+sub print_info{
+	foreach my $date (sort keys %streams) {
+		print "### $date\n";
+		foreach my $stream (sort keys %{$streams{$date}}){
+			my $stream_name = stream_name($stream);
+			printf "\t%s: %s (%s) - Int: %s (%s), Ext: %s (%s)\n",
+				$stream_name,
+				$streams{$date}{$stream}{count}{c},
+				$streams{$date}{$stream}{unique_count}{c},
+				$streams{$date}{$stream}{count}{int},
+				$streams{$date}{$stream}{unique_count}{int},
+				$streams{$date}{$stream}{count}{ext},
+				$streams{$date}{$stream}{unique_count}{ext},
+		}
+	}
+	print "\n\nTotal: $total{count}{c} ($total{unique_count}{c})\n";
+	print "Internal: $total{count}{int} ($total{unique_count}{int})\n";
+	print "External: $total{count}{ext} ($total{unique_count}{ext})\n";
+}
+
+while (<STDIN>) {
 	chomp;
 	my ($epoch, $ip, $stream, $connected_time, $bytes_sent, $loss_bytes, $loss_events) = /^(\d+) (\S+) (\S+) (\d+) (\d+) (\d+) (\d+)/ or next;
+
+	next if ($stream =~ m/-/);
+	next if ($stream =~ m/test/);
 	
 	my $stream_name = stream_name($stream);
 	
 	my $date = strftime("%d %b %Y", localtime($epoch));
+
+	my $internal = is_in_network($ip, '151.216.128.0/17', '2a02:ed02::/32');
+	unless($internal){
+		# check server /24
+		$internal = is_in_network($ip, '185.12.59.0/24', '2a02:ed02::/32');
+	}
 	
-	if($unique){
-		if($ips{$date}{$ip}){
-			# already viewed this day, skip
-			next;
+	print "$date, $stream_name, $ip, $internal\n";
+
+	if($ips{$date}{$ip}){
+		# already viewed this day
+
+		add_count($date, $stream_name, 'count', 'c');
+
+		if($internal){
+			add_count($date, $stream_name, 'count', 'int');
+			$total{count}{int}++;
 		} else {
-			# not viewed this day, add
-			$ips{$date}{$ip} = 1;
-			
-			if($streams{$date}{$stream_name}{count}){
-				$streams{$date}{$stream_name}{count}++;
-			} else {
-				$streams{$date}{$stream_name}{count} = 1;
-			}
-			$total++;
+			add_count($date, $stream_name, 'count', 'ext');
+			$total{count}{ext}++;	
 		}
+		
+		$total{count}{c}++;
 	} else {
-		if($streams{$date}{$stream_name}{count}){
-			$streams{$date}{$stream_name}{count}++;
+		# not viewed this day
+		$ips{$date}{$ip} = 1;
+		
+		add_count($date, $stream_name, 'count', 'c');
+		add_count($date, $stream_name, 'unique_count', 'c');
+		
+		if($internal){
+			add_count($date, $stream_name, 'count', 'int');
+			add_count($date, $stream_name, 'unique_count', 'int');
+			$total{count}{int}++;
+			$total{unique_count}{int}++;
 		} else {
-			$streams{$date}{$stream_name}{count} = 1;
+			add_count($date, $stream_name, 'count', 'ext');
+			add_count($date, $stream_name, 'unique_count', 'ext');
+			$total{count}{ext}++;
+			$total{unique_count}{ext}++;
 		}
-		$total++;
-	}
-}
-close $stats;
-
-foreach my $date (sort keys %streams) {
-	print "### $date\n";
-	foreach my $stream (sort keys %{$streams{$date}}){
-		next if ($stream =~ m/-/);
-		next if ($stream =~ m/test/);
-		my $stream_name = stream_name($stream);
-		print "\t$stream_name: $streams{$date}{$stream}{count}\n";
+
+		$total{count}{c}++;
+		$total{unique_count}{c}++;
 	}
 }
-print "\n\nTotal: $total\n";
 
-sub stream_name {
-	my $stream = shift;
-	$stream =~ s/\///g;
-	return $stream;
-}
+print_info();
\ No newline at end of file
diff --git a/tools/sql-strip.pl b/tools/sql-strip.pl
new file mode 100644
index 0000000..0e2fec6
--- /dev/null
+++ b/tools/sql-strip.pl
@@ -0,0 +1,35 @@
+#!/usr/bin/perl
+use warnings;
+use strict;
+
+my $ignore = "((([0-9a-f]{2}[:]){5}[0-9a-f]{2})|";
+$ignore .= "([0-9]{4}\-[0-9]{2}\-[0-9]{2} [0-9]{2}\:[0-9]{2}\:[0-9]{2})";
+$ignore .= ").*";
+
+my $community = "<removed>";
+my $snmpv3 = 'SHA/<removed>/AES/<removed>';
+
+my $skip = 0;
+
+open (SQL, $ARGV[0]) or die "Unable to open SQL-file";
+while (<SQL>) {
+	unless (/^$ignore$/){
+		
+		if (/COPY (linknet_ping|ping|mbd_log|squeue|temppoll|ap_poll|polls)/){
+			$skip = 1;
+			print;
+		}
+
+		if (/\\\./){
+			$skip = 0;
+		}
+
+		unless ($skip){
+			s/$community/<removed>/g; # community
+			s/PASSWORD '.+'/PASSWORD '<removed>'/g; # password for SQL-users
+			s/public$/<removed>/; # public-community -- assuming last column
+			#s/$snmpv3/SHA\/<removed>\/AES\/<removed>/g; # snmpv3
+			print;
+		}
+	}
+}