topic Re: How to make a new list of values that are in one list but not another/ in Splunk Search

How to make a new list of values that are in one list but not another/

schilds427 — Sat, 16 Jan 2021 17:19:49 GMT

I have created two lists from stats-list and stats-values. These are called Lookup_Vals(from lookup table's Lookup_procedures field) and Originals(from splunk search Procedure_Name field). I want a new list that is made up of values in the Lookup_Vals list but NOT in the Originals list. I've tried using match command but that just tells me if the lists are the same or not. I've also tried using "List(eval(if(IN(Lookup_procedures,Originals),"Match","No Match"))) as Missing" but that doesn't seem to work either. The if statement resolves to false every time even though I know the lists are mostly the same. Full search:

|search index here | fields Procedure_Name,Process_Name,Activity_Code, UpdatedDate | eval Procedure_Name=coalesce(Process_Name, Procedure_Name) | stats count by Procedure_Name | append [|inputlookup chubDashboardProcedures.csv |rename 1.0_Procedures as Lookup_procedures| eval count=0|fields Lookup_procedures count] | stats sum(count) as total, List(Lookup_procedures) as Lookup_Vals, Values(Procedure_Name) as Originals, Values(eval(if(IN(Lookup_procedures,Originals),"Match","No Match"))) as Missing

I've also true using mvjoin(Originals, ",") command on the Originals but that doesn't seem to help either.

Re: How to make a new list of values that are in one list but not another/

bowesmana — Sun, 17 Jan 2021 22:19:32 GMT

@schilds427

Originals will not exist during the stats statement

|search index here | fields Procedure_Name,Process_Name,Activity_Code, UpdatedDate | eval Procedure_Name=coalesce(Process_Name, Procedure_Name) | stats count by Procedure_Name | append [|inputlookup chubDashboardProcedures.csv |rename 1.0_Procedures as Lookup_procedures| eval count=0|fields Lookup_procedures count] | stats sum(count) as total, values(Lookup_procedures) as Lookup_Vals by Procedure_Name | eval Missing=if(isnull(mvfind(Lookup_Vals,Procedure_Name)), 1,0) | stats values(Procedure_Name) as Procedure_Name by Missing

Your final stats statement does not have any split by clause, so at best you would get a single row with a set of lookup values, a set of original values and one or two words indicating match or no match with no tie up between the matching and non matching lines.

In the above, the stats command will 'join' the lookup data and the indexed data and add the lookup to each procedure_name row, then the missing evaluation can be done after that using mvfind().

Then you can do whatever aggregation you need, i.e. above it will create up to two rows of Procedure_Name values for each missing set.

Hope this helps

Re: How to make a new list of values that are in one list but not another/

to4kawa — Mon, 18 Jan 2021 08:17:28 GMT

>I want a new list that is made up of values in the Lookup_Vals list but NOT in the Originals list

Why don't we just coalesce the fields we want to match and summarize them in stats?

However, it seems like you're taking some kind of quiz when you only present the query.

Re: How to make a new list of values that are in one list but not another/

schilds427 — Tue, 19 Jan 2021 13:56:04 GMT

Thank you, this was very helpful. I also wanted to further group my procedures by the day they happened but it seems like you can't do that and include procedures that didn't happen at all since those procedures obviously won't have date information.

Re: How to make a new list of values that are in one list but not another/

to4kawa — Tue, 19 Jan 2021 21:16:39 GMT

I need to see the logs to make that query.
If you're asking if it's possible, yes, it is.
1 If it's a day-by-day query, you can create a separate field for the day and use that to aggregate.
2 To aggregate "no", create a separate lookup and compare.

Re: How to make a new list of values that are in one list but not another/

schilds427 — Wed, 20 Jan 2021 14:58:45 GMT

So my events have fields that look like this:

2021-01-05 05:00:00.666, Auto_Status_ID="502", Process_Name="usp_Balance_PPS", Procedure_Name="Balance PPS", Process_Start_Time="2021-01-03 19:44:54.007", Process_End_Time="2021-01-03 20:14:07.723", Activity_Code="2000", Result_Description="Process Complete", CreatedDate="2019-05-17 16:16:12.3", UpdatedDate="2021-01-03 20:14:07.723"

I can do as you suggest and make a new "Day" field using the UpdatedDate field as a base, and that works for grouping events that did happen, but how would I then list procedures that didn't happen within those groups since those don't have UpdatedDate or any other field?

Re: How to make a new list of values that are in one list but not another/

to4kawa — Thu, 21 Jan 2021 08:41:34 GMT

sample:

https://docs.splunk.com/Documentation/Splunk/8.1.1/SearchReference/Inputlookup

try append=T