topic Re: Rewrite/Correspond MySQL Queries in Splunk in Splunk Search

How to rewrite/correspond MySQL queries in Splunk?

Username1 — Thu, 06 Aug 2020 19:04:20 GMT

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 ;

Re: Rewrite/Correspond MySQL Queries in Splunk

thambisetty — Thu, 06 Aug 2020 18:55:25 GMT

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.

Re: Rewrite/Correspond MySQL Queries in Splunk

Username1 — Thu, 06 Aug 2020 19:01:44 GMT

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

Re: Rewrite/Correspond MySQL Queries in Splunk

thambisetty — Thu, 06 Aug 2020 19:10:35 GMT

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.

Re: Rewrite/Correspond MySQL Queries in Splunk

Username1 — Thu, 06 Aug 2020 19:27:10 GMT

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

Re: Rewrite/Correspond MySQL Queries in Splunk

thambisetty — Thu, 06 Aug 2020 19:30:26 GMT

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?

Re: Rewrite/Correspond MySQL Queries in Splunk

Username1 — Thu, 06 Aug 2020 19:37:17 GMT

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

Re: Rewrite/Correspond MySQL Queries in Splunk

Username1 — Thu, 06 Aug 2020 19:40:39 GMT

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!

Re: Rewrite/Correspond MySQL Queries in Splunk

thambisetty — Thu, 06 Aug 2020 19:41:32 GMT

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.