Splunk Search

Why does my outputlookup search to truncate a lookup file work from the search bar, but not as a scheduled search??

New Member

Greetings

I record hourly traffic information of a web app in a lookup file (say myTraffic.csv) from which I update a chart every 10 minutes through out the day. I have also scheduled a search that truncates the same file every day around midnight so that it only keeps the daily traffic info. This is the scheduled search that truncates the file: | outputlookup "myTraffic.csv" and it is scheduled to tun at 00:10:00 every day.

When I run the above search on the 'Search' bar, it truncates the file all right. Unfortunately, when I used it in the scheduled search, it does not truncate the file at all. This is the message I got when I 'inspect' the 'run':

INFO: No results. Created empty file 'myTraffic.csv'

Why does the same search not produce the same result when scheduled is a mystery for me. Any suggestion, pointer, etc would be greatly appreciated.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Try this. I added a where clause which will force the query to keep events from today only. So when the search is run at 1:00 AM, it will remove everything from yesterday. This way you don't have to clean it up at all.

index=apache_secure http_method=GET earliest=-1h@h latest=now 
| bucket _time span=1h | stats count as Traffic by _time 
| eval _time=strftime(_time, "%H %M:%S") | inputlookup append=true "myTraffic.csv" 
| dedup _time | where _time>relative_time(now(),"@d") | outputlookup "myTraffic.csv" 

Update
Didn't realize that _time has been formatted to show only hours, so above will not work. Try something like this (this assume that first search that runs for the day is at 1:00 AM, adjust the append subsearch where clause if that's not the case)

index=apache_secure http_method=GET earliest=-1h@h latest=now 
| bucket _time span=1h | stats count as Traffic by _time 
| eval _time=strftime(_time, "%H %M:%S") | append [| inputlookup append=true "myTraffic.csv" | where NOT strftime(now(),"%H")="1"]
| dedup _time | outputlookup "myTraffic.csv" 

Now, the where clause on the append-subsearch will not append the content of myTraffic.csv if the current hour (time when the search is run) is 1. and will append it for rest of the hours/execution.

View solution in original post

SplunkTrust
SplunkTrust

Try this. I added a where clause which will force the query to keep events from today only. So when the search is run at 1:00 AM, it will remove everything from yesterday. This way you don't have to clean it up at all.

index=apache_secure http_method=GET earliest=-1h@h latest=now 
| bucket _time span=1h | stats count as Traffic by _time 
| eval _time=strftime(_time, "%H %M:%S") | inputlookup append=true "myTraffic.csv" 
| dedup _time | where _time>relative_time(now(),"@d") | outputlookup "myTraffic.csv" 

Update
Didn't realize that _time has been formatted to show only hours, so above will not work. Try something like this (this assume that first search that runs for the day is at 1:00 AM, adjust the append subsearch where clause if that's not the case)

index=apache_secure http_method=GET earliest=-1h@h latest=now 
| bucket _time span=1h | stats count as Traffic by _time 
| eval _time=strftime(_time, "%H %M:%S") | append [| inputlookup append=true "myTraffic.csv" | where NOT strftime(now(),"%H")="1"]
| dedup _time | outputlookup "myTraffic.csv" 

Now, the where clause on the append-subsearch will not append the content of myTraffic.csv if the current hour (time when the search is run) is 1. and will append it for rest of the hours/execution.

View solution in original post

New Member

I like the idea behind the above query. Thank you Somesoni2 for that. The code, however, does not work for me. I don't exactly know the reason but I have a question: when does the 'where' clause becomes false?

0 Karma

New Member

The code works for me now. I appreciate your help. Thanks so much Somesoni2.

Cheers.

0 Karma

New Member

The below query computes the past one hour traffic count and updates the lookup file "myTraffic.csv". This goes on until mid-night at which time the file is supposedly truncated using the scheduled search query (" |outputlookup "myTraffic.csv") but not working as it should:

index=apachesecure httpmethod=GET earliest=-1h@h latest=now | bucket time span=1h | stats count as Traffic by _time | eval _time=strftime(time, "%H %M:%S") | inputlookup append=true "myTraffic.csv" | dedup _time | outputlookup "myTraffic.csv"

0 Karma

SplunkTrust
SplunkTrust

What is your search that updates the hourly traffic information? You might be able to tweak the query and remove yesterday's records, that way you won't need your truncate query.

0 Karma

Champion

which app are you running the scheduled search from and is that the same app where the lookup is being written? Sounds like it might be running from a different app and just creating an empty lookup there.

0 Karma