SPL(二）SPL for sql

SPL(二）SPL for sql

SQL命令	SQL例句	Splunk例句
SELECT *	SELECT * FROM mytable	source=mytable
WHERE	SELECT * FROM mytable WHERE mycolumn=5	source=mytable mycolumn=5
SELECT	SELECT mycolumn1,mycolumn2,FROM mytable	source=mytable | FIELDS mycolumn1, mycolumn2
AND/OR	SELECT * FROM mytable WHERE (mycolumn1=“true” OR mycolumn2=“red”) AND mycolumn3="blue	source=mytable AND (mycolumn1="true" OR mycolumn2="red") AND mycolumn3="blue"
AND/OR		source=mytable (mycolumn1=“true” OR mycolumn2=“red”) mycolumn3=“blue”
AS	SELECT mycolumn AS column_alias FROM mytable	source=mytable |RENAME mycolumn as column_alias | FIELDS column_alias
BETWEEN	SELECT * FROM mytable WHERE mycolumn BETWEEN 1 AND 5	source=mytable mycolumn>=1 mycolumn<=5
GROUP BY	SELECT mycolumn,avg(mycolumn) FROM mytable WHERE mycolumn=value GROUP BY mycolumn	source=mytable mycolumn=value | STATS avg(mycolumn) BY mycolumn | FIELDS mycolumn, avg(mycolumn)
HAVING	SELECT mycolumn,avg(mycolumn) FROM mytable WHERE mycolumn=value GROUP BY mycolumn HAVING avg(mycolumn)=value	source=mytable mycolumn=value | STATS avg(mycolumn( BY mycolumn | SEARCH avg(mycolumn)=value | FIELDS mycolumn,avg(mycolumn)
LIKE	SELECT * FROM mytable WHERE mycolumn LIKE “%some text%”	source=mytable mycolumn=“some text”
LIKE		source=mytable “some text”
ORDER BY	SELECT * FROM mytable ORDER BY mycolumn desc	source=mytable | SORT -mycolumn
SELECT DISTINCT	SELECT DISTINCT mycolumn1,mycolumn2 FROM mytable	source=mytable | DEDUP mycolumn1,mycolumn2 | FIELDS mycolumn1,mycolumn2
SELECT TOP	SELECT TOP(5) mycolumn1,mycolum2 FROM mytable1 WHERE mycolum3 = “BAR” order by MYCOLUM1 MYCOLUM2	source=mytable1 mycolum3="bar | FIELDS mycolum1 mycolum2 | SORT mycolum1 mycolum2 | HEAD 5
INNER JOIN	SELECT * FROM mytable1 INNER JOIN mytable2 ON mytable1.mycolumn=mytable2.mycolumn	index=myIndex1 OR index=myIndex2 | stats values(*) AS * BY myfield
INNER JOIN		| LOOKUP myvaluelookup mycolumn OUTPUT myoutputcolumn
INNER JOIN		source=mytable1 [SEARCH source=mytable2 mycolumn2=myvalue | FIELDS mycolumn2
INNER JOIN		source=mytable1 | JOIN type=inner mycolumn [ SERACH source=mytable2 | RENAME mycolumn2 AS mycolumn
INNER JOIN		index=myIndex1 OR index=myIndex2 | rename myfield1 as myField | stats values(*) AS * BY myField
LEFT(OUTER) JOIN	SELECT * FROM mytable1 LEFT JOIN mytable2 ON mytable1.mycolumn=mytable2.mycolumn	source=mytable1 | JOIN type=left mycolumn [ SEARCH source=mytable2]
SELECT INTO	SELECT * INTO new_mytable IN mydb2 FROM old_mytable	source=old_mytable|EVAL source=new_mytable | COLLECT index=MYDB2
TRUNCATE TABLE	TRUNCATE TABLE mytable	source=mytable | DELETE
INSERT INTO	INSERT INTO mytable VALUES(value1,value2,value3,…)
UNION	SELECT mycolumn FROM mytable1 UNION SELECT mycolumn FROM mytable2	source=mytable1 | APPEND [SEARCH source=mytable2] | DEDUP mycolumn
UNION ALL	SELECT * FROM mytable1 UNION ALL SELECT * FROM mytable2	source=mytable1 | APPEND [SEARCH source=mytable2]
DELETE	DELETE FROM mytable WHERE mycolumn=5	````source=mytable1 mycolumn=5
UPDATE	UPDATE mytable SET column1=value column2=value,… WHERE some_column=some_value