I have two doubts, please help.
1. Join operation
MySQL [(none)]> SELECT id, data.uuid, data.userid, data.username, data.src_ip, data.service, data.ztna_server, data.src_port FROM db785_netfilter_connection limit 2;
+-------------------+--------------------------------------+-------------+---------------+-------------+--------------+------------------+---------------+
| id | data.uuid | data.userid | data.username | data.src_ip | data.service | data.ztna_server | data.src_port |
+-------------------+--------------------------------------+-------------+---------------+-------------+--------------+------------------+---------------+
| 75122889784432076 | a1802845-e95f-e9ed-ab8f-deb7ef70f26d | 99 | vamshi.k | 10.178.0.46 | RDP | windows | 37036 |
| 75122889784404797 | 42689623-26f6-5fed-88c1-4079280c7cc2 | 94 | vamshi.k | 10.178.0.38 | SSH | netfilter | 34988 |
+-------------------+--------------------------------------+-------------+---------------+-------------+--------------+------------------+---------------+
2 rows in set (0.002 sec)
MySQL [(none)]> SELECT id, data.uuid, data.userid, data.username, data.src_ip, data.service, data.ztna_server, data.src_port FROM db785_netfilter_connection_exit limit 2;
+-------------------+--------------------------------------+-------------+---------------+-------------+--------------+------------------+---------------+
| id | data.uuid | data.userid | data.username | data.src_ip | data.service | data.ztna_server | data.src_port |
+-------------------+--------------------------------------+-------------+---------------+-------------+--------------+------------------+---------------+
| 75122889784407099 | a6513364-f343-be31-9542-9d9a49feb898 | 94 | vamshi.k | 10.178.0.38 | SSH | netfilter | 57248 |
| 75122889784404991 | 42689623-26f6-5fed-88c1-4079280c7cc2 | 94 | vamshi.k | 10.178.0.38 | SSH | netfilter | 34988 |
+-------------------+--------------------------------------+-------------+---------------+-------------+--------------+------------------+---------------+
2 rows in set (0.002 sec)
MySQL [(none)]>
MySQL [(none)]> desc db785_netfilter_connection;
+-------+--------+------------+
| Field | Type | Properties |
+-------+--------+------------+
| id | bigint | |
| data | json | |
+-------+--------+------------+
2 rows in set (0.002 sec)
MySQL [(none)]> desc db785_netfilter_connection_exit;
+-------+--------+------------+
| Field | Type | Properties |
+-------+--------+------------+
| id | bigint | |
| data | json | |
+-------+--------+------------+
2 rows in set (0.002 sec)
MySQL [(none)]>
MySQL [(none)]> SELECT id, data.uuid, data.userid, data.username, data.src_ip, data.service, data.ztna_server, data.src_port FROM db785_netfilter_connection LEFT JOIN db785_netfilter_connection_exit ON db785_netfilter_connection.data.uuid = db785_netfilter_connection_exit.data.uuid;
ERROR 1064 (42000): table db785_netfilter_connection: join-on attribute 'data.uuid' not found
MySQL [(none)]>
is join operation not allowed in these cases? or How can we apply join to get the same scenario which I’m trying to?
2. DISTINCT Count
MySQL [(none)]> SELECT count(*) FROM db785_netfilter_connection GROUP BY data.uuid;
+----------+
| count(*) |
+----------+
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
+----------+
17 rows in set (0.002 sec)
MySQL [(none)]>SELECT COUNT(DISTINCT data.uuid) FROM ice:db785_netfilter_connection;
+---------------------------+
| count(distinct data.uuid) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.001 sec)
MySQL [(none)]>
Why it is showing 0 when I try to count distinct uuid’s?