topic Re: Need splunk to compare data values with current date value in Splunk Search

How to compare data values with current date value?

sekhar463 — Wed, 17 May 2023 13:11:53 GMT

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

Re: Need splunk to compare data values with current date value

ITWhisperer — Wed, 17 May 2023 11:32:34 GMT

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?

Re: Need splunk to compare data values with current date value

sekhar463 — Wed, 17 May 2023 12:40:37 GMT

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

Re: Need splunk to compare data values with current date value

ITWhisperer — Wed, 17 May 2023 12:49:50 GMT

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

Re: Need splunk to compare data values with current date value

sekhar463 — Wed, 17 May 2023 13:30:53 GMT

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 .

Re: Need splunk to compare data values with current date value

ITWhisperer — Wed, 17 May 2023 13:31:40 GMT

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.

Re: Need splunk to compare data values with current date value

sekhar463 — Wed, 17 May 2023 13:46:46 GMT

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

Re: Need splunk to compare data values with current date value

ITWhisperer — Wed, 17 May 2023 14:14:36 GMT

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.

Re: Need splunk to compare data values with current date value

sekhar463 — Wed, 17 May 2023 14:20:02 GMT

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