Splunk Search

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

pmcfadden91
Path Finder

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

alt text

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!

0 Karma

woodcock
Esteemed Legend

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>>]
0 Karma

pmcfadden91
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

pmcfadden91
Path Finder

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)
0 Karma

woodcock
Esteemed Legend

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.

0 Karma

Richfez
SplunkTrust
SplunkTrust

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.

0 Karma

pmcfadden91
Path Finder

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.

0 Karma

Richfez
SplunkTrust
SplunkTrust

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?

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...