FAZ specific query

[jjg]

Hi all,

FAZ 7.6.4

I need some help trying to write a very specific query and I'm struggling.  It needs to be able to Union All so that I can read from the VPN logs and the Traffic logs to be able to then collate the information and produce a table.

Here's what I need to be able to do.

1. I need to be able to detect a users source country when coming in on the SSL VPN
2. I then need to be able to see if a user connected to an ip address say 192.168.1.1
3. I then need to be able to list it by user rather than srcip
4. Filter out everything else

Can this even be done?

SELECT
user,
srcip,
srccountry
FROM
$log-event
WHERE
$filter
AND subtype = 'vpn'
AND srccountry != 'United Kingdom'
AND srccountry != 'Reserved'
GROUP BY
user, srcip, srccountry

UNION ALL

SELECT
user,
dstip AS srcip,
srccountry
FROM
$log-traffic
WHERE
$filter
AND dstip = '192.168.1.1'
GROUP BY
user, dstip, srccountry

ORDER BY
srcip

[jjg]

OK,

I'm getting somewhere, the blow validates fine in the dataset but when I run a report I get Report generation failed.

SELECT
srccountry,
user,
dstip
FROM (
/* From traffic logs */
SELECT
srccountry,
user,
dstip
FROM
$log-traffic
WHERE
$filter
AND dstip = '10.160.1.217'
GROUP BY
srccountry, user, dstip

UNION ALL

/* From event logs */
SELECT
srccountry,
user,
remip AS dstip
FROM
$log-event
WHERE
$filter
AND subtype = 'vpn'
GROUP BY
srccountry, user, remip
) t
GROUP BY
srccountry, user, dstip
ORDER BY
srccountry