Trying to get clickable URL list and then filter by user to get list of visited URLs

[stretchie]

Hi folks,

I'm trying to use FortiAnalyzer 6.2 to get a dataset of clickable URLs from a Fortigate 6.0.x that we can display in a table report which at report runtime is filtered by user. The aim is to get a table of all the URLs that the user has visited. We have web filtering enabled with deep inspection enabled for the domain joined PCs.  I can see the user field in the web filtering logs, suggesting the FSSO is working correctly.

When I just have the dataset as

select * from $log 

I can see all the different usernames, I'd like to filter them and manipulate the data as below, specifically combine the hostname and url and make it a clickable link.

So far I have got this dataset (against web filtering log):

select dtime, user, hostname||url as fullurl, concat(   '<a href="'    ,  '[link]https://', [/link] hostname, url,    '">',    concat(hostname, '</a>')) as clickable_url, sentbyte, rcvdbyte, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, catdesc from $log where $filter group by dtime, user, hostname, url, sentbyte, rcvdbyte, catdesc 

When testing this it looks OK, I get a page of data with what appears to be the right data but the username is all 'postgres'. No problems, I guess once we filter the data per username it'll come out in the report. Nothing is reported, even with no filters in the report. It seems the whole dataset once I select anything other than select * it just sets the user field to postgres??!!

The resultant chart should be pretty straight forward, just gets each of the constructed columns (dtime, user, clickable_url, sentbyte, rcvdbyte, bandwidth, catdesc) but wiht the dataset reporting only postgres as the user, this won't work in it's current format.

I'm really scratching my head here and have spent many hours trying to solve this, anyone got any ideas?

Thanks

Brenton

[stretchie]

I've gotten a little further and now have a dataset that has corect usernames rather than just all 'postgres' as the username. THe new dataset is:

select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src,to_timestamp(`itime`)::timestamp with time zone as time,concat(   '<a href="'    ,  '[link]https://', [/link] `hostname`, `url`,    '">',    concat(`hostname`, '</a>')) as clickable_url,`sentbyte`, `rcvdbyte`, `catdesc` from $log where $filter

However, the chart is blank when I chart this dataset, even with no filters. Any ideas?