I am unable to get additional columns from a CSV I have referenced in an SPL query that I have written. In the CSV there are numerous columns however I want to add additional columns based on my search. From a data view, my index has details about devices for example 3 fields (user, status, machinename). My query searches for "status = "in use"" and then tries to match this up to the CSV in question. The output I am after is that on a match to show the detail from the CSV as required (i.e. columns are labelled firstname, lastname, type)
My query is
index=devices status="in use"
| rename prim_user as userid
| search userid="*"
[|inputlookup mycatalog.csv | search type="valued" | fields userid]
| table userid type machinename
When I run this query a table is produced but the "type" column is not returned from the CSV. I tried editing my search to be
index=devices status="in use"
| rename prim_user as userid
| search userid="*"
[|inputlookup mycatalog.csv | search type="valued" | fields userid, type]
| table userid type machinename
This however returns a null value. I then tried to use a lookup but this fails too
index=devices status="in use"
| rename prim_user as userid
| search userid="*"
[|inputlookup mycatalog.csv | search type="valued" | fields userid]
| lookup mycatalog.csv prim_user as userid OUTPUT userid, type
| table userid type machinename
This returns an error in my lookup advising "could not find all of the specified lookup fields in the lookup table even though they are there.
Any help appreciated.
If I understood your issue here correctly, you want lookup the userid and get the type from the lookup. But I am not sure you are only looking for type="valued" in the subsearch.
This should work
index=devices status="in use"
| rename prim_user as userid
| lookup mycatalog.csv userid
| table userid type machinename
Hope this helps