About middlemiddle

middlemiddle · ‎09-24-2021

That is the documented way to only have one Field in the outputlookup command. What I need is the table command to have all the fields referenced so the Alert has context, however only have one field in the outputlookup to go to the .csv file. The search is not critical, all that remains are the fields within the table; both the table and the outputlookup are non-streaming commands. search .... | table monitor_status current_time current_day file_name file_cutoff_time host last_file_time | outputlookup.....

middlemiddle · ‎09-24-2021

I have an alert that joins RAW events with a lookup containing thresholds (and yes, it has to be a join). I would like to take one field from the alert details, last_file_time, and outputlookup only that field back to the root lookup table. Question: Is there a way to only outputlookup a single field from the table output? Example: | inputlookup MyFileThresholds.csv | join type=left file_name [ search ....... eval last_file_time=strftime(_time, "%x %T") ] | table monitor_status current_time current_day file_name file_cutoff_time host last_file_time | outputlookup append=false MyFileThresholds.csv <-I only want last_file_time going back to the root lookup table.

middlemiddle · ‎08-24-2021

space separated.

middlemiddle · ‎08-24-2021

I'm using the following to eval current_day: | inputlookup Files_And_Thresholds | eval current_day=lower(strftime(relative_time(now(),"@s"),"%A")) I have a column in a lookup file (.csv) with days '"file_days" I would like to search across, I can not figure out why this will not search? If I replace current_day with the string "tuesday" it works fine? | makemv delim=" " file_days | search file_days=current_day lookup table: file_cutoff_time file_days file_name 23:00:00 thursday wednesday FILE001.CSV 22:00:00 friday monday thursday tuesday wednesday FILE002.CSV

middlemiddle · ‎06-29-2021

I think I have it, see below. The trick was min/max of seconds prior to converting it to time (using duration). | eval seconds=_time%(60*60*24) | stats min(seconds) AS "earliest_time_seconds" , max(seconds) AS "latest_time_seconds", count AS number_of_files, by client_file_name | eval match=if(earliest_time=latest_time,"Yes", "No") | eval diff_seconds=latest_time_seconds - earliest_time_seconds | eval earliest_time=tostring(earliest_time_seconds, "duration") | eval latest_time=tostring(latest_time_seconds, "duration") | eval difference_in_time=tostring(diff_seconds, "duration")

middlemiddle · ‎06-29-2021

Thank you @ITWhisperer . Any idea on how to convert the seconds into HH:MM:SS? I can use the seconds to get earliest/latest, however when I use the following to get HH:MM:SS they become strings and I'm struggling getting earliest/latest with a string? | eval date4=tostring(date1, "duration") | eval date2=date_hour.":".date_minute.":".date_second | strcat date_hour ":" date_minute ":" date_second date3 | table client_file_name date1 date2 date3 date4

middlemiddle · ‎06-25-2021

I want to set dynamic SLA's for File Processing. In order to do this I need to: 1. get the earliest HH:MM:SS the job has processed in the last 30 days. 2. get the latest HH:MM:SS the job has processed in the last 30 days. 3. get the average time the jobs process in the last 30 days. 4. get the difference between the earliest & latest. Most of what I have found around stats with earliest & latest includes the date, so I end up with the time the job ran on day 1 and day 30. I need the earliest/latest by HH:MM:SS and then diff it?

middlemiddle · ‎06-24-2021

This works locally, thank you very much. I ended up adding a column to the lookup called "monitor_windows" and using the following, not as elegant as your approach but I have other fields in the table I did not add to the initial question and this gets it down to fewer lines in the search. monitor_windows 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 | eval event_hour=split(monitor_windows, ",") | mvexpand event_hour | foreach * [ eval event_hour_threshold=if("<<FIELD>>"=event_hour,'<<FIELD>>',event_hour_threshold)]

middlemiddle · ‎06-21-2021

I need to create a field "search_hours" with values for every hour in (%H:00) format within the search window, whether I have event data or not for that hour. If I search earliest=@D latest=now at say noon I should have a field "search hours" with values 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.

middlemiddle · ‎06-21-2021

I have a lookup with file_type name and a threshold per hour as the headers, like below. I would like the hour headers converted to a new column "time_interval" Current lookup: file_type,0:00,1:00,2:00,3:00,4:00,5:00,6:00,7:00,8:00,9: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 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 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 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 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 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 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 New lookup: file_type time_interval file_threshold TYPE1 0:00 0 TYPE1 1:00 0 TYPE1 2:00 0 TYPE1 3:00 0 TYPE1 4:00 0 TYPE1 5:00 0 TYPE1 6:00 0 TYPE1 7:00 0 TYPE1 8:00 8 TYPE1 9:00 8 TYPE1 10:00 8 TYPE1 11:00 8 TYPE1 12:00 8 TYPE1 13:00 8 TYPE1 14:00 8 TYPE1 15:00 8 TYPE1 16:00 8 TYPE1 17:00 8 TYPE1 18:00 0 TYPE1 19:00 0 TYPE1 20:00 0 TYPE1 21:00 0 TYPE1 22:00 0 TYPE1 23:00 0

middlemiddle · ‎06-10-2021

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

middlemiddle · ‎06-09-2021

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

middlemiddle · ‎05-27-2021

Works like a charm, thank you.

middlemiddle · ‎05-26-2021

I have a lookup with the files that should be sent each hour (common/flat file names) with the hour as the header, I would like to use an eval to set current hour to use to pull back thresholds in lookup with # of files for that hour: | eval current_hour=strftime(now(),"%H:00") Use the above with inputlookup to pull back fields "field file_names 14:00" as an example: | inputlookup file_monitoring_.csv | fields $current_hour$ lookup is like below: file_name,00:00,01:00,02:00,03:00,etc... file001.csv,5,10,15,20,etc.... file002.csv,0,0,0,1,etc.... file007.csv,105,206,409,727,etc.... file009.csv,1,2,3,4,etc....

Posts	15
Solutions	0
Karma Given	1
Karma Received	0
Member Since	‎05-26-2021

Online Status	Offline
Date Last Visited	‎11-16-2022 10:42 AM

outputlook with a subset of fields

Search eval strftime result of current day across ...

earliest and latest HH:MM across multiple days

create event for every hour in a search window

inputlookup convert columns to rows

eval on two columns in a lookup with host and tim...

eval strftime to create current_hour (HH:MM) to be...

Re: outputlook with a subset of fields

outputlook with a subset of fields

Re: Search eval strftime result of current day acr...

Search eval strftime result of current day across ...

Re: earliest and latest HH:MM across multiple days

Re: earliest and latest HH:MM across multiple days

earliest and latest HH:MM across multiple days

Re: inputlookup convert columns to rows

create event for every hour in a search window

inputlookup convert columns to rows

Re: eval on two columns in a lookup with host and...

eval on two columns in a lookup with host and tim...

Re: eval strftime to create current_hour (HH:MM) t...

eval strftime to create current_hour (HH:MM) to be...