Filter length

[jcostello]

what is the maximum filter length for the log menus on the Monitor tab? what is the maximum length of a query on a report?

I am pretty sure I have exceeded it with the following query:

(app eq unknown-tcp) and (zone.dst eq sample.com) and (port.dst neq 23) and (port.dst neq 1023) and (port.dst neq 1098) and (port.dst neq 1108) and (port.dst neq 1352) and (port.dst neq 1364) and (port.dst neq 1371) and (port.dst neq 1374) and (port.dst neq 2001) and (port.dst neq 2201) and (port.dst neq 3873) and (port.dst neq 4203) and (port.dst neq 5011) and (port.dst neq 5910) and (port.dst neq 9400) and (port.dst neq 13782) and (port.dst neq 27303) and (port.dst neq 27306) and (port.dst neq 27316) and (port.dst neq 27320) and (port.dst neq 27323) and (port.dst neq 27326) and (port.dst neq 27336) and (port.dst neq 27350) and (port.dst neq 27360) and (port.dst neq 27390) and (port.dst neq 27526) and (port.dst neq 35625) and (port.dst neq 42010) and (port.dst neq 20) and (port.dst neq 80) and (port.dst neq 514) and (port.dst neq 695) and (port.dst neq 1001) and (port.dst neq 1022) and (port.dst neq 1035) and (port.dst neq 1052) and (port.dst neq 1078) and (port.dst neq 1080) and (port.dst neq 1085) and (port.dst neq 1141) and (port.dst neq 1201) and (port.dst neq 1223) and (port.dst neq 1227) and (port.dst neq 1229) and (port.dst neq 1232) and (port.dst neq 1247) and (port.dst neq 1258) and (port.dst neq 1261) and (port.dst neq 1271) and (port.dst neq 1299) and (port.dst neq 1307) and (port.dst neq 1308) and (port.dst neq 1312) and (port.dst neq 1317) and (port.dst neq 1340) and (port.dst neq 1366) and (port.dst neq 1406) and (port.dst neq 1411) and (port.dst neq 1432) and (port.dst neq 1442) and (port.dst neq 1449) and (port.dst neq 1451) and (port.dst neq 1469) and (port.dst neq 1473) and (port.dst neq 1504) and (port.dst neq 1510) and (port.dst neq 1523) and (port.dst neq 1533) and (port.dst neq 1564) and (port.dst neq 1569) and (port.dst neq 1579) and (port.dst neq 1587) and (port.dst neq 1592) and (port.dst neq 1601) and (port.dst neq 1608) and (port.dst neq 1644) and (port.dst neq 1654) and (port.dst neq 1661) and (port.dst neq 1669) and (port.dst neq 1683) and (port.dst neq 1686) and (port.dst neq 1691) and (port.dst neq 1707) and (port.dst neq 1717) and (port.dst neq 1718) and (port.dst neq 1725) and (port.dst neq 1736) and (port.dst neq 1777) and (port.dst neq 1782) and (port.dst neq 1787) and (port.dst neq 1825) and (port.dst neq 1833) and (port.dst neq 1836) and (port.dst neq 1842) and (port.dst neq 1843) and (port.dst neq 1856) and (port.dst neq 1873) and (port.dst neq 1879) and (port.dst neq 1884) and (port.dst neq 1910) and (port.dst neq 1915) and (port.dst neq 1939) and (port.dst neq 1941) and (port.dst neq 1958) and (port.dst neq 1992) and (port.dst neq 1994) and (port.dst neq 2023) and (port.dst neq 2063) and (port.dst neq 2081) and (port.dst neq 2199) and (port.dst neq 2204) and (port.dst neq 2241) and (port.dst neq 2250) and (port.dst neq 2307) and (port.dst neq 2314) and (port.dst neq 2319) and (port.dst neq 2321) and (port.dst neq 2327) and (port.dst neq 2329) and (port.dst neq 2347) and (port.dst neq 2354) and (port.dst neq 2368) and (port.dst neq 2379) and (port.dst neq 2399) and (port.dst neq 2402) and (port.dst neq 2439) and (port.dst neq 2446) and (port.dst neq 2454) and (port.dst neq 2458) and (port.dst neq 2494) and (port.dst neq 2515) and (port.dst neq 2531) and (port.dst neq 2548) and (port.dst neq 2601) and (port.dst neq 2613) and (port.dst neq 2620) and (port.dst neq 2631) and (port.dst neq 2660) and (port.dst neq 2680) and (port.dst neq 2689) and (port.dst neq 2697) and (port.dst neq 2708) and (port.dst neq 2765) and (port.dst neq 2771) and (port.dst neq 2792) and (port.dst neq 2847) and (port.dst neq 2851) and (port.dst neq 2866) and (port.dst neq 2902) and (port.dst neq 3018) and (port.dst neq 3047) and (port.dst neq 3076) and (port.dst neq 3089) and (port.dst neq 3105) and (port.dst neq 3110) and (port.dst neq 3138) and (port.dst neq 3199) and (port.dst neq 3203) and (port.dst neq 3208) and (port.dst neq 3224) and (port.dst neq 3234) and (port.dst neq 3243) and (port.dst neq 3245) and (port.dst neq 3254) and (port.dst neq 3255) and (port.dst neq 3261) and (port.dst neq 3262) and (port.dst neq 3263) and (port.dst neq 3277) and (port.dst neq 3303) and (port.dst neq 3320) and (port.dst neq 3389) and (port.dst neq 3411) and (port.dst neq 3447) and (port.dst neq 3473) and (port.dst neq 3481) and (port.dst neq 3484) and (port.dst neq 3515) and (port.dst neq 3532) and (port.dst neq 3569) and (port.dst neq 3580) and (port.dst neq 3627) and (port.dst neq 3656) and (port.dst neq 3678) and (port.dst neq 3680) and (port.dst neq 3704) and (port.dst neq 3709) and (port.dst neq 3726) and (port.dst neq 3736) and (port.dst neq 3761) and (port.dst neq 3833) and (port.dst neq 3838) and (port.dst neq 3840) and (port.dst neq 3843) and (port.dst neq 3844) and (port.dst neq 3853) and (port.dst neq 3868) and (port.dst neq 3919) and (port.dst neq 3936) and (port.dst neq 3954) and (port.dst neq 3981) and (port.dst neq 4002) and (port.dst neq 4004) and (port.dst neq 4007) and (port.dst neq 4016) and (port.dst neq 4050) and (port.dst neq 4051) and (port.dst neq 4056) and (port.dst neq 4058) and (port.dst neq 4063) and (port.dst neq 4072) and (port.dst neq 4080) and (port.dst neq 4084) and (port.dst neq 4119) and (port.dst neq 4125) and (port.dst neq 4165) and (port.dst neq 4182) and (port.dst neq 4197) and (port.dst neq 4222) and (port.dst neq 4223) and (port.dst neq 4236) and (port.dst neq 4245) and (port.dst neq 4253) and (port.dst neq 4260) and (port.dst neq 4265) and (port.dst neq 4267) and (port.dst neq 4277) and (port.dst neq 4286) and (port.dst neq 4310) and (port.dst neq 4311) and (port.dst neq 4314) and (port.dst neq 4318) and (port.dst neq 4321) and (port.dst neq 4329) and (port.dst neq 4347) and (port.dst neq 4351) and (port.dst neq 4372) and (port.dst neq 4380) and (port.dst neq 4390) and (port.dst neq 4401) and (port.dst neq 4407) and (port.dst neq 4411) and (port.dst neq 4430) and (port.dst neq 4444) and (port.dst neq 4464) and (port.dst neq 4470) and (port.dst neq 4479) and (port.dst neq 4485) and (port.dst neq 4488) and (port.dst neq 4505) and (port.dst neq 4546) and (port.dst neq 4547) and (port.dst neq 4552) and (port.dst neq 4558) and (port.dst neq 4578) and (port.dst neq 4590) and (port.dst neq 4596) and (port.dst neq 4613) and (port.dst neq 4620) and (port.dst neq 4624) and (port.dst neq 4639) and (port.dst neq 4650) and (port.dst neq 4659) and (port.dst neq 4664) and (port.dst neq 4676) and (port.dst neq 4697) and (port.dst neq 4711) and (port.dst neq 4739) and (port.dst neq 4744) and (port.dst neq 4747) and (port.dst neq 4767) and (port.dst neq 4824) and (port.dst neq 4864) and (port.dst neq 4869) and (port.dst neq 4880) and (port.dst neq 4883) and (port.dst neq 4980) and (port.dst neq 5007) and (port.dst neq 6768) and (port.dst neq 7001) and (port.dst neq 7500) and (port.dst neq 7920) and (port.dst neq 8000) and (port.dst neq 8001) and (port.dst neq 8471) and (port.dst neq 8562) and (port.dst neq 8592) and (port.dst neq 12557) and (port.dst neq 13011) and (port.dst neq 15010) and (port.dst neq 15230) and (port.dst neq 15240) and (port.dst neq 16001) and (port.dst neq 17250) and (port.dst neq 17260) and (port.dst neq 23010) and (port.dst neq 25572) and (port.dst neq 26422) and (port.dst neq 27000) and (port.dst neq 27003) and (port.dst neq 27010) and (port.dst neq 27300) and (port.dst neq 27330) and (port.dst neq 27460) and (port.dst neq 27480) and (port.dst neq 27600) and (port.dst neq 31001) and (port.dst neq 31018) and (port.dst neq 31538) and (port.dst neq 35621) and (port.dst neq 42000) and (port.dst neq 44714) and (port.dst neq 65129)

When I try to run a report to gather unknown-tcp traffic on ports that have not been identified yet the screen just hangs and eventually my session expires. If I try a query against the logs, I receive a message about invalid XML response.

Any thoughts on how to craft this query so that I can get the infromatioin we need

Thanks

James

[Jamiefitzgerald]

You can do a high level csv export of the (app eq unknown-tcp) and (zone.dst eq sample.com) and (receive_time in last-24-hrs) and then use a spreadsheet application to complete the rest of the filtering.  

[jcostello]

Unfortunately that gives only about 2 hours of data due to the 65335 line limit within excel.

Sent from Samsung mobile

[mikand]

What about load that CSV into a locally installed MySQL database and use your favorite GUI-tool (sqlyog or such) to perform the rest of the query (or the CLI directly)?

The load part is fairly simple, the tricky part might be to construct a table with proper columntypes for each column.

The below is example for how to load GeoIP csv from maxmind.com:

DROP TABLE IF EXISTS EXAMPLE.T_Geoip_Csv;

CREATE TABLE EXAMPLE.T_Geoip_Csv (
`GeoipStartIp` varchar(15) NOT NULL DEFAULT '',
`GeoipEndIp` varchar(15) NOT NULL DEFAULT '',
`GeoipStart` int UNSIGNED NOT NULL DEFAULT '0',
`GeoipEnd` int UNSIGNED NOT NULL DEFAULT '0',
`GeoipCC` char(2) NOT NULL DEFAULT '',
`GeoipCN` varchar(50) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOAD DATA INFILE "/opt/GeoIPCountryWhois.csv"
INTO TABLE EXAMPLE.T_Geoip_Csv
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY "\""
LINES TERMINATED BY "\n";

Regarding Excel Microsoft claims that total number of rows is now >1 million (without specifying it further) since 2007.

Libreoffice has a row limit of 1048576 since v3.3 but that might still be too few rows so I think the best option is to load the data into a mysql db and pick out what you need from there (unless you can operate directly on the csv file with some sed kung-fu).