topic Re: How do you translate SQL into Splunk? in All Apps and Add-ons

How do you translate SQL into Splunk?

mnikhil7692 — Tue, 29 Sep 2020 21:51:04 GMT

Hi there,

Per the attached picture, i just put this data in Excel and got this chart out. However, i was not able to get this into Splunk. Visualization does not show me anything.Obviously, i am missing something. Please advise.

Here's the query I'm running, which generates the same results :

SELECT
TRUNC(CREATION_DATE_TIME),
COUNT(1) TOTAL_PROCESSES,
COUNT( distinct PROVIDER_name ) DISTINCT_PROVIDERS,
SUM(CASE WHEN FLAG = 'O' THEN 1 END ) OUTGOING,
SUM(CASE WHEN FLAG = 'I' THEN 1 END ) INCOMING,
FROM

GROUP BY TRUNC(CREATION_DATE_TIME)

here's what i am running in Splunk :

index="MyIndex" sourcetype="MySourceType" source="MySource" 
| eval x_out=if((FLAG=="O"),1,0), x_in=if((FLAG=="I"),1,0) 
| convert ctime(creation_date_time) as cdt timeformat=%m/%d/%y 
| timechart span=7d dc(provider_name) by cdt
| stats sum(x_out) as Outbound sum(x_in) as Inbound dc(provider_name) as Providers

Re: How do you translate SQL into Splunk?

niketn — Wed, 31 Oct 2018 16:18:09 GMT

@mnikhil7692 what is the source of your data? Are you indexing spreadsheet data or any other data to Splunk or is it coming from CSV which has been uploaded to Splunk lookup file.

If it is indexed to Splunk what do the events look like? Do you have fields already extracted?

If the data shows up exactly the way you have displayed in your table, and there is one event per day, just the table command should work.

Following is a run anywhere example which uses command from | makeresult till | KV to generate some sample events as per data. Then uses table to plot the table. You can change visualization to Line Chart in Splunk to see the result:

| makeresults
| eval data="CREATION_DATE_TIME=10/24/2018,TOTAL_PROCESS=96,DISTINCT_PROVIDERS=21,OUTGOING=38,INCOMING=58;CREATION_DATE_TIME=10/25/2018,TOTAL_PROCESS=96,DISTINCT_PROVIDERS=21,OUTGOING=37,INCOMING=59;CREATION_DATE_TIME=10/26/2018,TOTAL_PROCESS=95,DISTINCT_PROVIDERS=21,OUTGOING=37,INCOMING=58;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| table CREATION_DATE_TIME DISTINCT_PROVIDERS INCOMING OUTGOING TOTAL_PROCESS

PS: Just to add here is the Splunk documentation for transitioning from SQL to SPL: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk

Re: How do you translate SQL into Splunk?

mnikhil7692 — Wed, 31 Oct 2018 17:46:51 GMT

Hi Nilay, thanks for your quick response and the reference link form Sql to SPL.
My source data is in Oracle. please don't let my screenshot of the excel mis-guide you. I used that to only show how i want my final visualization to look like in Splunk.
The query is the one i am trying to translate into Splunk, so hard-coding the incomding, outgoing,, providers etc wont work. It has to be something that's generated dynamically.

Re: How do you translate SQL into Splunk?

niketn — Tue, 29 Sep 2020 21:52:33 GMT

@mnikhil7692 hardcoding was only to generate sample data as per your question as we will not have same data as your system. Please provide some sample events (mock/anonymize any sensitive information).

Unless we know what your event looks like and what are your fields it will not be easy for us to assist, however, based on your query provided try the following and confirm:

 index="MyIndex" sourcetype="MySourceType" source="MySource" 
| eval _time=strptime(creation_date_time,"%m/%d/%Y")
| timechart count(eval(FLAG=="O")) as Outbound count(eval(FLAG=="I")) as Inbound dc(provider_name) as Providers

OR May be the following if you do not need Time as epoch time and your existing creation_date_time is in MM/DD/YYYY format:

 index="MyIndex" sourcetype="MySourceType" source="MySource" 
| chart count(eval(FLAG=="O")) as Outbound count(eval(FLAG=="I")) as Inbound dc(provider_name) as Providers by creation_date_time

Following is the run anywhere example based on cooked up data as per my understanding of your data. Each event has creation_date_time in MM/DD/YYYY format. FLAG O and I denote Outbound and Inbound respectively and provider_name can repeat, where you want unique provider count.

PS: Intention of run anywhere search is to demo the required output. Instead of commands from | makeresults till | KV, you will use your main search using index="MyIndex" sourcetype="MySourceType" source="MySource", as mentioned above.

| makeresults
| fields - _time
| eval data="CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval _time=strptime(CREATION_DATE_TIME,"%m/%d/%Y")
| timechart count(eval(FLAG=="O")) as Outbound count(eval(FLAG=="I")) as Inbound dc(provider_name) as Providers

Re: How do you translate SQL into Splunk?

mnikhil7692 — Tue, 29 Sep 2020 21:51:57 GMT

@niketn , thanks for your reply. I did find my solution ; it was hiding all along in plain sight. Some of my fields were in lowercase i.e. dc(provider_name) instead of dc(PROVIDER_NAME).
But all the info and the links you have provided is very useful, for someone who is starting off, like me. Cheers !

Re: How do you translate SQL into Splunk?

niketn — Tue, 06 Nov 2018 04:46:13 GMT

@mnikhil7692, did you get a chance to try out the answer?

Re: How do you translate SQL into Splunk?

mnikhil7692 — Tue, 06 Nov 2018 11:50:05 GMT

Hi Niket, i did. i replied back on the post that i found the solution.
Thanks for all your help.