Getting Data In

How to have a search run outputcsv even if the search returns no data and fill with NULL values?

pete_charlton
Explorer

I have a report that dumps to an outputcsv, during the weekends this report will not return any values due to the lack of user traffic to the environment. I want to have my search create a blank CSV with only the column headers and a NULL value.

My search example is below.

index=index1 source=log1 "string of text" "string of text" | append [search index=index2 source=log2 Field=value] | table _raw | addinfo | eval ResultsTime="ResultsTime="+strftime(info_search_time,"%FT%T%z") | eval ResultsRange_Begining="ResultsRange_Begining="+strftime(info_min_time,"%FT%T%z") | eval ResultsRange_End="ResultsRange="+strftime(info_max_time,"%FT%T%z") | eval ResultsID="ResultsID="+info_sid | fields - info_min_time, info_max_time, info_search_time, info_sid | outputcsv file.csv

1 Solution

chris
Motivator

Two suggestions that do not do exactly what you want but they might help:

  • There is a create_empty option to outputcsv that will just create an empty file (without the header & NULL values):

    index=index1 source=log1 "string of text" "string of text" | append [search index=index2 source=log2 Field=value] | table _raw | addinfo | eval ResultsTime="ResultsTime="+strftime(info_search_time,"%FT%T%z") | eval ResultsRange_Begining="ResultsRange_Begining="+strftime(info_min_time,"%FT%T%z") | eval ResultsRange_End="ResultsRange="+strftime(info_max_time,"%FT%T%z") | eval ResultsID="ResultsID="+info_sid | fields - info_min_time, info_max_time, info_search_time, info_sid | outputcsv create_empty=true file.csv

  • If you can afford to have a line with literal NULL in your csv even if your search returns other Results:

    index=index1 source=log1 "string of text" "string of text" | append [search index=index2 source=log2 Field=value] | table _raw | addinfo | eval ResultsTime="ResultsTime="+strftime(info_search_time,"%FT%T%z") | eval ResultsRange_Begining="ResultsRange_Begining="+strftime(info_min_time,"%FT%T%z") | eval ResultsRange_End="ResultsRange="+strftime(info_max_time,"%FT%T%z") | eval ResultsID="ResultsID="+info_sid |append [ stats count | foreach _raw fielda fieldb ResultsTime ResultsRange_Begining ResultsRange_End ResultsID [eval <>="NULL"]]| fields - info_min_time, info_max_time, info_search_time, info_sid, count | outputcsv create_empty=true file.csv

Just add whatever fields you need to the foreach command

Hope this helps

Chris

View solution in original post

chris
Motivator

Two suggestions that do not do exactly what you want but they might help:

  • There is a create_empty option to outputcsv that will just create an empty file (without the header & NULL values):

    index=index1 source=log1 "string of text" "string of text" | append [search index=index2 source=log2 Field=value] | table _raw | addinfo | eval ResultsTime="ResultsTime="+strftime(info_search_time,"%FT%T%z") | eval ResultsRange_Begining="ResultsRange_Begining="+strftime(info_min_time,"%FT%T%z") | eval ResultsRange_End="ResultsRange="+strftime(info_max_time,"%FT%T%z") | eval ResultsID="ResultsID="+info_sid | fields - info_min_time, info_max_time, info_search_time, info_sid | outputcsv create_empty=true file.csv

  • If you can afford to have a line with literal NULL in your csv even if your search returns other Results:

    index=index1 source=log1 "string of text" "string of text" | append [search index=index2 source=log2 Field=value] | table _raw | addinfo | eval ResultsTime="ResultsTime="+strftime(info_search_time,"%FT%T%z") | eval ResultsRange_Begining="ResultsRange_Begining="+strftime(info_min_time,"%FT%T%z") | eval ResultsRange_End="ResultsRange="+strftime(info_max_time,"%FT%T%z") | eval ResultsID="ResultsID="+info_sid |append [ stats count | foreach _raw fielda fieldb ResultsTime ResultsRange_Begining ResultsRange_End ResultsID [eval <>="NULL"]]| fields - info_min_time, info_max_time, info_search_time, info_sid, count | outputcsv create_empty=true file.csv

Just add whatever fields you need to the foreach command

Hope this helps

Chris

Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...