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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...