Using 'like' to find lookup fields which contain the current day of the week (%A)

[TBH0]

Hey all, I'm attempting to compare a variable (we'll call it cDOW), which is set to (strftime(now(), "%A")),  to a DOW field in a lookup file which contains 1 or more days of the week. Here is what I am using currently to include fields in the results which have a DOM or DOW field, or which have them filled with NA:

| eval cDOM=strftime(now(), "%d")
| eval cDOW=strftime(now(), "%A")

| where (DOM like cDOM OR DOM="NA") AND (DOW like cDOW OR DOW="NA")

This works fine for fields which match exactly (e.g. DOW=Wednesday, cDOW=Wednesday), but does not work if the DOW field contains multiple days of the week (as many will due to this lookup file being a schedule of jobs). the DOM field will only ever have the exact number day of the month, but the DOW field will often contain 1-5 days, and I'd like to have this 'where' statement return fields which contain the current day of week regardless of how many days are listed.

I've tried utilizing wildcards, but can't syntactically figure this out since it's comparing an eval variable to a lookup field and there is no static values. Trying to append wildcards to a relative time in the where statement itself also does not work syntactically.

Any ideas on how to easily accomplish this?

[yuanliu]

Can you show the code from your lookup to this search?  The command | where (DOM like cDOM OR DOM="NA") AND (DOW like cDOW OR DOW="NA") should work as you desired if multivalued DOW contains one value that is an exact match of cDOW.  Maybe you can illustrate an example when DOW has multiple values, like showing

| where mvcount(DOW) > 1
| table DOW cDOW

[TBH0]

So when I search the lookup for mvcount(DOW) > 1, there are 0 results. When I have it show for >0, it shows all results, including those with multiple days. 

I'm beginning to suspect this is a delimiter issue or something similar. My excel sheet actually has the days listed with line breaks between them rather than just spaces, commas, etc.. I'm unsure how line breaks are actually seen within Excel or within Splunk but maybe this is the issue?

I will try to modify that field to have spaces and/or commas delimiting the days and see if that helps.

[yuanliu · Accepted solution]

If mvcount(DOW) > 1 shows no results, it means that DOW is not truly multivalue; it probably has a delimiter separating each string for day of week?  If you can identify that delimiter, you can split the field into multivalue and use your original search.

For example, if the field value is something like "Monday,Wendesday,Friday", i.e., uses a single comma (, no space) as delimiter, you can do

| eval cDOM=strftime(now(), "%d")
| eval cDOW=strftime(now(), "%A")
| eval DOW = split(DOW, ",")
| eval DOM = split(DOM, ",") ``` in case DOM is similarly constructed ```
| where (DOM like cDOM OR DOM="NA") AND (DOW like cDOW OR DOW="NA")

Hope this helps. 

[TBH0]

This is actually working perfectly after adding comma delimiters and using split(DOW, ",").

Thank you!!

[yuanliu]

I'm beginning to suspect this is a delimiter issue or something similar. My excel sheet actually has the days listed with line breaks between them rather than just spaces, commas, etc.. I'm unsure how line breaks are actually seen within Excel or within Splunk but maybe this is the issue?

I should have noticed the notion of line break as delimiter.  All that was needed was to use newline in the split function so you did not have to change your spreadsheet.  The code would look something like

| eval DOW = split(DOW, "
"), DOM = split(DOM, "
")

Of course, if it's not a big pain to change from newline to comma in spreadsheet, you can use comma which looks more obvious in Splunk.