How to compare data values with current date value?

[sekhar463]

Hi I have some data events with Date value 

How to create splunk search if value of MAX_POSITION_DATE  for TABLE2 SHOULD BE  (TODAY -1) 

and table 1 has TODAY value and table 3 has TODAY .

i want to display any events if not met above condition

    TABLES NAMES MAX_POSITION_DATE 

TABLE1

2023-05-17 00:00:00.000

TABLE2

2023-05-16 00:00:00.000

TABLE3

2023-05-17 00:00:00.000

[sekhar463]

this data we have ingested in splunk as events

splunk search to get if value of MAX_POSITION_DATE  for TABLE2 SHOULD BE  (TODAY -1) 

and table 1 has TODAY value and table 3 has TODAY .

i have used search  but it was giving all events event condition not met for the data which i have given in the table

| search (TABLE1 AND MAX_POSITION_DATE!=@d-1) OR (TABLE2 AND MAX_POSITION_DATE!=@d) | stats count

TABLE_NAME            MAX_POSITION_DATE
TABLE1               2023-05-17 00:00:00.000
 TABLE2              2023-05-16 00:00:00.000
 TABLE3               2023-05-17 00:00:00.000

[ITWhisperer]

| transpose 0 header_field=TABLE_NAME
| where strptime(TABLE1,"%F") == relative_time(now(),"@d") AND strptime(TABLE2,"%F") == relative_time(now(),"-1d@d") AND strptime(TABLE3,"%F") == relative_time(now(),"@d")

[sekhar463]

is transpose command required ?

and also i wanto compare by using MAX_POSITION_DATE  filled in the TABLE FIELDS( which has 3 tables ) 

 if value of MAX_POSITION_DATE  for TABLE2 SHOULD BE  (TODAY -1) 

and table 1 has TODAY value and table 3 has TODAY .

[ITWhisperer]

Given the results of the stats command that you shared, then yes, it is required as it puts all the values in one event so that they can be compared to each other.

Don't forget that Splunk effectively processes events one at a time in the pipeline, so in order to do the comparison, the values need to be in the same event.

[sekhar463]

not getting results 

HERE ARE THE SAMPLE EVENTS 

5/17/23
5:20:01.653 PM
2023-05-17 05:50:01.653, TABLE_NAME="TABLE1", MAX_POSITION_DATE="2023-05-17 00:00:00.000", MAX_DMA_RUN_DATETIME="2023-05-17 11:03:40.479"
host = 127.0.0.1:8088source = ANG sourcetype = ANG Data layer
5/17/23
5:20:01.653 PM
2023-05-17 05:50:01.653, TABLE_NAME="TABLE2", MAX_POSITION_DATE="2023-05-16 00:00:00.000", MAX_DMA_RUN_DATETIME="2023-05-16 23:11:01.388"
host = 127.0.0.1:8088source = ANG sourcetype = ANG Data layer
5/17/23
5:20:01.653 PM
2023-05-17 05:50:01.653, TABLE_NAME="TABLE3", MAX_POSITION_DATE="2023-05-17 00:00:00.000", MAX_DMA_RUN_DATETIME="2023-05-17 11:03:42.879"
host = 127.0.0.1:8088source = ANG sourcetype = ANG Data layer

[ITWhisperer]

Here is a runanywhere example showing you it working with the data you provided. I have assume that the first part of each event is the _time field and stripped it from the _raw data

| makeresults
| fields - _time
| eval _raw="2023-05-17 05:50:01.653, TABLE_NAME=\"TABLE1\", MAX_POSITION_DATE=\"2023-05-17 00:00:00.000\", MAX_DMA_RUN_DATETIME=\"2023-05-17 11:03:40.479\"
2023-05-17 05:50:01.653, TABLE_NAME=\"TABLE2\", MAX_POSITION_DATE=\"2023-05-16 00:00:00.000\", MAX_DMA_RUN_DATETIME=\"2023-05-16 23:11:01.388\"
2023-05-17 05:50:01.653, TABLE_NAME=\"TABLE3\", MAX_POSITION_DATE=\"2023-05-17 00:00:00.000\", MAX_DMA_RUN_DATETIME=\"2023-05-17 11:03:42.879\""
| multikv noheader=t
| table _raw
| eval _time=strptime(_raw,"%F %T.%3N")
| rex "TABLE_NAME=\"(?<TABLE_NAME>[^\"]+).+MAX_POSITION_DATE=\"(?<MAX_POSITION_DATE>[^\"]+)"
| table TABLE_NAME MAX_POSITION_DATE
``` the lines above generate sample data in line with your example ```
| transpose 0 header_field=TABLE_NAME
| where strptime(TABLE1,"%F") == relative_time(now(),"@d") AND strptime(TABLE2,"%F") == relative_time(now(),"-1d@d") AND strptime(TABLE3,"%F") == relative_time(now(),"@d")

If it is still not working for you, it is because you haven't provided an accurate enough representation of your events.

[sekhar463]

this is the data in which already fields are there for TABLE NAME and MAX_POSITION_DATE

5/17/23
5:20:01.653 PM
2023-05-17 05:50:01.653, TABLE_NAME="TABLE1", MAX_POSITION_DATE="2023-05-17 00:00:00.000", MAX_DMA_RUN_DATETIME="2023-05-17 11:03:40.479"
host = 127.0.0.1:8088source = ANG sourcetype = ANG Data layer
5/17/23
5:20:01.653 PM
2023-05-17 05:50:01.653, TABLE_NAME="TABLE2", MAX_POSITION_DATE="2023-05-16 00:00:00.000", MAX_DMA_RUN_DATETIME="2023-05-16 23:11:01.388"
host = 127.0.0.1:8088source = ANG sourcetype = ANG Data layer
5/17/23
5:20:01.653 PM
2023-05-17 05:50:01.653, TABLE_NAME="TABLE3", MAX_POSITION_DATE="2023-05-17 00:00:00.000", MAX_DMA_RUN_DATETIME="2023-05-17 11:03:42.879"
host = 127.0.0.1:8088source = ANG sourcetype = ANG Data layer

[ITWhisperer]

SPL is not SQL - what are tables in this context?

Please can you share some anonymised events (in a code block </>) to prevent formatting corruptions?