- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What's the query to show user, IP and hostname?
I see that many datasets use the following to pull in a user:
select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_srcIf 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, srcipHowever the user column is populated with "postgres" in all rows. And I don't know what value to query to pull in hostnames.
Any help is much appreciated!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
accessing the user filed it is better to use `user`. I do not know where can be other "problems" if you not use field names in `` so I am using it everywhere.
For computer names you can use the srcname field. The query can looks like:
select distinct `user`, `srcip`, `srcname` from $log where $filter
Probably you will se a lot of addresses from the Internet among the results. You can limit the results to show only the private address ranges, like:
select distinct `user`, `srcip`, `srcname` from $log where $filter and (`srcip`<<'10.0.0.0/8' or `srcip`<<'172.16.0.0/12' or `srcip`<<'192.168.0.0/16' )
group by `user`, `srcip`, `srcname`
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
do not use single quotes. The correct character is ` (ALT+96) or the sign under the ESCAPE key (on my keyboard).
Or just copy and paste my dataset.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Forgive my ignorance! The ` character resolved the user field issue. Thank you for guiding me through that!
The only thing left is that the srcname is not being recognized:
ERROR: column "srcname" does not exist
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use the Log type: Traffic
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.