Splunk Search

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

schilds427
Explorer

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. 

Labels (6)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust

>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.

 

View solution in original post

to4kawa
SplunkTrust
SplunkTrust

>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.

 

View solution in original post

schilds427
Explorer

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. 

0 Karma

to4kawa
SplunkTrust
SplunkTrust

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.

schilds427
Explorer

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? 

0 Karma

to4kawa
SplunkTrust
SplunkTrust

sample:

index=_internal component!=Metrics
| stats min(_time) as start max(_time) as end by component
| convert ctime(start) ctime(end)
| eval fm="search"
| append [| makeresults
| eval component=split("Metrics#LMSlaveInfo","#")
| mvexpand component
| fields - _time
| eval fm="lookup"]
| eventstats values(fm) as fm dc(fm) as count by component

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

try append=T

bowesmana
Influencer

@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