I see that many datasets use the following to pull in a user:
select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src
If the user isn't available it shows the IP address. One of our clients wants to see both the user and IP address and where possible the internal hostname of the computer. We have enabled resolving hostnames in the log settings and I have verified that there are reverse zones where needed.
How can columns for user, ip address and hostname be shown? If any of the values are missing then the field can be blank. I tried:
select user, srcip
However the user column is populated with "postgres" in all rows. And I don't know what value to query to pull in hostnames.
Thank you for the response. I attempted to put single quotes around user, srcip and srcname, however when I do that the columns are populated with that exact string. If I remove the single quotes the srcname field is throwing an error stating that srcname does not exist. See screenshot below:
Thank you. If I use the log type of "Traffic" then srcname works! The new problem is that web category no longer works. I assume that the traffic log doesn't contain the webfilter categories in it. Is there a way to bring all of this together into one query, or is what I'm trying to do not possible?
Here is the query:[code lang=sql]select `user`, `srcip`, `srcname`, catdesc, count(*) as sessions
from $log where $filter
group by `user`, `srcip`,`srcname`, catdesc
order by sessions desc
The catdesc column is blank when running this on the traffic log.
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.