Dashboards & Visualizations

Add a DBConnect Input to Dashboard

dgwann
Explorer

I have a DBConnect Input defined that produces the following output:

DateGroup_NameNumber_of_Submissions

2023-10-02

Apple780
2023-10-03Apple1116
2023-10-04Apple1154
2023-10-05Apple786
2023-10-06Apple699

2023-10-02

Banana358
2023-10-03Banana760
2023-10-04Banana254
2023-10-05Banana1009
2023-10-06Banana876

2023-10-02

Others1265
2023-10-03Others1400
2023-10-04Others257
2023-10-05Others109
2023-10-06Others1709

 

I want to have this data displayed on a Dashboard as a multi-line chart, x-axis is the Date, y-axis is the Number of submissions, and there should be different color lines representing the different groups.  I am new to Splunk.  Very new.  I need succinct instructions pls.  Thanks!!!

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @dgwann,

let me understand: do you want to use DB-Connect to extract data from a DB saving them in an index and then search on the index or directly execute the query run-time on the DB and display results?

the second option doesn't consume license but surely will have very low performces because Splunk DB-Connect was created to extract data from a DB wexecuting query, not to execute runtime queries.

When you'll have the data in an index you can create your search using SPL, something like this:

 

index=your_index
| stats sum(Number_of_Submissions) AS Number_of_Submissions BY Date Group_name

 

if instead you want to run on line queries (I not hint this because it will be more than slowest!), you should try something like this:

 

| dbxquery query="<your_query>" connection="<mySQL<" 
| stats sum(Number_of_Submissions) AS Number_of_Submissions BY Date Group_name

 

for more details see at https://docs.splunk.com/Documentation/DBX/3.14.1/DeployDBX/Commands

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @dgwann,

let me understand: do you want to use DB-Connect to extract data from a DB saving them in an index and then search on the index or directly execute the query run-time on the DB and display results?

the second option doesn't consume license but surely will have very low performces because Splunk DB-Connect was created to extract data from a DB wexecuting query, not to execute runtime queries.

When you'll have the data in an index you can create your search using SPL, something like this:

 

index=your_index
| stats sum(Number_of_Submissions) AS Number_of_Submissions BY Date Group_name

 

if instead you want to run on line queries (I not hint this because it will be more than slowest!), you should try something like this:

 

| dbxquery query="<your_query>" connection="<mySQL<" 
| stats sum(Number_of_Submissions) AS Number_of_Submissions BY Date Group_name

 

for more details see at https://docs.splunk.com/Documentation/DBX/3.14.1/DeployDBX/Commands

Ciao.

Giuseppe

dgwann
Explorer

I ended up using the chart command instead of stats and got it to come out correctly.  Thanks again!!

0 Karma

dgwann
Explorer

I want a dashboard panel to display the line chart, the query should be run in the background every 30 minutes to an hour and update the display.  So I assume I mean that I want to directly execute the query on the DB and display results.  I've created the Input.  Do I need to create an Output?  I don't want to update the database at all, just read it.

0 Karma

dgwann
Explorer

@gcusello Where do I put those blocks of code you sent?  Do I put them somewhere in the dashboard builder, or in the DBConnect for the Input?  Like I said, I'm very new to this.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dgwann,

in the dashboard,

as I said, you should create an input in DB-Connect, indexing results in Splunk and using my first solution in dashboards.

Ciao.

Giuseppe

0 Karma

dgwann
Explorer

Okay, I'm getting closer.  I get a line chart to show up now.  However, I need 3 different lines representing the 3 different customers and the Number of submissions for each day.  I just need to get the SPL right.  Thanks for your assistance and patience!!

 

Tags (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dgwann,

what re the results executing your searches?

Ciao.

Giuseppe

0 Karma

dgwann
Explorer

I've done exactly as you said, but it's still not working.  What kind of visualization type should I select to get a multi line graph?  I'm running on a clustered environment using SQL Server 2016 and using DBConnect.  My field in the dashboard still says it is waiting for input.  I updated the Indexes.conf files and did the Rolling Restart on the Index Clustering Manager node.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @dgwann,

if you run a search using dbquery, you should have as result a table that you can display as you like, as every other kind of Splunk panel.

it isn't relevant (for the display) where the data come from.

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...