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
Solved! Go to Solution.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
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.
Created on 02-26-2022 05:26 PM
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.
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.
Hey Debbie_FTNT,
Thanks very much for this. It works!
Kind regards,
NetMan_Bath
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.