topic Re: Add a DBConnect Input to Dashboard in Dashboards & Visualizations

Add a DBConnect Input to Dashboard

dgwann — Fri, 06 Oct 2023 15:19:43 GMT

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

Re: Add a DBConnect Input to Dashboard

gcusello — Fri, 06 Oct 2023 15:28:57 GMT

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

Re: Add a DBConnect Input to Dashboard

dgwann — Fri, 06 Oct 2023 16:08:18 GMT

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

Re: Add a DBConnect Input to Dashboard

dgwann — Fri, 06 Oct 2023 16:17:24 GMT

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.

Re: Add a DBConnect Input to Dashboard

gcusello — Sat, 07 Oct 2023 05:47:51 GMT

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

Re: Add a DBConnect Input to Dashboard

dgwann — Thu, 12 Oct 2023 17:05:29 GMT

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.

Re: Add a DBConnect Input to Dashboard

gcusello — Fri, 13 Oct 2023 06:29:15 GMT

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

Re: Add a DBConnect Input to Dashboard

dgwann — Tue, 17 Oct 2023 19:55:40 GMT

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

Re: Add a DBConnect Input to Dashboard

gcusello — Wed, 18 Oct 2023 06:30:15 GMT

Hi @dgwann,

what re the results executing your searches?

Ciao.

Giuseppe

Re: Add a DBConnect Input to Dashboard

dgwann — Wed, 18 Oct 2023 12:51:21 GMT

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