Developing for Splunk Enterprise

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

View solution in original post

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!