What's the query to show user, IP and hostname?

[bascheew]

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.

Any help is much appreciated!

[AtiT]

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`

[bascheew]

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:

[AtiT]

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.

[bascheew]

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

  

[AtiT]

Use the Log type: Traffic

[bascheew]

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.