eval on two columns in a lookup with host and time

[middlemiddle]

I have a search pulling back fields "file_type" and "host", I have set "event_hour" and doing a stats so I know the number of files by file_type & host every hour per host.  Search below, this works.

I have thresholds in a lookup table by "file_type", "host", and by <<hour>>.  I want to alert and build a dashboard if I do not have the number of files (stats) for each hour (event_hour) in the lookup (<<hour>>).  

This is the portion I have that is pulling the threshold, I need help with:

1) it is not currently using "host" and grabbing the first "file_type" in the lookup.  Is it possible to to eval with AND while reading the lookup? (see RED)
2) I would like to use "event_hour" instead of "previous_hour" so that I can compare any days 10:00 to the lookup tables 10:00 as an example?

3) if there are any efficiencies you see please let me know, I'm open to suggestions.

| foreach * [ eval file_threshold_current_hour=if("<<FIELD>>"=previous_hour AND host=<<host>>,'<<FIELD>>',file_threshold_current_hour)]

Primary Search To Date:

earliest=-h@h latest=@h
[| inputlookup dd_app_file_management_monitoring_fundlinx.csv
| where enabled=1
| fields index sourcetype host]

| transaction source

| eval event_hour=strftime(_time, "%H:00")
| eval event_day=strftime(_time, "%Y/%m/%d")
| eval previous_hour=strftime((relative_time(now(),"-1h")),"%H:00")

| stats count AS files_per_hour by file_type event_hour event_day host

| join type=outer
[
| inputlookup dd_app_file_management_monitoring_fundlinx.csv
| where enabled=1
| eval previous_hour=strftime((relative_time(now(),"-1h")),"%H:00")
| foreach * [ eval file_threshold_current_hour=if("<<FIELD>>"=previous_hour,'<<FIELD>>',file_threshold_current_hour)]
| fields file_type previous_hour file_threshold_current_hour 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
]

| table previous_hour event_hour event_day host file_type file_threshold_current_hour files_per_hour 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00

I have thresholds defined in a lookup table per hour by host and file_type, I would like 

<search string>
| eval event_hour=strftime(_time, "%H:00")
| stats count AS files_per_hour by file_type event_hour host

[bowesmana]

I'm not across the subtleties of your search without seeing your data, but I am not sure why you just can't use the lookup file as a lookup rather than using the join statement

| eval enabled=1
| lookup dd_app_file_management_monitoring_fundlinx.csv file_type host enabled
| foreach * [ eval file_threshold_current_hour =if("<<MATCHSTR>>"= current_hour, '<<FIELD>>', file_threshold_current_hour) ]

Perhaps because you're joining outer  - but that can be solved with an append on the end

You can't use the outer search host in the join host, but that's why lookup+append will give you what I think you're looking for.

[middlemiddle]

The primary reason I'm using JOIN is the case is different between the host in the environment (non-standard, some are UPPER some lower and some mixed) and what is in the lookup table, I also can't guarantee the case in the lookup as multiple folks are updating it.  I find programmatic solutions around user input is the best method, i.e. upper (or lower) everything in my working stream.  It's a small dataset.

Based on other Answers lookup does not support UPPER/LOWER, you can change the default match NO to YES in transforms.conf, however that would again require the lookup table match the legitimate host name in the env.  I'm sure I could add a transform to do this in flight as it's writing to the Index, however knowing the actual case of the hostname is important for other use cases.

I'll add another Answer for that question?

I've worked through most of the issues by pulling the extraneous search items and let Splunk do what Splunk does, I essentially broke the defaults that were working.

Where I'm struggling now is the eval case for MonitorStatus, I need to determine <=> between the FILES_PER_HOUR and from the table with the corresponding hour? (See GREEN)

[| inputlookup dd_app_file_management_monitoring_fundlinx.csv
| where enabled=1
| fields index sourcetype host]
| transaction source

| eval event_hour=strftime(_time, "%H:00")
| eval event_day=strftime(_time, "%Y/%m/%d")
```JOIN WILL NOT WORK IF CASE IS NOT CONSISTENT, SETTING "host" & "batch_process" WILL ALLOW THE JOIN TO WORK. YOU HAVE TO SET IT IN BOTH LOCATIONS.```
| eval host=upper(host)
| eval batch_process=upper(batch_process)

| stats count AS files_per_hour by file_type host event_hour event_day host

| join type=outer file_type host
[
| inputlookup dd_app_file_management_monitoring_fundlinx.csv
| where enabled=1
| eval host=upper(host)
| fields host file_type 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
]

```THIS IS CLOSE
| eval MonitorStatus=case(files_per_hour<%event_hour%, "LessThanUsual", files_per_hour=%event_hour%, "OnTarget", files_per_hour>%event_hour%, "MoreThanUsual")```

| table MonitorStatus event_hour event_day host file_type files_per_hour 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00

LOOKUP TABLE

host,file_type,00:00,01:00,02:00,03:00,04:00,05:00,06:00,07:00,08:00,09:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00
windows-clienta-prodapp1,TYPE1,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
windows-clienta-prodapp1,TYPE2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clienta-prodapp1,TYPE3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clienta-prodapp1,TYPE4,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
windows-clienta-prodapp1,TYPE5,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
windows-clienta-prodapp1,TYPE6,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientb-prodapp1,TYPE1,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
windows-clientb-prodapp1,TYPE2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientb-prodapp1,TYPE3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientb-prodapp1,TYPE4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientb-prodapp1,TYPE5,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientb-prodapp1,TYPE6,1,1,1,1,1,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,1,1,1
windows-clientc-prodapp1,TYPE1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientc-prodapp1,TYPE2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientc-prodapp1,TYPE3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientc-prodapp1,TYPE4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientc-prodapp1,TYPE5,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
windows-clientc-prodapp1,TYPE6,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1