Add source IP address to Analyser Dataset

[NetMan_Bath]

Hi,
I use the below query to provide a summary of every user that hits the categories identified in the list. I would be very grateful if anyone knows of a way to include the Source IP address as one of the returned columns. Given that lots of our users have more than one device, it would mean I wouldn't have to go back to the Analyzer for confirmation.

Kind regards,
NetMan_Bath

select
from_dtime(dtime) as timestamp, user_src, catdesc, hostname as website,
action as status, sum(bandwidth) as bandwidth
from
###(
select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src,
dtime, catdesc, hostname, utmaction as action,
sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth
from $log-traffic
where $filter and
hostname is not null and
logid_to_int(logid) not in (4, 7, 14) and ((logver>=52 and countweb>0)
or
((logver is null) and utmevent in ('webfilter', 'banned-word', 'web-content',
'command-block', 'script-filter'))) and (catdesc='Child Abuse' or catdesc='Child **bleep**ual Abuse' or catdesc='Potentially Unwanted Program' or catdesc='Crypto Mining' or catdesc='Nudity and Risque' or catdesc='Unrated' or catdesc='Extremist Groups' or catdesc='Explicit Violence' or catdesc='Drug Abuse' or catdesc='Other Adult Materials' or catdesc='Gambling' or catdesc='Dating' or catdesc='Malicious Websites' or catdesc='Discrimination' or catdesc='Hacking' or catdesc='Illegal or Unethical' or catdesc='Abortion' or catdesc='Marijuana' or catdesc='**bleep**ography' or catdesc='Sports Hunting and War Games' or catdesc='Plagiarism' or catdesc='Phishing' or catdesc='Dynamic DNS' or catdesc='Potentially Illegal' or catdesc='Terrorism' or catdesc='Spam URLs' or catdesc='Proxy Avoidance' or catdesc='Weapons (sales)')
group by user_src, dtime, catdesc, hostname, utmaction
order by dtime desc
)### t
group by user_src, dtime, catdesc, website, status
order by dtime desc

[Anonymous]

Hello @NetMan_Bath ,

Thank you for posting to the Fortinet Community Forums. We appreciate your patience. We will have someone soon helping you with this query.

[Debbie_FTNT · Accepted solution]

Hey NetMan_Bath,

that dataset does include the source IP in some manner:

select coalesce(nullifna(`user`), ipstr(`srcip`))

This means the dataset will show the username, and if no username is present, it will instead use the source IP.

If you want to have the source IP included expressively, you would need to add that to the different select statements, something like this probably:
select from_dtime(dtime) as timestamp, user_src, srcip, catdesc, hostname as website,[...]
select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, dtime, catdesc, hostname, srcip, [...]

You might also need to include it in the group-by clause:

group by user_src, srcip, dtime, catdesc, hostname, utmaction
order by dtime desc
)### t
group by user_src, srcip, dtime, catdesc, website, status
order by dtime desc

Please note that I do not currently have logs or a FortiAnalyzer to test this with - this is cobbled together based on my sparse knowledge of SQL.

You can try to reach out to FortiAnalyzer technical Support, but assistance with customizing datasets, or creating them from scratch, is typically out of scope and belongs to Professional Services instead.

[NetMan_Bath]

Hey Debbie_FTNT,

Thanks very much for this.  It works! 

Kind regards,

NetMan_Bath