topic using lookup data to filter event in Knowledge Management

using lookup data to filter event

chookp — Thu, 09 Apr 2020 03:19:34 GMT

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.

Re: using lookup data to filter event

gcusello — Wed, 30 Sep 2020 04:58:50 GMT

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

Re: using lookup data to filter event

to4kawa — Thu, 09 Apr 2020 08:34:00 GMT

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

Re: using lookup data to filter event

chookp — Wed, 30 Sep 2020 04:56:59 GMT

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

Re: using lookup data to filter event

gcusello — Tue, 14 Apr 2020 07:26:32 GMT

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

Re: using lookup data to filter event

chookp — Tue, 14 Apr 2020 07:34:46 GMT

yes the PM_DATE is my lookup definition name

Re: using lookup data to filter event

chookp — Tue, 14 Apr 2020 07:36:36 GMT

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

Re: using lookup data to filter event

gcusello — Tue, 14 Apr 2020 07:41:58 GMT

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

Re: using lookup data to filter event

chookp — Tue, 14 Apr 2020 07:51:03 GMT

i have the different date

Re: using lookup data to filter event

chookp — Tue, 14 Apr 2020 07:57:51 GMT

Re: using lookup data to filter event

chookp — Tue, 14 Apr 2020 08:06:34 GMT

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??

Re: using lookup data to filter event

to4kawa — Tue, 14 Apr 2020 08:31:10 GMT

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

Re: using lookup data to filter event

harishalipaka — Tue, 14 Apr 2020 11:01:22 GMT

hi @chookp

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

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