Add source IP address to Analyser Dataset

Question

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 selectfrom_dtime(dtime) as timestamp, user_src, catdesc, hostname as website,action as status, sum(bandwidth) as bandwidthfrom###(select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src,dtime, catdesc, hostname, utmaction as action,sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidthfrom $log-trafficwhere $filter andhostname is not null andlogid_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, utmactionorder by dtime desc)### tgroup by user_src, dtime, catdesc, website, statusorder by dtime desc

Debbie_FTNT · Accepted Answer

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.

Anonymous_User · Answer

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.

Sign up

Login with SSO

Login to the community

Login with SSO