I have a dashboard with this query:
| dbxquery connection=abcd-local query="SELECT DATE_FORMAT(date(dts),GET_FORMAT(DATE,'USA')) as rate_date, category_id, count(*) FROM url_directory WHERE category_id in (1,2,3) and dts >= now() - INTERVAL 1 MONTH group by rate_date, category_id" | eval category_id = case(
category_id=="1", "Scam",
category_id=="2", "Phishing",
category_id=="3", "Malicious",
1==1, category_id)
It returns a table like this:
Scam 5 2/1/2023 Phishing 18 2/1/2023 Malicious 23 2/1/2023 Scam 8 2/2/2023 Phishing 12 2/2/2023 Malicious 17 2/2/2023
I want to turn that table into a Pivot Table like this:
Scam Phishing Malicious 2/1/2023 5 18 23 2/2/2023 8 12 17 ...
What can I add to the query (or how can I change the query) to create that pivot table in a dashboard?
Thanks!
... View more