topic Re: Return something when search doesn't return anything in Splunk Search

Return something when search doesn't return anything

jordans — Tue, 29 Nov 2011 23:18:48 GMT

I want to have a table with results of a search of the SQL logs for backups. But the search I have only returns the successful backups. I want to display a line in the table if a backup failed, i.e. if a database name does NOT appear in the search results.

Is this possible? I tried an 'append', but the last line is repeated if the search term is not found.

Re: Return something when search doesn't return anything

eelisio2 — Wed, 30 Nov 2011 16:03:29 GMT

Do you have (or can generate) a list of all Database Names?
You could index that list.
Extract the database name from the backup logs. Use the same fieldname (e.g., db_name) in both sourcetypes (the list of all databases and the backup log)

The following should give you a list of database names from the alldatabases sourcetype that aren't matched in the backuplog sourcetype.

sourcetype="alldatabases" | join type=outer db_name [search sourcetype=backuplog] | search sourcetype=alldatabases | table db_name

Re: Return something when search doesn't return anything

jordans — Wed, 30 Nov 2011 18:32:57 GMT

I don't think the set math works. I get a list of all databases from the database list.

To test, I added a bogus database name to a txt file of valid db names, indexed it, and performed your search.

Re: Return something when search doesn't return anything

jordans — Wed, 30 Nov 2011 18:51:27 GMT

My own comment of 'set math' set me on a path to the "set union" command. I now search for successes and failures and combine to a single table.

I have not answered my original question, but I have a work around for my current problem.

Re: Return something when search doesn't return anything

eelisio2 — Wed, 30 Nov 2011 19:27:21 GMT

I'm glad that you found a different solution. The search from my answer will work as well. There needs to be a common field name for the join command to work.

Note that if you run my search without " | search sourcetype=alldatabases | table db_name", the result set has a mix of the 2 sourcetypes. The rows with the sourcetype="alldatabases" did not find a match in the other sourcetype. So, the extra search command at the end should filter the results accordingly.