How to use a variable for an array

Follow

John McPherson

• October 23, 2020 17:23
• Edited

I am new to queries and Sumo altogether. I have a list of vulnerability IDs that I want to reference across multiple dashboard objects. It would function as a selection filter. 

I want to either include or exclude the results based upon those IDs.

Previously I would do 

| where !(QID in ("90007","90482", "91540", "91671", "100086", "100087", "100093"," 100368", "100369", "105145", "116368", "117077", "118425", "119518", "124194", "370469", "370610", "370727", "370887", "371079", "371265", "371528", "371749", "371750", "372013", "372014","372163", "372220", "372333"))

This works perfectly but I have 20 objects that I have to update on 15 different dashboards. I recently started successfully using the new dashboard with template variables. I am wondering how do a lookup against a static list so I only have to modify the single list in one place and not across all those objects in every dashboard.

I have tried using

| where !(QID in ({{qid_set}})   // where qid_set is a template - variable type - custom list but it doesn't like * and I don't want to select a single QID.

I have done many Google searches and read many Sumo labs and articles but I'm just not getting it. Any help would be greatly appreciated.

0

• 

  Shobhit Garg

  • October 26, 2020 05:09

  Hi John,

  I can understand that it can be a little painful.

  So what you can do is create a lookup file and put all those QID there.

  There are two ways you can create lookup

  1. This is classic method

  https://help.sumologic.com/05Search/Search-Query-Language/Search-Operators/lookup-classic

  2. Newly introduced Lookup tables, with more functionalities.

  https://help.sumologic.com/05Search/Lookup_Tables/01_Create_a_Lookup_Table

   

  Then you can simply use lookup operator to compare the values

  https://help.sumologic.com/05Search/Search-Query-Language/Search-Operators/lookup

  In your case the query would be like, please see https://help.sumologic.com/05Search/Search-Query-Language/Search-Operators/lookup

  ..initial query

  | lookup qid_lookup from https://company.com/userTable.csv on qid=qid_lookup

  | if isnull(qid_lookup,"ignore") as qid_lookup

  | where qid_lookup="ignore"

   

  Let me explain a bit what we are doing.

  By default lookup has an OUTER JOIN, so it will show NULL for those qid which are not there in lookup file and we are interested in them, so we replace NULL with "ignore" and then we put a where condition to only include those records which do not exist in lookup file. That means we are interested in "ignore" only.

  Incase you want to work on the qid which are included in the lookup file then you just need to change the "where" condition to 

  | where qid_lookup != "ignore"

  As you are going to use this in dashboards, we do have some lookup limitations with dashboards

  https://help.sumologic.com/05Search/Search-Query-Language/Search-Operators/lookup

  Dashboard limitation 

  The lookup operator behaves differently when used in live mode versus interactive mode or an interactive search. When used in live mode the lookup operation is done continually to provide real-time results. However, only the most recent data point is looked up in real time, while the previous data points keep their previously looked up result. An interactive search will conduct the lookup operation on all data points when the query is processed. Therefore, when you compare live mode results to interactive results you will likely see differences in your lookup results.

  For example, say you're plotting the average price of a stock over the last 30 days.

  In live mode, lookup returns the real-time price and retains the previously looked up data points during the 30 day period.

  In an interactive search, lookup will only use the real-time stock price to plot over the past 30 days. In this case, you would have to provide the previous stock prices for the past 30 days.

  In other words, in live mode, lookup will use and retain the lookup data at that point in time when it ran. Whereas lookup in an interactive search will only use the data that was available when it ran.

   

  0

  Comment actions Permalink
• 

  Shobhit Garg

  • October 26, 2020 09:49

  Hi John,

  There is little correction in the query

   

  ..initial query

  | lookup qid_lookup from https://company.com/userTable.csv on qid=qid_lookup

  | if (isNull(qid_lookup), "ignore", qid_lookup) as qid_lookup

  | where qid_lookup="ignore"

  0

  Comment actions Permalink
• 

  John McPherson

  • October 26, 2020 17:09

  Shobhit Garg Thank you so much! I will try and implement what you have suggested and let you know how it goes!

  0

  Comment actions Permalink

Please sign in to leave a comment.