topic Re: How to compare each day's events to lookup table values and return differnces? in Splunk Search

How to compare each day's events to lookup table values and return differnces?

schilds427 — Fri, 15 Jan 2021 19:19:20 GMT

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.

Re: How to compare each day's events to lookup table values and return differnces?

to4kawa — Fri, 15 Jan 2021 22:05:33 GMT

index=yours | lookup A as B OUTPUT C | where NOT C!=""

There are some fields that are only available in lookup, so filter them.

Re: How to compare each day's events to lookup table values and return differnces?

schilds427 — Fri, 15 Jan 2021 23:40:29 GMT

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.

Re: How to compare each day's events to lookup table values and return differnces?

to4kawa — Sat, 16 Jan 2021 00:10:43 GMT

That's fine because you can see the results, but we don't know anything.
You can do it.