topic Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results? in Splunk Search

Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

pmcfadden91 — Wed, 07 Oct 2015 21:09:48 GMT

Hi,

I have a DB query as below which displays the results as shown in the attached picture:

| dbquery "PB CSL" limit=1000 "select trunc(creation_time), SOURCE_SYSTEM_NAME, count(1) Count 
from PB_CSL.activity a, PB_CSL.Activitymap b 
where trunc(creation_time) >= trunc(sysdate) -5 and a.service_id = b.ACTIVITY_ID 
group by trunc(creation_time), SOURCE_SYSTEM_NAME 
order by trunc(creation_time), SOURCE_SYSTEM_NAME" | rename TRUNC(CREATION_TIME) as Date | eval Date=strftime('Date', "%m-%d-%Y") | chart values(COUNT) over SOURCE_SYSTEM_NAME by Date

I want to show the Total, Average, and % Change as additional columns. I already used the chart command for the table, so how would I be able to show the 3 remaining columns? Any help is greatly appreciated!

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

Richfez — Tue, 29 Sep 2020 07:29:31 GMT

You seem to be doing a lot of work in the dbquery that may be easier done in Splunk, and even if not, that "heavy" query makes it difficult for folks not conversant with "regular" DBs to try to answer.

Could you provide a few rows of the dbquery output before you do anything in Splunk to it and, if possible, post a few rows of output for a query like
| dbquery "PB CSL" limit=1000 "select trunc(creation_time) AS Date, SOURCE_SYSTEM_NAME
from PB_CSL.activity a, PB_CSL.Activitymap b where a.service_id = b.ACTIVITY_ID"
Those may help us to see what it is we're working with.

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

pmcfadden91 — Tue, 29 Sep 2020 07:32:00 GMT

So the dbquery output is 3 columns :

Creation_Time Source_System Count
10/2/2015 ADP 2483
10/2/2015 CSL 10039
10/2/2015 DIVP 2032
10/2/2015 RECON 7
10/2/2015 SHRS 621
10/2/2015 T24 587
10/2/2015 TPE 64702
10/3/2015 ADP 2519
10/3/2015 CSL 138
10/3/2015 DIVP 420
10/3/2015 TPE 3

It shows like this in the database and when I run the query into Splunk before adding or making any additonal changes to it.

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

Richfez — Sat, 10 Oct 2015 12:04:33 GMT

Thanks for that! This was the output of your whole db_query, right - just without any of the splunk pieces?

So without all that grouping and summing inside SQL, you'd just have rows like

10/2/2015 08:43:17.455 ADP
10/2/2015 08:43:19.221 ADP
10/2/2015 08:43:19.664 CSL
...

Right? Exact date/time format doesn't matter, just that it's date and time, not just date, right?

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

woodcock — Sat, 10 Oct 2015 22:52:22 GMT

Add this to your current search:

| fillnull value=0 | addtotals row=true| addtotals col=true | eval prev=0 | foreach *2015 [ eval <<FIELD>>_changePct=100*((<<FIELD>>-prev)/prev) | eval prev=<<FIELD>>]

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

pmcfadden91 — Tue, 29 Sep 2020 07:41:20 GMT

Hi woodcock, thanks for your answer. I was able to modify my query but am still having touble with the % Difference. The query is below but I need a need the "Difference" column to work as such: 100*((5_Day_Avg-Current_Day/5_Day_Avg)). The Current Day being today's date, and then calculating when tomorrow is the new current date. I tried an if statement for eval, but can't seem to quite figure out why it wont work.

 index=csl_dblogs | rename TRUNC_CREATION_TIME as Date | rename SOURCE_SYSTEM_NAME as Source_System| eval Date=strftime('Date', "%m-%d-%Y") | chart values(COUNT) over Source_System by Date | addtotals fieldname=Total | eval 5_Day_Avg=round(Total/5)

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

woodcock — Thu, 29 Oct 2015 16:28:53 GMT

What is the output of your query? Did you even try my solution? As long as the output has not changed (you are no longer using dbquery), it should work but it will give you the percent change of adjacent fields. Perhaps you would like the % difference between the left-most column and the rightmost column? Define the approach and I am sure I will be able to help you.

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

pmcfadden91 — Mon, 09 Nov 2015 20:57:01 GMT

@woodcock, I tried your solution, combined with what I have:

index=csl_dblogs | rename TRUNC_CREATION_TIME as Date | rename SOURCE_SYSTEM_NAME as Source_System| eval Date=strftime('Date', "%m-%d-%Y") | chart values(COUNT) over Source_System by Date | addtotals fieldname=Total | eval 5_Day_Avg=round(Total/5)   | fillnull value=0 | addtotals row=true| addtotals col=true | eval prev=0 | foreach *2015 [ eval <<FIELD>>_changePct=100*((<<FIELD>>-prev)/prev) | eval prev=<<FIELD>>]

So it sort of works, but I only need 1 specific column that displays %Difference based on "Today's Date -1"(Excluding Weekends). So for today, the difference (1 column) would show for all systems where the date is Nov 6.

Re: Splunk DB Connect 1: How to edit my dbquery to add Total, Average, and Percentage columns to my results?

woodcock — Sun, 15 Nov 2015 23:30:53 GMT

You have completely destroyed the function of what I gave you which I guess is because you are desiring something totally different than what I understood you to mean. If you will come back with a mockup table showing your desired output, then maybe I can help. As it stands right now, I believe that I have given you exactly what you described so I cannot help any more because I just don't understand what you really need.