Translate sql to Splunk search language

[dwong2]

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"

[kmaron · Accepted solution]

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]

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

[kmaron]

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

If not let me know so I can help.