How to create a pivot table in a dashboard from a search?

[DLT76]

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!

[bowesmana]

Use chart

| chart sum(count) as count over rate_date by category_id

Then if you want to rearrange your columns, use the table command

[DLT76]

When I append that string to the end of my query, it returns "No results found." pretty quickly.  When I remove it, I get data.  Any ideas?

Thank you for your help.

[bowesmana]

Can you show your splunk table with that data. I assumed from your SQL/SPL that you have 3 fields

count, rate_date, category_id

If you don't have those fields, you need to tailor the chart accordingly

[DLT76]

These are the fields in the table:

auto_cascade
buff_status
category_id
directory
domain
dts
norateflag
port
regex_unrated_value
regexid
review_later
root_domain_id
source
type
url

Thanks so much!

[bowesmana]

So when you are doing

| 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" 

the field that is showing as "2/1/2023" - what is that? In the above, I assume it's rate date, but you don't list that field in your list of fields and your data example only shows 3 bits of information, e.g.

Scam 5 2/1/2023

so something's not right - what is the name of the field in your data that corresponds to 2/1/2023 from the list of fields you posted?

[DLT76]

It originates from the dts field based on this part of the original query:

DATE_FORMAT(date(dts),GET_FORMAT(DATE,'USA')) as rate_date

I should have shown the example in the actual format the query displays it in:  02.01.2023.

[bowesmana]

In your original question you posted an example of a table containing

Scam 5 2/1/2023
...

which has 3 fields.

My suggestion to use

| chart sum(count) as count over rate_date by category_id

is based on assuming the '5' is in a field called 'count', the "Scam" is in the field called category_id and the 2/1/2023 (or 2.1.2023) is in a field called rate_date.

Does your table look like this, with the column headings as shown or is it something different?

You simply need to use the names of the fields in your table in the corresponding chart command above.

[DLT76]

I think I'm not using the right words. My apologies. This is all a bit confusing. Let me start from the top with some better examples. Be aware that I might not be using the right Splunk or SQL terminology.

We have a database table with data that I want to display like an Excel pivot table would.

Here's a subset of the table with actual column names (fields?) and some sample data:

category_id url dts
1 scamurl1 2/1/2023
1 scamurl2 2/1/2023
2 phishingurl1 2/2/2023
2 phishingurl2 2/3/2023
3 maliciousurl1 2/3/2023
1 scamurl3 2/3/2023
3 maliciousurl2 2/4/2023
2 phishingurl3 2/4/2023
2 phishingurl4 2/4/2023

What I'd like to do is display this data in a dashboard that looks like this:

Date Scam Phishing Malicious
2/1/2023 2 0 0
2/2/2023 0 1 0
2/3/2023 1 1 1
2/4/2023 0 2 1

In other words, I want to create a pivot table that has a row for each day (going back one month) and displays a count of the URLs in each category that day. And I want that pivot table to show aliases for the category numbers at the top.

Is that doable? I sure appreciate your time and assistance.

[bowesmana]

Please take and post a screenshot of the data you can see immediately after your initial SPL example code in your first post showing what the first few lines of the table are.

[DLT76]

Here you go:

[bowesmana · Accepted solution]

The problem is that your field name for count is actually "count(*)" and the chart command is doing sum(count) and there is no 'count' field.

Unfortunately you can't have a useable field with a wildcard in it - you cannot rename it and you cannot use it in other functions, so change your count(*) to

.... count(*) as count ...

and then use the original chart command I gave

[DLT76]

That worked!  Thank you for your patience helping me diagnose the issue.

[bowesmana]

Great, glad we got there in the end!