FAZ 5.2 Question On DataSet

[BWiebe]

I have the following dataset pulling from traffic log: select from_dtime(dtime) as timestamp, user as user, catdesc, hostname as website, action as status, sum(bandwidth) as bandwidth from (###(select dtime, catdesc, hostname, 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 utmevent in (' webfilter' , ' banned-word' , ' web-content' , ' command-block' , ' script-filter' ) group by dtime, catdesc, hostname, action order by dtime desc)### union all ###(select dtime, catdesc, hostname, action, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-webfilter where $filter and hostname is not null and (eventtype is null or logver=52) group by dtime, user, catdesc, hostname, action order by dtime desc)###) t group by dtime, user, catdesc, website, status order by dtime desc And it works, mostly, to give me detailed web browsing. The problem in 5.2 is, it seems the ' user as user' doesn' t return the right information. It returns ' postgres' for all users. I' d like to get srcip and username if at all possible in this but not having any luck. Any thoughts?

[AtiT]

Hi, I am not an SQL specialist by I think that ' user as user' is not correct. Give a reference called " user" to a column called " user" . Which one the SQL should use? I suggest to use something else for example: user as u ... or user as usr, etc. But the problem is that this is also not correct. You should use like this: `user` as usr You should use the ` sign (ALT+96 normally the first button under the ESC on the keyboard) to write the column name between these signs. Try the following datasets and see the differences: SELECT user AS usr, COUNT(*) AS totalnum FROM $log GROUP BY usr ORDER BY totalnum DESC SELECT `user` AS usr, COUNT(*) AS totalnum FROM $log GROUP BY usr ORDER BY totalnum DESC The first one will return the result with " postgres" username and how many times was it found in the logs - incorrect. The second one will return the usernames and will count them how many times were found in the logs - correct.

[BWiebe]

Interesting - testing your queries definitely shows what I would expect for user, etc. However, if I change my query to include `user` AS usr, I get column " user" does not exist. So - that' s strange, as I' d expect if it worked in one query, it' d work in another.