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.
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
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.
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.
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.