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.
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
index=yours
| lookup maintenance.csv STATION_NAME as LOCATION OUTPUT DATE
| where strftime(_time,"%Y/%m/%d) != DATE
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
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
yes the PM_DATE is my lookup definition name
i got the same result using | where DATE!=strftime(_time,"%d/%m/%Y"), the data is filtered away.
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
i have the different date
use | fillnull DATE
before where
and use | sort 0 _time
before streamstats
hi @chookp
use this | where NOT match(DATE,strftime(_time,"%d/%m/%Y"))
istead of | where DATE!=strftime(_time,"%d/%m/%Y")