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.
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
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.
Add it in Stats.
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...
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.
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
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
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.
Where should I add the rest of the fields I want to see?
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>>
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
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...
Where should I add the rest of the fields I want to see?
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.
Try the updated query
It returns all values in the indexed data but the location_information and country_code rows are empty.