Add a DBConnect Input to Dashboard

[dgwann]

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

Date	Group_Name	Number_of_Submissions
2023-10-02	Apple	780
2023-10-03	Apple	1116
2023-10-04	Apple	1154
2023-10-05	Apple	786
2023-10-06	Apple	699
2023-10-02	Banana	358
2023-10-03	Banana	760
2023-10-04	Banana	254
2023-10-05	Banana	1009
2023-10-06	Banana	876
2023-10-02	Others	1265
2023-10-03	Others	1400
2023-10-04	Others	257
2023-10-05	Others	109
2023-10-06	Others	1709

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

[gcusello · Accepted solution]

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]

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

[dgwann]

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.

[dgwann]

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

[gcusello]

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

[dgwann]

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

[gcusello]

Hi @dgwann,

what re the results executing your searches?

Ciao.

Giuseppe

[dgwann]

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.

[gcusello]

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