Getting Data In

How to create a list of source files for use in primary search?

aoleske
Path Finder

My question focuses around using one search to create a list of files I don’t want to consider in the primary search. Here is an overview of the problem I am trying to solve. I have a directory that fills with small files, one file per transaction series for an application we run.
1) Each file is a source.
2) If the file contains “Transaction Rolled Back" OR "Process FNXML Complete”, I do not want to have the filename (source) in the final list of sources. I am looking for transactions that did not complete.
3) I would like the final output to be a list of sources (log files) that do not have the “Transaction Rolled Back" OR "Process FNXML Complete” messages within the last three file lines.

Data examples are:
Action 1/9/2018 3:08:33 PM: XML Processing is complete.
Action 1/9/2018 3:08:33 PM: *******Commit Transaction *******
Action 1/9/2018 3:08:33 PM: Not writing to if_header - No transaction was set in the XML.
Action 1/9/2018 3:08:33 PM: ProcessNode Complete
Action 1/9/2018 3:08:33 PM: Process FNXML Complete.

OR

Action 1/9/2018 3:05:37 PM: ******** Transaction Rolled Back **********
Action 1/9/2018 3:05:37 PM: ErrorDesc=The current status of the shop order [RUN] is not in the list of old shop order oper status values.

Action 1/9/2018 3:05:37 PM: Not writing to if_header - No transaction was set in the XML.

When I run the following command, I will get a list of sources that looks like something like this:

(host=vel*) index=velocity sourcetype="velocity:icim" source="E:\\logs\\icim\\*"  
| stats count by source
OUTPUT:
source                                                                                         count (This is linecount)
E:\logs\Intercim\10076010920181646645312.log    17
E:\logs\Intercim\10076010920181646746093.log    34
E:\logs\Intercim\10076010920181646750905.log    20

I can create the lookup table successfully, and create a separate search to compare the search against the lookup table, but I can’t seem to create a combined search. Part of the issue is that the second half, as I get a “Mismatched ‘]’ error. I think the issue is a subsearch within a subsearch, but am not sure how to get past it. Any help would be greatly appreciated.

Works to create lookup table:

(host=vel*) index=velocity sourcetype="velocity:icim" source="E:\\logs\\icim\\*" ("Transaction Rolled Back" OR "Process FNXML Complete") 
| dedup source 
| stats count by source 
| fields source 
| outputlookup icimlogs.csv
Works to search with lookup table:

(host=vel*) index=velocity sourcetype="velocity:icim" (source="E:\\logs\\icim\\*" NOT source="E:\\logs\\icim\\2018*") Action  NOT  
[| inputlookup icimlogs.csv] 
| dedup source 
| table source

Combined search that fails (This is just the latest iteration):

(host=vel*) index=velocity sourcetype="velocity:icim" source="E:\\logs\\icim\\*" ("Transaction Rolled Back" OR "Process FNXML Complete") 
| dedup source 
| stats count by source 
| fields source 
| outputlookup icimlogs.csv 
| append 
    [ search (host=vel*) index=velocity sourcetype="velocity:icim" (source="E:\\logs\\icim\\*" NOT source="E:\\logs\\icim\\2018*") Action NOT 
        [| inputlookup icimlogs.csv]] 
| dedup source 
| table source]

Thanks for your help, everyone.

0 Karma
1 Solution

nabeel652
Builder

What I have understood you just need one query in order to:
i- filter out any files like E:\logs\icim\2018* ===> NOT source="E:\logs\icim\2018*"
ii- filter out files (source) containing "Transaction Rolled Back" OR "Process FNXML Complete" ===> NOT [|search index=velocity sourcetype="velocity:icim" (host=vel*) ("Transaction Rolled Back" OR "Process FNXML Complete") | dedup source | fields source]

So your query would be like:

index=velocity sourcetype="velocity:icim" (host=vel*)  NOT  source="E:\\logs\\icim\\2018*" NOT [|search  index=velocity sourcetype="velocity:icim" (host=vel*) ("Transaction Rolled Back" OR "Process FNXML Complete") | dedup source | fields source]  | your query continues...

View solution in original post

0 Karma

aoleske
Path Finder

Thanks Nabeel652. I have accepted the answer. I used a variation of what you suggested and then discovered that I get a log file with an Oracle disconnect message which listed the pertinent log file directly. I have lost the original search to the mists of time, or I would post it here. Thanks again for your help!
Andrew

0 Karma

nabeel652
Builder

What I have understood you just need one query in order to:
i- filter out any files like E:\logs\icim\2018* ===> NOT source="E:\logs\icim\2018*"
ii- filter out files (source) containing "Transaction Rolled Back" OR "Process FNXML Complete" ===> NOT [|search index=velocity sourcetype="velocity:icim" (host=vel*) ("Transaction Rolled Back" OR "Process FNXML Complete") | dedup source | fields source]

So your query would be like:

index=velocity sourcetype="velocity:icim" (host=vel*)  NOT  source="E:\\logs\\icim\\2018*" NOT [|search  index=velocity sourcetype="velocity:icim" (host=vel*) ("Transaction Rolled Back" OR "Process FNXML Complete") | dedup source | fields source]  | your query continues...
0 Karma

aoleske
Path Finder

Hi Nabeel652! Thank you for the response. I am not sure where I would put that, as I have two queries with index-velocity. I am attempting to really do the following. (Assumption is that all log files are under E:\logs\icom):
1) Ignore any log files with "Transaction Rolled Back" OR "Process FNXML Complete" inside the contents.
2) Ignore any files that begin with "E:\logs\icim\2018*".

This leaves me with a small list of files where a transaction did not complete or rollback, and any possible files listing an error of some type. I can then explore any logs that have some form of error and figure out what transaction actually created the problem. Note that a pertinent file may not have the word "error" in it, so I can't just search for "error".

0 Karma

nabeel652
Builder

Ah sorry I got you wrong. Please see my updated answer 🙂

0 Karma

aoleske
Path Finder

The extra bracket ']' after [|inputlookup icimlogs.csv] was a typo. I have removed it, and the error is now gone. However, I am still only outputting my original file list, and not the filtered list.

0 Karma
Get Updates on the Splunk Community!

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...