Splunk Search

How to rewrite/correspond MySQL queries in Splunk?

Username1
Path Finder

Hey community I have my data in both MySQL and in Splunk. I'm trying to mimic the MySQL queries in Splunk so I can make a visual. My Data has five columns,:"Month", "Project", "Status", "Completion", "Points". The first query sums the column "Points" only if that row includes a Status and Completion value of "Done" and then grouping it by Month. The second query is summing the column points just by the Month.

The problem I'm running into is having them together in one Splunk Query as I'm trying to have both tables in one graph. Any suggestions? 

 

select Month,sum(Points) from TABLE
 where Status = "Done"
 and Completion = "Done"
 group by Month
 ;
&
 select Month,sum(Points)from TABLE
 group by Month
 ;

 

Tags (2)
0 Karma
1 Solution

Username1
Path Finder
index=index
| table Month, Project, Status, Accepted, Points
| eval done = if(Status= "Done" AND Accepted = "Done",Points,'0')
| stats sum(Points) as all_points, sum(done) as done_points by Month


The error was in the double and single quotes, that is my mistake...Thanks you @thambisetty  for all of your help!

View solution in original post

0 Karma

thambisetty
Super Champion

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,Points,“0”)

| stats sum(Points) as all_points, sum(done) as done_points by Month

Sample output

———-

Month all_points done_points

Jan  10 5

Feb 20 10

 

done will have points when status and accepted value is done. sum(done) renamed to done_points this will have sum of points where status and accepted is done by month.

 

hope this answers your question.

————————————
If this helps, give a like below.

Username1
Path Finder

@thambisetty  Thanks for the response 

I ran your query and it is showing a blank for done_points

It returns the below, but done_points is all null

Month | all_points| done_points

Jan       |  10              |

Feb       |   20            |


Any thoughts on why that may be happening?

0 Karma

thambisetty
Super Champion

What is exact field name of Status and accepted?

Check the field names I used in eval statement are matching with field names in Splunk.

————————————
If this helps, give a like below.

Username1
Path Finder

@thambisetty  The exact field names are Status and Accepted.
I have made sure that the field names are correct and the single quotes are correct as well.

0 Karma

thambisetty
Super Champion

Can you post your query here please?

and also remove last stats and run search and check if you are seeing new field done or not in the fields?

————————————
If this helps, give a like below.

Username1
Path Finder

Here is my current query

index=index
| table Month, Project, Status, Accepted, Points
| eval done = if(Status= 'Done' AND Accepted = 'Done',Points,'0')
| stats sum(Points) as all_points, sum(done) as done_points by Month

I removed the last |stats and it shows theres no results for "done". 

0 Karma

thambisetty
Super Champion

Run this query as it is.

 

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,Points,“0”)

| stats sum(Points) as all_points, sum(done) as done_points by Month

 

you replaced double quotes with single quotes in eval statement. 
if eval is throwing an error for some reason just remove double quotes and input them from your keyboard.

————————————
If this helps, give a like below.

Username1
Path Finder
index=index
| table Month, Project, Status, Accepted, Points
| eval done = if(Status= "Done" AND Accepted = "Done",Points,'0')
| stats sum(Points) as all_points, sum(done) as done_points by Month


The error was in the double and single quotes, that is my mistake...Thanks you @thambisetty  for all of your help!

View solution in original post

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!