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"
Thanks for providing your search and sample data. Unfortunately, I can't run the query because the croniter command complains about invalid arguments, specifically "input" and "start_epoch". Which version of the SA-croniter app are you using?
@richgalloway - We are actually using v1.0.4 of this add-on: https://splunkbase.splunk.com/app/4027/
The problem stems from the subsearch that is part of the table command. It's the equivalent to
| table interface_id, integration_name, priority, service_area, source_sys_name, target_sys_name "fred"
which will print "fred" in the table header, but doesn't actually create a field called "fred". Without a field, the fillnull command can do nothing.
I'm not sure what to do about it, but thought I should mention this so you can think of other ways to reach the same goal.
@richgalloway - The subsearch which actually populates the date columns actually ended up working for another SPL query which leverages some of the same logic, but doesn't include any of the croniter/Python integration commands.
Here is the SPL query where that subsearch functioned perfectly. @Richfez and @xpac provided a lot of assistance on getting that one cleared up and the Splunk post pertaining to that is here - https://community.splunk.com/t5/Splunk-Search/How-to-populate-nonexistent-values-in-a-table/td-p/513...
index=hcnc_mulesoft_test_new sourcetype=api_events_legacy_new
| rename "API Name" as api_name, "Application Name" as application_name, "Status Code" as status_code
| eval service_id=case(Verb="GET" AND api_name="Provider Data Management API" AND application_name="PHP-AMHC", "PRO-I-001-0001", Verb="GET" AND api_name="Member Data API" AND application_name="Enrollment Broker", "MEM-I-001-0001", Verb="PUT" AND api_name="Member Data API" AND application_name="Enrollment Broker", "MEM-I-002-0001", 1=1, "Unqualified")
| where service_id!="Unqualified"
| eval Priority=case(Verb="GET" AND api_name="Provider Data Management API" AND application_name="PHP-AMHC", "2", Verb="GET" AND api_name="Member Data API" AND application_name="Enrollment Broker", "2", Verb="PUT" AND api_name="Member Data API" AND application_name="Enrollment Broker", "1", 1=1, "Unqualified")
| where Priority!="Unqualified"
| eval service_area=case(Verb="GET" AND api_name="Provider Data Management API" AND application_name="PHP-AMHC", "Provider", Verb="GET" AND api_name="Member Data API" AND application_name="Enrollment Broker", "Member", Verb="PUT" AND api_name="Member Data API" AND application_name="Enrollment Broker", "Member", 1=1, "Unqualified")
| where service_area!="Unqualified"
| eval combined=service_id."@".Verb."@".api_name."@".Priority."@".service_area."@".application_name."@"
| timechart span=1d count(eval(status_code)) as count by combined
| untable _time combined count
| eval date=strftime(_time, "%Y-%m-%d")
| xyseries combined date count
| rex field=combined "^(?<service_id>[^@]+)@(?<Verb>[^@]+)@(?<api_name>[^@]+)@(?<Priority>[^@]+)@(?<service_area>[^@]+)@(?<application_name>[^@]+)@$"
| fields - combined
| table service_id Verb api_name Priority service_area application_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 service_id as "Service ID", Verb as "Resource Name", api_name as "API Name", Priority as "Priority", service_area as "Service Area", application_name as "Consumer"
I think you're running into a scenario of "proving a negative". This article should be helpful.
@chirsf - Thanks for the link. We are able to use timechart on the _time field, as mentioned in the below post, to get a value of "0" to populate the missing values.
However, we were unable to use the same approach for this scenario because there are multiple timestamps being used, unless I'm thinking of this incorrectly.