Dashboards & Visualizations

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

DLT76
Path Finder

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!

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

DLT76
Path Finder

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

DLT76
Path Finder

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!

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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?

0 Karma

DLT76
Path Finder

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.

Tags (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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?

bowesmana_0-1676500133378.png

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

0 Karma

DLT76
Path Finder

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

 

0 Karma

DLT76
Path Finder

Here you go:

DLT76_0-1676502955368.png

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Great, glad we got there in the end!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...