Splunk Search

How to use the result from one search in another search?

sushmitha_mj
Communicator

I have a set of fields like Servername, type, Country, desc,_time. These fields have been indexed and I already have a dashboard in place displaying these fields.
I now have additional info, which is the location details in a CSV along with Server name. I have uploaded this and I can access this CSV using the command:

| inputlookup location_table_v2.csv 

to my existing dashboard which displays Servername, type, Country, desc,_time. I want to add the location details as well. The location detail CSV as well as the indexed data has a common field that is Server name. So I can match the fields in CSV and indexed data using the server name field.

I want my final output to display Servername, type, Country, desc,_time, location_name.
I am using a subsearch for this purpose, but I am not get all the fields in the output. Also, it displays all the rows in the CSV, while it should be restricted to the just the server names shown in my dashboard now.

| index="logs" | eval country_code=substr(server_name,3,3) | append [inputcsv location_table.csv | rename "Host names" as host_name , "Location Information" as Location_Information  | eval country_code= substr(host_name, 5,3) ] | stats values(Location_Information) as Location_Information by country_code

I have been trying to get the desired result for a while, but I just get one step closer.

0 Karma

somesoni2
Revered Legend

Since the location information is added as lookup table, you could use the lookup command to add those values to your existing dashboard search

Updated
Didn't realize there were some typos. Try this one.

index="logs" | eval country_code=substr(server_name,1,2) | table _time, server_name, country_code, desc, type | lookup location_table_v2.csv  "Host names" as server_name OUTPUT "Location Information" country_code  | rename  "Location Information" as Location_Information | eval country_code= substr(host_name, 5,3) | stats values(Location_Information) as Location_Information by country_code

sushmitha_mj
Communicator

Here is a slightly refined query

index="logs" | eval compare=substr(server_name,1,5) | append [ inputlookup location_table_v2.csv | eval compare=Country.number_code ] | stats first(Location_Information) as Branch_name by compare

This query works but it displays all the rows in the csv.

Here is my parent query /dashboard query

| index="logs" | eval country_code=substr(server_name,1,2) | table _time, server_name, country_code, desc, type

I want to add the location detail to this query.

0 Karma

somesoni2
Revered Legend

Add it in Stats.

0 Karma

sushmitha_mj
Communicator

I think the problem is there is no _time value in the csv(lookup) so, I am not able to match it. I get the location_info and country code. If I add the other fields it gives me multiple values . That is because, only if I group by time will it give me one entry per server. But if I group by time along with country code then there are no results
Isn't there a simple way of doing this?

For example in SQL if I have 2 tables I can simply use a where to match the common fields and extract different fields from each table. This is exactly the same but I am stuck with it for days...

0 Karma

somesoni2
Revered Legend

The looks command does exactly the same as what you described in your SQL example. It adds the fields from lookup table (csv), to the current result by matching the common column. This is case sensitive match, so check if the values are matching between your indexed data and lookup table csv.

Also., provide the actual column names in teh lookup table location_table_v2.csv.

0 Karma

sushmitha_mj
Communicator

Okay...... I checked the field match is perfect.
The location_table_v2.csv has Host_names,Country,Location_Information,number_code

From the indexed data, the fields I am using are _time,server_name,desc,type

If I get a substr of Host_names field in lookup and a substr of server_name field in the indexed data they match.

So, ideally, I just want to add the location_information in addition to the existing fields in each of the rows without affecting the existing output. And the field I would use to match then is the substr(Host_names) and substr(server_name)

The existing query is :
| index="logs" | table _time, server_name, country_code, desc, type

0 Karma

somesoni2
Revered Legend

It makes more sense now . We can't use lookup as the match is not exact, but a substring. Give this a try

index="logs" | eval country_code=substr(server_name,1,2) | table _time, server_name, country_code, desc, type | eval From=1
| append [inputlookup location_table_v2.csv | fields Location_Information, Host_names | eval country_code=substr(Host_names,3,5)  | table country_code Location_Information | eval From=2] | stats values(Location_Information) as Branch_name, values(type), values(desc), values(server_name) sum(From) as From by country_code | where NOT From=2 | fields - From
0 Karma

sushmitha_mj
Communicator

Okay..... the output has the following fields
country_code Branch_name values(type) values(desc) values(server_name)

And the type, desc and server_name fields have 2 values in one row.
The _time field is missing. That is probably why I am not getting one value per row.

0 Karma

sushmitha_mj
Communicator

Where should I add the rest of the fields I want to see?

0 Karma

sundareshr
Legend

Try using lookup like this

index="logs" | eval country_code=substr(server_name,3,3) | lookup location_table.csv  "Server name" | rename "Host names" as host_name , "Location Information" as Location_Information | eval country_code= substr(host_name, 5,3)  | table Location_Information  country_code <<REST OF THE FIELDS>>

sushmitha_mj
Communicator

This is my latest query for reference

index="logs" | eval country_code=substr(server_name,1,5) | append [inputlookup location_table_v2.csv |  fields Location_Information, Host_names | eval country_code=substr(Host_names,3,5) ]  | stats values(Location_Information) as Branch_name, values(type), values(desc), values(server_name) by country_code
0 Karma

sushmitha_mj
Communicator

I think the problem is there is no _time value in the csv(lookup) so, I am not able to match it. I get the location_info and country code. If I add the other fields it gives me multiple values . That is because, only if I group by time will it give me one entry per server. But if I group by time along with country code then there are no results
Isn't there a simple way of doing this?

For example in SQL if I have 2 tables I can simply use a where to match the common fields and extract different fields from each table. This is exactly the same but I am stuck with it for days...

0 Karma

sushmitha_mj
Communicator

Where should I add the rest of the fields I want to see?

0 Karma

sushmitha_mj
Communicator

Here is a slightly refined query

index="logs" | eval compare=substr(server_name,1,5) | append [ inputlookup location_table_v2.csv | eval compare=Country.number_code ] | stats first(Location_Information) as Branch_name by compare

This query works but it displays all the rows in the csv.

Here is my parent query /dashboard query

| index="logs" | eval country_code=substr(server_name,1,2) | table _time, server_name, country_code, desc, type

I want to add the location detail to this query.

0 Karma

sundareshr
Legend

Try the updated query

0 Karma

sushmitha_mj
Communicator

It returns all values in the indexed data but the location_information and country_code rows are empty.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!