We have the following SPL query which generates statuses (i.e. "Success", "Failure", "Warn") for various different "services" (these are basically files being transferred from a source location to a target location). The requirement is to be able to show the statuses for these various services over a 7 day period since this SPL query shall be used as part of a dashboard to monitor file transfer statuses on a daily basis. The issue we are running into is our inability to generate a value of "Not Run" for the date columns where no transactions occurred. Basically, there is no timestamp for the cron fields nor for the epoch_file_info_created_at/modified_at fields. Currently, we are trying to see how we can populate the blank/non-existent values with an output of "Not Run". Is there a way for us to accomplish this? Here is a sample output which includes a column with blank/non-existent values: Service ID Service Name Priority Service Area Source Target 2020-08-15 2020-08-14 2020-08-13 100 File1 2 SA1 Source1 Target2 Success Not Run 105 File2 1 SA2 Source2 Target1 Warn Success Here are some raw logs with dummy data as well: 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:42:55.746+0000 2020-08-15 13:42:55.746, md5hash="hash1", file_info_rule_id="200", file_id="25", file_name="File1", location="/file/location", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:42:55.746+00", file_info_modified_at="2020-08-15 13:42:58.377+00", filesize="12 MB", id="8", interface_id="Int1", integration_name="FileName1", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 9 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="5", file_type=".csv", expected_rows_counts="90000", priority="Medium", sonc_metadata_created_at="2020-07-13 15:47:30.83+00", sonc_metadata_modified_at="2020-07-22 13:07:04.732+00", sonc_metadata_rule_id="200", s3_folder_path="file/location2", long_processing_time="0.5", service_area="SA1" 2020-08-15T09:41:30.663+0000 2020-08-15 13:41:30.663, md5hash="hash2", file_info_rule_id="225", file_id="24", file_name="filename1", location="file/location/3", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:41:30.663+00", file_info_modified_at="2020-08-15 13:41:33.373+00", filesize="12 MB", id="14", interface_id="INT3", integration_name="FileType4", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 8 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="7", file_type=".x12", priority="High", sonc_metadata_created_at="2020-07-20 15:12:45.625+00", sonc_metadata_modified_at="2020-07-22 13:16:29.969+00", sonc_metadata_rule_id="225", s3_folder_path="file/location/new", long_processing_time="0.5", service_area="SA3" 2020-08-15T09:41:30.663+0000 2020-08-15 13:41:30.663, md5hash="hash2", file_info_rule_id="225", file_id="24", file_name="filename1", location="file/location/3", source_sys_name="Source1", target_sys_name="Target1", validation_status="VALID", transfer_status="Successful", verification_status="Verified", file_info_created_at="2020-08-15 13:41:30.663+00", file_info_modified_at="2020-08-15 13:41:33.373+00", filesize="12 MB", id="14", interface_id="INT3", integration_name="FileType4", source_sys_id="6", target_sys_id="10", early_file_delivery_time="0 8 * * *", late_file_delivery_time="0 16 * * *", recurrence_pattern="0 15 * * *", processing_duration="0.25", short_processing_time="0.05", expected_file_size_threshold="10-15", validation_type="7", file_type=".x12", priority="High", sonc_metadata_created_at="2020-07-20 15:12:45.625+00", sonc_metadata_modified_at="2020-07-22 13:16:29.969+00", sonc_metadata_rule_id="225", s3_folder_path="file/location/new", long_processing_time="0.5", service_area="SA3" Here is the SPL: index=hcnc_rds_db sourcetype=rds_test
| eval epoch_file_info_created_at=strptime(file_info_created_at, "%Y-%m-%d %H:%M:%S.%3Q")-14400, epoch_file_info_modified_at=strptime(file_info_modified_at, "%Y-%m-%d %H:%M:%S.%3Q")-14400
| streamstats latest(*) as * by interface_id, epoch_file_info_created_at
| eval early_start_epoch=relative_time(epoch_file_info_created_at,"@d"), late_start_epoch=relative_time(epoch_file_info_created_at,"@d"), recurrence_pattern_epoch=relative_time(epoch_file_info_created_at,"@d")
| croniter iterations=1 input=early_file_delivery_time start_epoch=early_start_epoch
| rename croniter_return as epoch_early_file
| croniter iterations=1 input=late_file_delivery_time start_epoch=late_start_epoch
| rename croniter_return as epoch_late_file
| croniter iterations=1 input=recurrence_pattern start_epoch=recurrence_pattern_epoch
| rename croniter_return as epoch_recurrence
| fields - early_file_delivery_time
| eval epoch_early_file=epoch_early_file+14400, epoch_late_file=epoch_late_file+14400, epoch_recurrence=epoch_recurrence+14400
| fieldformat epoch_early_file =strftime(epoch_early_file, "%Y-%m-%d %H:%M:%S.%3Q")
| fieldformat epoch_late_file =strftime(epoch_late_file, "%Y-%m-%d %H:%M:%S.%3Q")
| fieldformat epoch_recurrence =strftime(epoch_recurrence, "%Y-%m-%d %H:%M:%S.%3Q")
| eval date_reference=strftime(epoch_file_info_created_at, "%Y-%m-%d"), process_time=epoch_file_info_modified_at-epoch_file_info_created_at
| eval results=case(validation_error="FILE_TYPE_MISMATCH", "Failure", epoch_file_info_created_at>epoch_early_file AND epoch_file_info_created_at<epoch_late_file, "Success", epoch_file_info_created_at<epoch_early_file, "Warn", process_time>300 AND process_time<1800, "Success", 1=1, "Not Run")
| eval combined=interface_id."@".integration_name."@".priority."@".service_area."@".source_sys_name."@".target_sys_name."@"
| xyseries combined date_reference results
| rex field=combined "^(?<interface_id>[^\@]+)\@(?<integration_name>[^\@]+)\@(?<priority>[^\@]+)\@(?<service_area>[^\@]+)\@(?<source_sys_name>[^\@]+)\@(?<target_sys_name>[^\@]+)\@$"
| fillnull value="Not Run"
| eval alert_sent=if(isnull(alert_sent), "No", alert_sent)
| eval status=if(isnull(status), "Not Run", status)
| eval ticket_id=if(isnull(ticket_id), "No", ticket_id)
| table interface_id, integration_name, priority, service_area, source_sys_name, target_sys_name
[ makeresults | addinfo
| eval time = mvappend(relative_time(info_min_time,"@d"),relative_time(info_max_time,"@d"))
| fields time | mvexpand time
| makecontinuous time span=1d
| eval time=strftime(time,"%F")
| reverse
| stats list(time) as time
| return $time
]
| rename interface_id as "Service ID", integration_name as "Service Name", priority as "Priority", service_area as "Service Area", source_sys_name as "Source", target_sys_name as "Target"
... View more