How can I use a temporary table in Splunk?

[raghu0463]

How can i use Common Table Expressions? i need to store my result in temporary table and use that result later on in the search

[DalJeanis · Accepted solution]

Heh. CTEs are cool, but they are an SQL way of doing things. Here, you are going to use subsearches, or outputcsv, or collect, or appendpipe, or a number of other special features of the splunk language to achieve the same thing.

Use collect when you have reason to keep the results of your search and refer to it for a long time afterward. You can think of it as creating a permanent summary table.

Use outputcsv or outputlookup when you need to create a temporary or short-term storage of data. If you are running a clustered shop and the file needs to be available on a different search head, you pretty much must use outputlookup. If the searches are all going to run on the same place, then either will work, and csv is slightly simpler. There's no inherent expiration data on these kind of files, so you need to remember to clean them up occasionally.

If you need to collect certain information and then use that to select OTHER information, then subsearch with the format command may be the right solution, either with or without putting the data to a csv first.

The other language features aren't easy to give broad rules of thumb in the context of your question. It's a darn big toolkit, and there's plenty of doohickeys to pull out when the right opportunity arrives.

[niketn]

Could you please provide more context in terms of what kind of results to be stored in table? I am not sure if this fits in to your requirement... Please check outpulookup command to push search results to a csv file and that csv file can be referenced by another search commands.

[raghu0463]

thanks, will try this