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!

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