Splunk Dev

Translate sql to Splunk search language

dwong2
New Member

How can this sql "like" query be translated into something the Splunk search language can understand?

select count(*) from LOG start from X time till Y time
select uniquecount(deviceGUID) from LOG start from X time till Y time
"SELECT COUNT(DISTINCT e.account) FROM Log e WHERE e.launchPoint IN ?1 AND e.action = 'Tile Selection' AND e.assetId IS NULL AND e.destination LIKE 'page:%' AND e.dateTime BETWEEN ?2 AND ?3"

Tags (1)
0 Karma
1 Solution

kmaron
Motivator

These are a bit generic since your SQL is a bit generic but hopefully it will help you get the gist.

select count(*) from LOG start from X time till Y time

index=LOGindex sourectype=LOGsourcetype earliest=X latest=Y | stats count 

Fill in the index and/or sourcetype that correspond to your LOG.
Fill in the earliest and latest times that correspond to X and Y or use the time picker
stats count gets you the count(*)

select uniquecount(deviceGUID) from LOG start from X time till Y time

index=LOGindex sourectype=LOGsourcetype earliest=X latest=Y | stats dc(deviceGUID)

Same as the one above only use stats dc to get a distinct count

"SELECT COUNT(DISTINCT e.account) FROM Log e WHERE e.launchPoint IN ?1 AND e.action = 'Tile Selection' AND e.assetId IS NULL AND e.destination LIKE 'page:%' AND e.dateTime BETWEEN ?2 AND ?3"

index=Logelocation sourcetype=Logesourcetype launchPoint=1 action="Title Selection" NOT assetId=* destination="*page:*" earliest="12/11/2012:20:00:00" latest="15/11/2012:20:00:00" | stats dc(e.account)

index and/or sourcetype for the log as above.
Your where criteria goes in the search (before the pipe)
Using wildcards can mimic a LIKE search - destination="page:"
earliest and latest can be filled in as dates or as relative
and then your distcint count

View solution in original post

kmaron
Motivator

These are a bit generic since your SQL is a bit generic but hopefully it will help you get the gist.

select count(*) from LOG start from X time till Y time

index=LOGindex sourectype=LOGsourcetype earliest=X latest=Y | stats count 

Fill in the index and/or sourcetype that correspond to your LOG.
Fill in the earliest and latest times that correspond to X and Y or use the time picker
stats count gets you the count(*)

select uniquecount(deviceGUID) from LOG start from X time till Y time

index=LOGindex sourectype=LOGsourcetype earliest=X latest=Y | stats dc(deviceGUID)

Same as the one above only use stats dc to get a distinct count

"SELECT COUNT(DISTINCT e.account) FROM Log e WHERE e.launchPoint IN ?1 AND e.action = 'Tile Selection' AND e.assetId IS NULL AND e.destination LIKE 'page:%' AND e.dateTime BETWEEN ?2 AND ?3"

index=Logelocation sourcetype=Logesourcetype launchPoint=1 action="Title Selection" NOT assetId=* destination="*page:*" earliest="12/11/2012:20:00:00" latest="15/11/2012:20:00:00" | stats dc(e.account)

index and/or sourcetype for the log as above.
Your where criteria goes in the search (before the pipe)
Using wildcards can mimic a LIKE search - destination="page:"
earliest and latest can be filled in as dates or as relative
and then your distcint count

dwong2
New Member

@kmaron Thank you for the quick response. I'll give it a try.

0 Karma

kmaron
Motivator

Did these work out for you? If they did please accept my answer.

If not let me know so I can help.

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 ...