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!

Preparing your Splunk Environment for OpenSSL3

The Splunk platform will transition to OpenSSL version 3 in a future release. Actions are required to prepare ...

Unleash Unified Security and Observability with Splunk Cloud Platform

     Now Available on Microsoft AzureThursday, March 27, 2025  |  11AM PST / 2PM EST | Register NowStep boldly ...

Splunk AppDynamics with Cisco Secure Application

Web applications unfortunately present a target rich environment for security vulnerabilities and attacks. ...