Splunk Search

Populating non-existent values where multiple timestamp comparisons exist.

adnankhan5133
Communicator

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 IDService NamePriorityService AreaSourceTarget2020-08-152020-08-142020-08-13
100File12SA1Source1Target2Success Not Run
105File21SA2Source2Target1Warn Success

 

Here are some raw logs with dummy data as well:

2020-08-15T09:42:55.746+00002020-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+00002020-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+00002020-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+00002020-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+00002020-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+00002020-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+00002020-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+00002020-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+00002020-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+00002020-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"

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.
0 Karma

adnankhan5133
Communicator

@richgalloway - We are actually using v1.0.4 of this add-on: https://splunkbase.splunk.com/app/4027/

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

adnankhan5133
Communicator

@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"

 

0 Karma

chirsf
Explorer

I think you're running into a scenario of "proving a negative". This article should be helpful.

 

https://www.duanewaddle.com/proving-a-negative/

0 Karma

adnankhan5133
Communicator

@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.

https://community.splunk.com/t5/Splunk-Search/How-to-populate-nonexistent-values-in-a-table/td-p/513...

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. 

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...