using lookup data to filter event

[chookp]

in my look up table there is my maintenance date which i would like to remove it in my event is there a way that i can remove the event without deleting it from the index?

above is my sample data , below is my maintenance date

is there a way that i can filter out my event from my lookup data so that those date which appear in my lookup table will not be seen in my event.

hope you all can understand what i mean. thanks.

[chookp]

i add a station_name to check, there is my BCL-JLB but there is no date status is it because of this it for filtered out? as the system deem it as All time??

[chookp]

[chookp]

hi thanks for the help i am able to do it but it seem there is some error below is my command:
DESCRIPTION="sump pump" OR (DESCRIPTION="ejector pump" AND DESCRIPTION="run/stop")
| eval TIMEONLY =strptime(CREATEDATETIME ,"%d/%m/%Y %I:%M:%S %p")
| eval _time=TIMEONLY
| rex field=ASSET_NAME "^(?[^/]+)"
| rex field=VALUE mode=sed "s/TRIP/STOP/g"
| lookup PM_DATE STATION_NAME OUTPUT DATE
| where strftime(_time,"%d/%m/%Y") != DATE
| streamstats count(eval(VALUE="STOP")) AS TransactionID BY ASSET_NAME
| stats range(_time) AS duration list(VALUE) AS VALUES min(_time) AS _time BY TransactionID ASSET_NAME
| chart sum(duration) AS TotalActiveTime BY ASSET_NAME

when i take out the command "| where strftime(_time,"%d/%m/%Y") != DATE"
i got these info:

the problem i face is that in my lookup file there is no BCL-JLB, but during the search of my command the infomation is being filtered out, is there any idea how i can solve this issue?thanks

[to4kawa]

index=yours
| lookup  maintenance.csv STATION_NAME as LOCATION OUTPUT DATE
| where strftime(_time,"%Y/%m/%d) != DATE

[gcusello]

Hi @chookp,
let me understand: you want to update your lookup based on the events in your index?
If this is your need you could create a search that display the fields in your lookup and then update it using the outputlookup command, the question is: have you in your events all the fields of your lookup?
in your screenshot I see date, Station_Name (as a part of Asset_name field), but I don't see Staion_id.
If you have all the fields from your events, you could run something like this:

index=your_index
| eval DATE=strptime(DATE."%d/%m/%Y %H:%M:%S %p"), STATION_NAME=substr(ASSET_NAME,1,3)
| stats values(STATION_NAME) AS STATION_NAME max(DATE) AS DATE BY STATION_ID
| eval DATE=strftime(DATE."%d/%m/%Y)
| table STATION_ID STATION_NAME DATE
| outputlookup maintenance.csv

If instead you don't have (as it seems) the STATION_ID, you should create a lookup containing the relationship between STATION_ID and STATION_NAME (called e.g. stations.csv), and use it for the creation of the table:

 index=your_index
| eval DATE=strptime(DATE."%d/%m/%Y %H:%M:%S %p"), STATION_NAME=substr(ASSET_NAME,1,3)
| stats max(DATE) AS DATE BY STATION_NAME
| lookup stations.csv STATION_NAME OUTPUT STATION_ID
| eval DATE=strftime(DATE."%d/%m/%Y)
| table STATION_ID STATION_NAME DATE
| outputlookup maintenance.csv

Ciao.
Giuseppe

[gcusello]

Hi @chookp,
don't use strftime in the first part of eval, but after !=

| where DATE!=strftime(_time,"%d/%m/%Y")

then are you sure that the fields used as key for the lookup are correct (PM_DATE)?

Ciao.
Giuseppe

[chookp]

yes the PM_DATE is my lookup definition name

[chookp]

i got the same result using | where DATE!=strftime(_time,"%d/%m/%Y"), the data is filtered away.

[gcusello]

Hi @chookp,
debug your search in this way

DESCRIPTION="sump pump" OR (DESCRIPTION="ejector pump" AND DESCRIPTION="run/stop")
| eval TIMEONLY =strptime(CREATEDATETIME ,"%d/%m/%Y %I:%M:%S %p")
| eval _time=TIMEONLY
| rex field=ASSET_NAME "^(?[^/]+)"
| rex field=VALUE mode=sed "s/TRIP/STOP/g"
| lookup PM_DATE STATION_NAME OUTPUT DATE
| eval my_DATE=strftime(_time,"%d/%m/%Y")
| table DATE my_date

Ciao.
Giuseppe

[chookp]

i have the different date

[to4kawa]

use | fillnull DATE before where
and use | sort 0 _time before streamstats

[harishalipaka]

hi @chookp

use this | where NOT match(DATE,strftime(_time,"%d/%m/%Y"))

istead of | where DATE!=strftime(_time,"%d/%m/%Y")