Knowledge Management

I'm having trouble understanding how to translate these sql statements into SPL.

dwong2
New Member

@Query("SELECT COUNT(DISTINCT e.guid) FROM Log e WHERE e.launchPoint IN ?1 AND tile is not null AND e.assetId IS NULL AND e.destination LIKE 'page:%' AND e.dateTime BETWEEN ?2 AND ?3")
Long countUniqueGuidFolderClicksBetweenDates(Set launchPoint, ZonedDateTime start, ZonedDateTime end);

@Query("SELECT COUNT(DISTINCT e.account) FROM Log e WHERE e.launchPoint IN ?1 AND tile is not null AND e.assetId IS NULL AND e.destination LIKE 'page:%' AND e.dateTime BETWEEN ?2 AND ?3")
Long countUniqueAccountFolderClicksBetweenDates(Set launchPoint, ZonedDateTime start, ZonedDateTime end);

SELECT count(*) FROM Log e WHERE tile is not null AND e.dateTime BETWEEN ?3 AND ?4 ORDER BY e.dateTime and assetid == null and e.destination LIKE 'page:%' group by action

Tags (1)
0 Karma
1 Solution

kmaron
Motivator

These are nearly the same queries you asked before in https://answers.splunk.com/answers/626834/translate-sql-to-splunk-search-language.html

your select count(distinct xx) becomes: | stats dc(xx)
select count(*) becomes: | stats count
your from and your where are the base search
tile is not null translates to: tile=*
e.assetId IS NULL translates to: NOT e.assetID=*
to do a like use and equals and replace the % with an asterisk
And then your timeframe can either be denoted using earliest and latest or use the time picker
To do a group by make add a by to your stats command: `| stats count by action

View solution in original post

0 Karma

kmaron
Motivator

These are nearly the same queries you asked before in https://answers.splunk.com/answers/626834/translate-sql-to-splunk-search-language.html

your select count(distinct xx) becomes: | stats dc(xx)
select count(*) becomes: | stats count
your from and your where are the base search
tile is not null translates to: tile=*
e.assetId IS NULL translates to: NOT e.assetID=*
to do a like use and equals and replace the % with an asterisk
And then your timeframe can either be denoted using earliest and latest or use the time picker
To do a group by make add a by to your stats command: `| stats count by action

0 Karma

kmaron
Motivator

If this helped you please accept the answer.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...