Knowledge Management

using lookup data to filter event

chookp
Explorer

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

above is my sample data , below is my maintenance datealt text

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.

Tags (1)
0 Karma

chookp
Explorer

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

0 Karma

chookp
Explorer

alt text

0 Karma

chookp
Explorer

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

alt text

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

alt text

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

0 Karma

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

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

chookp
Explorer

yes the PM_DATE is my lookup definition name

0 Karma

chookp
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

chookp
Explorer

i have the different date

0 Karma

to4kawa
Ultra Champion

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

0 Karma

harishalipaka
Motivator

hi @chookp

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

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

Thanks
Harish
0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...