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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

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