I have a lookup file that is a Master List of all sites I might need to create reports for in Splunk. This list includes data such as site name, account manager, etc that is not indexed in Splunk. This list is created by a dbxquery to our warehouse tables. I have it run once a week to recreate the list in case new sites were added that week. What I am looking to do is have an email sent to me when new sites are added. I cannot just append the new sites since previous sites might have information updated such as account manager or software version. Below is my start and thoughts on the logic of how to achieve this but I'm struggling to work out the details. Hoping one of you can help me out.
|dbxquery query="select site_id from table" connection="Warehouse"
|rename site_id as propertyId
NOT propertyId IN ([|inputlookup MasterList.csv
|sendemail (of new propertyIds)
|dbxquery query="FULL QUERY OF ALL NEEDED FIELDS"
My first issue is that I can't get the NOT to work after the dbxquery since its not searching the index in Splunk. So how do I compare the new query to the lookup file?
I think I can figure out the sendemail once I have some results.
Then can I have the full query re-run to include all fields I want in the lookup file and recreate the MasterList?
Is there a better way to achieve what I want to do?
Let me know if you need more explanation of what I'm trying to do and thanks for any help.