Skip to main content
bascheew
bascheew
Visitor III
June 18, 2019
Question

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

  • June 18, 2019
  • 1 reply
  • 8835 views

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!

 

 

 

 

1 reply

AtiT
AtiT
New Member
June 20, 2019

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
bascheewAuthor
Visitor III
June 20, 2019

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
AtiT
New Member
June 20, 2019

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.

Terms & ConditionsAccessibility statement