Splunk Search

Lookup led to repplication of results, why & how to avoid it?

mohitab
Path Finder

I had a query being called from my webApp which was getting XML results nicely.

Query:

search index="timedata" | 
search (icao_aircraft_type_actual="*") | 
eval actual_air_time=ceiling((strptime(actual_runway_arrival,"%Y-%m-%d %H:%M:%S")-strptime(actual_runway_departure,"%Y-%m-%d %H:%M:%S"))/3600 )|  
chart limit=19 count by actual_air_time icao_aircraft_type_actual | 
rename actual_air_time AS State

To avoid repetitive calculation of 'actual_air_time' I did a pre-query to generate a lookup table as:

index="timedata"| 
search (icao_aircraft_type_actual="*") | 
eval actual_air_time=ceiling((strptime(actual_runway_arrival,"%Y-%m-%d %H:%M:%S")-strptime(actual_runway_departure,"%Y-%m-%d %H:%M:%S"))/3600 ) | 
table id departure_airport_icao_code arrival_airport_icao_code actual_air_time delay_departure delay_arrival | 
outputlookup mytable.csv

So I changed the Query accordingly as:

search index="timedata" | search (icao_aircraft_type_actual="a388") | lookup mytable.csv id | chart limit=19 count by actual_air_time icao_aircraft_type_actual | rename actual_air_time AS State

The queries are fired from angularJS based app through https://localhost:8089/servicesNS/admin/search/search/jobs/export. Now, the problem is that when I see response object from lookup based query, I see two results tag. First one is same as what I get from non-lookup based query, but second tag has debug information:

<messages>
  <msg type="DEBUG">Configuration initialization took 17ms for /opt/splunk/etc</msg>
  <msg type="DEBUG">base lispy: [ AND index::timedata ]</msg>
  <msg type="DEBUG">search context: user="admin", app="search", bs-pathname="/opt/splunk/etc"</msg>
  <msg type="INFO">Assuming implicit lookup table with filename 'mytable.csv'.</msg>
</messages>

First, My code broke due to badly structured response string while parsing it for XML. Secondly, unnecessarily double size data is getting transfered. Can anyone help me understand why I am getting duplicate results, and any way to avoid it?

mohitab
Path Finder

Any ideas on this?

0 Karma

mohitab
Path Finder

Update:

  • I managed to get rid of message "Assuming implicit lookup table with filename 'mytable.csv'" by making an entry in transforms.conf.
  • To make clear what I get in results after using lookup (notice there are two results tags):

    <?xml version='1.0' encoding='UTF-8'?>



    State
    A388




    7


    1




    11


    1




    State
    A388



    Configuration initialization took 18ms for /opt/splunk/etc
    base lispy: [ AND index::timedata ]
    search context: user="admin", app="search", bs-pathname="/opt/splunk/etc"

    <result offset='0'>
        <field k='State'>
            <value><text>7</text></value>
        </field>
        <field k='A388'>
            <value><text>1</text></value>
        </field>
    </result>
    <result offset='1'>
        <field k='State'>
            <value><text>9</text></value>
        </field>
        <field k='A388'>
            <value><text>1</text></value>
        </field>
    </result>
    <result offset='2'>
        <field k='State'>
            <value><text>11</text></value>
        </field>
        <field k='A388'>
            <value><text>2</text></value>
        </field>
    </result>
    

  • You may also notice that the output is not exactly same.

I am a bit puzzled, why two previews are generated, and why results are not same in the two previews.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...