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
;
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!
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.
@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?
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.
@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.
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?
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".
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.
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!