I have a search that gets events related to procedures from the past week and organizes them into days. I also have a lookup table which holds the values of the procedure names I want to look for. This lookup table data has no date values, unlike my splunk data, since I want to compare it against every day represented. Search:
(index=app host=host1sourcetype=st1) OR (index=chub source=s2) earliest=-1w@w latest=now
[|inputlookup chubDashboardProcedures.csv | fields 1.0_Procedures | rename 1.0_Procedures as search|format "" "(" "" ")" "OR" ""]
| fields Procedure_Name,Process_Name,Activity_Code, UpdatedDate
| eval Procedure_Name=coalesce(Process_Name, Procedure_Name)
| eval update = strptime( UpdatedDate, "%Y-%m-%d %H:%M:%S")
| eval Day = relative_time(update,"@d") - if((tonumber(strftime(update, "%H%M")) < 1400), (24*60*60), 0)
| dedup Procedure_Name Day
| append [|inputlookup chubDashboardProcedures.csv|fields 1.0_Procedures, UpdatedDate| rename 1.0_Procedures as Procedure_Name_New| eval from="lookup"]
| stats values(Procedure_Name_New) as lookup_procedures values(from) as from count(Procedure_Name) as fromcount values(Procedure_Name) as Procedure_Name_List values(eval(if(Activity_Code !="2000", Procedure_Name,null()))) as Failures by Day
| mvexpand lookup_procedures
| eval missing= if(match(Procedure_Name_List,lookup_procedures), "SAME", lookup_procedures)
| sort - Day
| eval Success_Percent = round(((Success_Count/Procedure_Count)*100),2)
| eval Day = strftime(Day, "%F")
| table Procedure_Name_List, lookup_procedures Failures missing
The lookup table values are there and I can compare them to a list of procedures that haven't been grouped into dates, but as soon as I group into dates I can't compare them anymore. I want to display events that are found in the lookup table but not the events for each day.
That's fine because you can see the results, but we don't know anything.
You can do it.
index=yours
| lookup A as B OUTPUT C
| where NOT C!=""
There are some fields that are only available in lookup, so filter them.
Not sure that works for my situation. I can get the lookup table field values I want and can even run them through stats-values but if I try to group that data as I do my splunk data I can't work with it anymore.
So if I just run :
(index=app host=host1 sourcetype=st1) OR (index=chub source=s1) earliest=-1w@w latest=now
[|inputlookup chubDashboardProcedures.csv | fields 1.0_Procedures | rename 1.0_Procedures as search|format "" "(" "" ")" "OR" ""]
| fields Procedure_Name,Process_Name,Activity_Code, UpdatedDate
| eval Procedure_Name=coalesce(Process_Name, Procedure_Name)
| dedup Procedure_Name
| append [|inputlookup chubDashboardProcedures.csv|fields 1.0_Procedures| rename 1.0_Procedures as Procedure_Name_New]
| stats values(Procedure_Name_New) as Lookup_Procedure_List values(Procedure_Name) as Procedure_List count(eval(Activity_Code = "2000")) as Success_Count, values(eval(if(Activity_Code !="2000", Procedure_Name,null()))) as Failures, count as Procedure_Count
| table Procedure_List Lookup_Procedure_List
That gives me two lists: one that's the procedures from splunk and one that's the procedures from my lookup table. But if I try grouping the data by date my lookup procedure list becomes empty.