Skip to main content
simosghi
simosghi
New Member
June 1, 2016
Solved

URL field

  • June 1, 2016
  • 2 replies
  • 17941 views

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

Best answer by CrisP

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

 

2 replies

CrisP
CrisP
New Member
June 2, 2016

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
CrisPAnswer
New Member
June 2, 2016

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
CrisP
New Member
June 2, 2016

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
simosghiAuthor
New Member
June 2, 2016

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

Terms & ConditionsAccessibility statement