topic Re: what can be used in SPL for decalre variables in SQL . in Splunk Search

what can be used in SPL for decalre variables in SQL .

raghu0463 — Mon, 31 Jul 2017 18:33:06 GMT

im trying to write spl for one of the sql quires which has like declare variables and CTE tables im bit confused what to use in SPL for that.

example :

SQL - declare @prev date
SPL ?

SQL - declare @table1 table(col1 date)
SPL - ?

SQL -
with cte as
(select col1 from table1
union all
select dateadd(day,1,col1) from cte
where col1 < maxdate-1)

how to convert this SQL into SPL pls ?

thanks

Re: what can be used in SPL for decalre variables in SQL .

woodcock — Mon, 31 Jul 2017 18:51:12 GMT

It will be easier for us if you show us the event data and what you would like the final form to be.

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Mon, 31 Jul 2017 19:03:49 GMT

can i get ur email pls, so that i can send it you ..

Re: what can be used in SPL for decalre variables in SQL .

s2_splunk — Mon, 31 Jul 2017 19:16:32 GMT

You don't need to declare variables in Splunk. Just use them. | eval myVar = 12345 makes a new 'variable' (called a field in Splunk) that has a numeric value.
| eval myVar2 = "This is a string" makes a string. Easy. 🙂

Re: what can be used in SPL for decalre variables in SQL .

DalJeanis — Mon, 31 Jul 2017 19:19:10 GMT

A recursive CTE, such as the one you posted, is a fantastic feature of a couple of variants of SQL, and are designed to solve specific problems. The basic answer is, you can't translate a recursive CTE directly. I want to make absolutely clear, the general answer to your general question is, "You CAN'T." You have to figure out what it is doing, and then analyze how to accomplish it in a different, splunky, way.

However, the GREAT news is that this particular CTE is merely accomplishing a task that in splunk is trivial. Specifically, "How do I create a list of dates from the value in col1 to one day less less than the value in maxdate?"

Here's the SPL code for that, assuming that Date1Epoch and Date2Epoch are both already in epoch format...

| eval mysetofdates=mvrange(Date1Epoch, Date2Epoch, 86400)

That creates a single field called mysetofdates that has the entire list, starting from Date1Epoch, and ending BEFORE Date2Epoch.

Now, how you use that multivalue field to meet the rest of your needs is going to depend on the rest of your requirements. You will probably end up using mvexpand, but after that it's anybody's guess.

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Tue, 01 Aug 2017 14:27:39 GMT

then how about the variable table pls ?

Re: what can be used in SPL for decalre variables in SQL .

woodcock — Tue, 01 Aug 2017 14:53:45 GMT

It is in my profile.

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Wed, 02 Aug 2017 19:41:19 GMT

here in the eval mysetofdates will hold all the dates right ?

Re: what can be used in SPL for decalre variables in SQL .

DalJeanis — Wed, 02 Aug 2017 20:38:24 GMT

@raghu0463 - Right, it will be a multivalue field with epoch values of the dates, such as the results of this code...

|makeresults | bin _time span=1d |eval mytime=mvrange(_time-86400*3,_time,86400)

...which looks like...

1501372800
1501459200
1501545600

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Thu, 03 Aug 2017 03:32:32 GMT

Hello DalJeanis,

I have one more question, im trying to ingest data from sql server tables (3) and also date from one view into splunk,
lets say A,B,C are table names
Z is view name
for table c the no.of records in the table are 76500, it has composite primary key. will i able to store all the records in one stretch. if not, what should i use in the rising column.

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Thu, 03 Aug 2017 03:36:38 GMT

hello Daljeanis,

i tried to build spl for the first question, but im getting errors, im bit new to splunk. will you able to provide me your email id pls so that i can send my question to you.

thanks

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Thu, 03 Aug 2017 13:17:36 GMT

@DalJeanis, instead of using _time can i use now(), because _time changes for every event right ?

Thanks

Re: what can be used in SPL for decalre variables in SQL .

DalJeanis — Thu, 03 Aug 2017 13:44:43 GMT

@raghu0463 - let's keep the questions and answers on the forum, where other people can benefit from them, and where I can ask friends for help if they go a direction that isn't my area. Feel free to link up with me on LinkedIn (https://www.linkedin.com/in/daljeanis) and ask me there if it's anything confidential, but at this level of questions, it's better to post it here.

Without seeing the rest of the SQL, I don't know why the CTE code is generating that list of dates. now() would be fine, assuming that the intention is to generate a list of dates from today to just less than some fixed future date.

With regard to your "rising column" question, please post that as a new question so it can get proper attention. Give it a title like "What would I use for rising column in this scenario...?".

Give us non-confidential details about the keys of the three tables, and what significant fields (dates, update date/time etc) are available in those columns. You can post the SQL, clipped back to basics as much as you can without affecting the actual joins, binds and tricky bits.

Re: what can be used in SPL for decalre variables in SQL .

raghu0463 — Thu, 03 Aug 2017 15:43:55 GMT

@DalJeanis

when i was using
|eval mytime=mvrange(now()-86400*90,now(),86400)| eval date = strftime(mytime,"%y/%m/%d") | stats count(date)

im getting the count 59490 , but i think i should get 90. what is the error in the above commands pls