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.

[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.

[simosghi]

Thank really really much for your interest CrisP.

The specific request is the following:

I already use this dataset, LOG TYPE: TRAFFIC

"

select coalesce(nullifna(`user`),nullifna(`srcname`), nullifna(`srcmac`), ipstr(`srcip`)) as user_src, hostname, catdesc from $log where $filter and hostname is not null group by  hostname, catdesc, user_src having sum($browse_time)>0 order by catdesc asc

"

My customer ask me to reporting the whole link, and I thought to implement the "URL" after the "hostname", but the column is not present the table $log. As you show me, the URL column is present in the $log-filter, is it true?

How can I adjust the dataset for get the result? Thank's

[hzhao_FTNT · Accepted solution]

"url" field is only available in webfilter log, but srcname/srcmac/browse_time only available in traffic log.

please change log type to webfilter and try:

select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, hostname || url as website, catdesc from $log where $filter and hostname is not null group by website, catdesc, user_src order by catdesc asc

[simosghi]

Perfect, this is what I need.

Thank's to all

[CrisP]

Hi, Just for the sake of delving into the log database, I've played a bit with your report. Discard the info if not needed, but for me it was a useful exercise, and I've learned a lot. If you need to use data from several log tables, the first approach is to make an INNER JOIN. In your case, browsing time is stored in $log-traffic, while the URL is stored in $log-webfilter. [Note that the ebtime and ebtime2 fields from the traffic log are both used for recording browsing time, but I couldn't find details about their significance. All I know is that there are default Fortinet datasets for browsing time (using ebtime) and enhanced browsing time (using ebtime2). For now, the variables $browse-time and $browse-time2 yield the same results as ebtime and ebtime2.] As any Postgres SQL tutorial tell us, an INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row. Take a look to the following dataset: select     usr || hostname as uh, usr, hostname || url as site, catdesc, browsetime, bandw, count from (          select srcip as usr, hostname, catdesc, sum(ebtime) as browsetime,                   sum(coalesce(traf.sentbyte, 0) + coalesce(traf.rcvdbyte, 0)) as bandw,

                  count(*) as count          from $log-traffic traf          where ebtime>0          group by srcip, hostname, catdesc          order by srcip, hostname          ) z            inner join (                             select wf.srcip as wusr, wf.hostname as whostname, url                             from $log-webfilter wf                             ) zz                    on wusr || whostname = z.usr || z.hostname                    group by uh, usr, hostname, catdesc, browsetime, bandw, count, url                    order by browsetime desc, bandw desc, count desc Table "z" contains the info we need from $log-traffic, table "zz" from $log-webfilter (just the URL, in our case). The two tables are joined on a key built by concatenating the User (here, source IP for simplicity) and the destination hostname. To understand why, note that the GROUP BY clause returns the rows in the log tables that have identical values in the grouped-by columns. But Postgres, by design, does not return ALL the rows for each combination of GROUP values: you cannot get a list of all the sessions in the traffic log that have a certain pair of srcip and hostname! Instead you can count them (using count(*)), and you can compute things using fields that are not gouped. So, if we used "group by srcip, hostname, catdesc", we get in table "z" a row for each combination (srcip, hostname, catdesc), the total browsing time for this combination, the total traffic volume, and the number of sessions. See, everything outside the GROUP is computed or counted -- you get a lot of error messages in the datasets if you forget this... Unless, of course, if you really want the complete list of rows with identical group values -- but in this case you must group ALL the columns. As we needed all the rows in the table resulting from joining "z" and "zz", we used "group by uh, usr, hostname, catdesc, browsetime, bandw, count, url", so all the columns are gouped. On the other hand, in table "zz" we have a row for every session that each user opened on each hostname, including the URL info. Apparently, for each row in "z" we must have quite a lot of rows in "zz", as each user+hostname+catdesc combination generated a lot of sessions for various URLs, thus gathering the aggregated bandwidth during the aggregated browsing time. Back to the INNER JOIN, we don't need to join $log-traffic with $log-webfilter on the sessionid column (as we can see in FortiView screens, where each selected traffic log displays several associated AppCtrl, WF, AV, IPS logs, all having the same sessionid value, which links them in a session-related security events chain). All we need is to have a list of all the URLs visited by each user+hostname combination. That's the reason we concatenate these columns and join on the combined value. Further on, with this dataset we can obtain a basic chart, but also a drilldown chart. We do not have to bind "uh" to them, this column could be used for ordering, in case we want to organize the output by user/destination pairs. Moreover, the example dataset uses browsetime as the main criterium, but we must build separate datasets if we focus on bandwidth or session counts. This is, unfortunately, caused by the fact that we cannot specify descending order in Charts, so we are forced to multiply datasets instead of using the same dataset with several ordering in the upper levels (charts, maybe even reports runtime filters). Here is a sample output of the report.

https://dl.dropboxusercontent.com/u/33044717/UserHostURL.jpg

https://dl.dropboxusercontent.com/u/33044717/DrilUserURLBrT.jpg

I hope this is not excessive (but I enjoyed it notwithstanding!). Let's also hope that Fortinet shall decide to document a little bit more their great tools.

[hzhao_FTNT]

Hi CrisP,

1. ebtime is calculated from traffic log, but ebtime2 is calculated from webfilter log, if your FOS version is 5.2+, the result should be different.

2. We do not recommend $log join $log, not only concerns performance issue, it will also cause issues when merge multiple hcache tables during report running.

Regards,

hz

[CrisP]

Hello Zhao, and thanks for the details. Do you have some extra info regarding how 'browsing' is defined, compared to session duration? 

I see that JOINS take a lot of time to execute, but they are nevertheless present in some default datasets. For instance, left joins and inner joins appear in almost every apprisk-ctrl-... dataset, which are used in the App Risk and Control standard FAZ template. Maybe Fortinet intend to solve the hcache issues you mention? If not, please let me know asap, as we deliver the ARC report by email to our customers every month, and I would like to avoid getting complaints. We don't check these reports every months, the labor volume is too high.

Thanks again and best regards. (And appologies to simosghi for hijacking this post!)