URL field

[simosghi]

I tried to generate a report within URL field but i'm not able to looking for the right query to use in the dataset.

At the log View I see the URL field.

Someone can help me?

Thank's

[CrisP · Accepted solution]

Hi

What do you mean by 'report within url field'?

Using a dataset like 'select srcip, dstip, url from $log-webfilter where $filter group by srcip, dstip order by srcip' can yield ugly results, like:

10.10.3.234    191.232.139.124    / 10.10.3.236    52.17.119.94    /p/ 10.10.3.236    52.48.150.15    /b/?action=pos_config&postype=1&mid=104&posid=&cver=51234047&lan=ro_RO&v=15&sdkv=2&ch=200203&mcc=&androidid=88 10.10.3.236    52.48.150.15    /b/?action=pos_config&postype=1&mid=201&posid=&cver=5152005&lan=RO_ro&v=20&sdkv=3.9 10.10.3.236    54.215.190.20    /batterydoctor/getversions.php 10.10.3.236    54.244.22.196    / 10.10.3.236    114.112.93.204    /sj/data.gif?root=0&prodid=2&mac=54%3A27%3A58%3Ab2%3A49%3Ae2&kong=0&mode=Tab2A7-20F&andver=4.4.2&androidid= 10.10.3.236    172.217.16.202    / 10.10.3.236    216.58.209.174    / 10.10.3.236    216.58.209.206    /

[CrisP · Accepted solution]

Here is a query that I find useful, because you can actually find out not just the url, but also the virus-infected file:

select ipstr(srcip) as User, filename as Infected, url, ipstr(dstip) as dst, virus, crlevel, count(*) as cnt from $log where direction='incoming' and action!='blocked' group by filename, ipstr(srcip), ipstr(dstip), url, virus, crlevel order by ipstr(srcip) desc, filename asc

And the output looks like (I replaced real IP addresses with UserX):

user    infected    url    dst    virus    crlevel    cnt ================================================= User1    2.php    [link]http://blogulmeu.top/adv/2.php  [/link]  31.14.22.28    JS/Redir.NV!tr    critical    1 User2    l.js    [link]http://www.muzica.com/js/l.js  [/link]  104.28.7.114    JS/FBJack.A!tr    critical    1 User3    56e2a4a792fa9.apk    http://cncdn.apiv6.com/sp...792fa9.apk    104.20.50.145    Android/Agent.VC!tr    critical    1 User3    F5_0524GZ.apk    http://down.abcvipcdn.com...0524GZ.apk    104.27.173.154    Android/Hiddad.G!tr    critical    1 User3    ISample5koko0525.apk    http://gt.yepodjr.com/4/I...ko0525.apk    104.27.160.90    Android/Rootnik.AP!tr    critical    2 User3    is0524_1116.jar    http://down.upgamecdn.com...4_1116.jar    104.25.104.26    Android/Qysly.S!tr    critical    4 User3    is0524_1116.jar    http://down.upgamecdn.com...4_1116.jar    104.25.105.26    Android/Qysly.S!tr    critical    6 User3    pushr201604142021.jar    http://apk.cs9adv.com/upl...142021.jar    104.27.152.185    Android/Agent.PB!tr    critical    3 User3    pushr201604142021.jar    http://apk.cs9adv.com/upl...142021.jar    104.27.153.185    Android/Agent.PB!tr    critical    2 User4    Sexygirl20160525.apk    http://apk.cs9adv.com/upl...160525.apk    104.27.153.185    Android/Agent.RN!tr    critical    1 User4    rp-1.8.apk    http://risechen.b0.upaiyu...rp-1.8.apk    77.67.51.98    PossibleThreat.P1    critical    1

[CrisP · Accepted solution]

Sorry, I forgot to mention:

$log works when you select Log Type=Virus in the dataset.

When you operate with several kind of logs in the same dataset, you must use the following constants to specify them:

$log-attack for IPS

$log-webfilter for Web Filter

$log-app-ctrl for App Control

$log-virus for AntiVirus

$log-traffic for traffic logs

If you want to find out all the information in each log type, create a test dataset query using 'select * from $log', push the Test button and you'll get all the table columns (copy-paste them in an Excel file, it helps!). Regarding the possible values in each column, this is a different story, as those values are not documented and you must browse a lot of logs to get all the possible variants. Moreover, this is very important in queries, as you must know what to select.

For instance, you need to select the correct value for each logtype, from 'block', 'blocked', 'drop-session', 'passthrough', 'allow', 'pass', 'dropped', if you want to create different detail graphs or tables for sessions that passed or were blocked by the firewall.

Traffic logs are the main entrance, as you can see in the field 'utmaction' (allow/block) if the session was finally blocked or not, and why. The fields 'countweb, countapp, countav, countips' contain the number of detected security events for each session. So if you see countweb=1 and countapp=1, then you know that the WF module detected a site category, and the App Control module detected an application. Then some details are captured in the traffic log (e.g. catdesc, appcat, appact -- site and app category, app action taken pass or blocked), while others (like the direction incoming/outgoing of the offending event) are stored only in the specialized logs. In the field 'threats' of the traffic log you can see the array of security events.

All the log table columns values can be used in FortiView for queries.

Examples of FortiView lines in the Search field (a space means AND operator):

srcip=10.1.* countav>1 utmaction=block (use in traffic logs for users in subnet 10.1.0.0/16 with viruses detected)

-appcat=Botnet action=block (use in appctrl logs, get all non-Botnet apps that were blocked)

direction=incoming action=monitored (use in Virus logs, get all incoming viruses that passed)

direction=incoming action=detected (use in IPS to get incoming attacks that passed)

etc.

[hzhao_FTNT]

Hi CrisP, As I known, ebtime/ebtime2 is calculated in FortiLogD, before insert into DB. It will add all duration time, and then deduct overlap parts.

For join issue, we do have some dataset using join, but never use $log join $log directly, most of them are $log join mdata table. If you do need this kind of join, you need create 2 temporary tables, pls see reputation-Top-Devices-With-Increased-Scores for reference. 

[CrisP]

I took a quick look, but my example is not using $log JOIN $log. It is using $log-traffic JOIN $log-webfilter.

Do we have the same problems like $log/$log? I see that in reputation-... the first temporary table uses "from $log where $pre_period $filter" ($pre_period? whatever this means... maybe the aggregated raw logs inserted into SQL?), versus the second temp table using "from $log where $filter" (maybe the raw logs not yet inserted into SQL?). In our case, $log-traffic joining $log-webfilter can be expected to simply work with the already SQL-inserted logs?

Thanks

[hzhao_FTNT]

Hi CrisP,

"$log join $log" means $log-traffic JOIN $log-webfilter or other log tables.

For the reference dataset, I am just going to show a way to create temporary table in this kind of issue. For example, your query can be written as below:

DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE temporary TABLE t1 AS ###( SELECT srcip, hostname, catdesc, sum($browse_time) AS browsetime, sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandw, count(*) AS count FROM $log-traffic WHERE ebtime>0 GROUP BY srcip, hostname, catdesc ORDER BY srcip, hostname)###; CREATE temporary TABLE t2 AS ###( SELECT srcip, hostname, url FROM $log-webfilter GROUP BY srcip, hostname, url)###; SELECT t1.srcip AS usr, t1.hostname, t2.url, t1.catdesc, Sum(t1.browsetime) AS browsetime, Sum(bandw) AS bandw, Sum(t1.count) AS COUNT FROM t1 INNER JOIN t2 ON t1.hostname=t2.hostname GROUP BY t1.srcip, t1.hostname, t2.url, t1.catdesc ORDER BY browsetime DESC

[simosghi]

don't worry CrisP, all the information will be useful for everyone.

Thank's