topic Re: How to create a pivot table in a dashboard from a search in Dashboards & Visualizations

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

DLT76 — Wed, 15 Feb 2023 20:52:33 GMT

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!

Re: How to create a pivot table in a dashboard from a search

bowesmana — Tue, 14 Feb 2023 23:50:13 GMT

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

Re: How to create a pivot table in a dashboard from a search

DLT76 — Wed, 15 Feb 2023 00:15:38 GMT

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.

Re: How to create a pivot table in a dashboard from a search

bowesmana — Wed, 15 Feb 2023 02:03:19 GMT

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

Re: How to create a pivot table in a dashboard from a search

DLT76 — Wed, 15 Feb 2023 19:24:38 GMT

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!

Re: How to create a pivot table in a dashboard from a search

bowesmana — Wed, 15 Feb 2023 22:09:30 GMT

So when you are doing

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?

Re: How to create a pivot table in a dashboard from a search

DLT76 — Wed, 15 Feb 2023 22:22:27 GMT

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.

Re: How to create a pivot table in a dashboard from a search

bowesmana — Wed, 15 Feb 2023 22:29:05 GMT

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.

Re: How to create a pivot table in a dashboard from a search

DLT76 — Wed, 15 Feb 2023 22:46:10 GMT

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.

Re: How to create a pivot table in a dashboard from a search

bowesmana — Wed, 15 Feb 2023 23:08:33 GMT

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.

Re: How to create a pivot table in a dashboard from a search

DLT76 — Wed, 15 Feb 2023 23:16:05 GMT

Here you go:

Re: How to create a pivot table in a dashboard from a search

bowesmana — Wed, 15 Feb 2023 23:22:07 GMT

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

Re: How to create a pivot table in a dashboard from a search

DLT76 — Wed, 15 Feb 2023 23:38:04 GMT

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

Re: How to create a pivot table in a dashboard from a search

bowesmana — Thu, 16 Feb 2023 00:30:26 GMT

Great, glad we got there in the end!