Splunk Search

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

aseid
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

somesoni2
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

somesoni2
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.

aseid
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

aseid
New Member

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

Cheers.

0 Karma

aseid
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=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 | outputlookup "myTraffic.csv"

0 Karma

somesoni2
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

maciep
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...