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 |table propertyId |format]) |sendemail (of new propertyIds) |dbxquery query="FULL QUERY OF ALL NEEDED FIELDS" |rename........ |outputlookup MasterList.csv
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.
With a few suggestions from a coworker, I was able to figure this one out. Here's how I accomplished this for anyone who may be looking to do some similar.
|dbquery query="select site_id from table" connection="Warehouse" |rename site_id as propertyId |search NOT propertyId IN ( [|inputlookup MasterList.csv |fields propertyId |mvcombine delim="," propertyId |eval propertyId=mvjoin(propertyId, ",") |return $propertyId]) |sendemail to="myemail@companyABC.com" subject="Added" sendresults=true
I decided to leave this as a separate report and have it run 2 hours prior to the report that creates/updates the overall MasterList.
You could also probably just do a normal lookup command against MasterList.csv and output some field from it "AS" the name of some field you then can pipe to a | where isnull(fieldname)