topic Re: How to convert current graph numbers into actual names using a lookup? in Splunk Search

How to convert current graph numbers into actual names using a lookup?

splunkman341 — Tue, 11 Aug 2015 14:53:49 GMT

Hi guys,

So I currently have a search which has "the five most active OOID's by folder activity". The OOID (Organization ID) is just a number that refers to an actual client name. What I am trying to do is integrate a lookup file that I have named "OOID_File" which is a csv file with two columns. The first column is called "name" and that has the actual OOID numbers, and the second column called "clientName" is the actual client name respectively.

The search I am trying to do all of this with is here:

index=doccloud_main sourcetype=doccloud_catalina "OOID Folder workspace" |  rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)" | chart count by OOID action  | addtotals | sort 5 -Total

I think I have all the necessary information, but should you have any further questions or need clarification please just ask!

Thanks for taking the time to read!

Re: How to convert current graph numbers into actual names using a lookup?

lguinn2 — Tue, 11 Aug 2015 15:04:08 GMT

Here is a tutorial on field lookups. It walks through how to set up your CSV file as a lookup table. If you load your CSV file following these directions, here is your search with a manual lookup:

index=doccloud_main sourcetype=doccloud_catalina "OOID Folder workspace" 
|  rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)" 
| lookup client_lookup name as OOID
| chart count by clientName action  | addtotals | sort 5 -Total

Note that I named the lookup client_lookup
If you set an automatic lookup, then your search would look like this:

index=doccloud_main sourcetype=doccloud_catalina "OOID Folder workspace" 
|  rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)" 
| chart count by clientName action  | addtotals | sort 5 -Total

Re: How to convert current graph numbers into actual names using a lookup?

splunkman341 — Tue, 11 Aug 2015 15:30:06 GMT

Thanks for you're quick response! I added the lookup table and lookup definition as you mentoned. I even deleted my old table and renamed it to be the same as yours to "client_lookup" and tried executing the manual search and it would not work.

Tried:

 index=doccloud_main sourcetype=doccloud_catalina "OOID Folder workspace" 
 |  rex "(?<action>created|updated|deleted\/moved) (?:.*) OOID:(?<OOID>[^,]+)" 
 | lookup client_lookup name as OOID
 | chart count by clientName action  | addtotals | sort 5 -Total

and now it is displaying 0 events. I even tried just doing "lookup client_lookup" and it will not display anything. 0.0

HELP!!!

Re: How to convert current graph numbers into actual names using a lookup?

lguinn2 — Tue, 11 Aug 2015 15:55:24 GMT

I assumed that the columns in your table were

name,clientName
100585,"ABC client"
211468,"XYZ client"
etc

It would actually be clearer if the CSV headings were

OOID,clientName

then the lookup command would be

lookup client_lookup OOID

Re: How to convert current graph numbers into actual names using a lookup?

splunkman341 — Tue, 11 Aug 2015 17:31:48 GMT

Yes the fields in my data are as follows:

 OOID                                      clientName
  G3XQ74RR1N1894WK         Barney's Frost Cake

I also changed the headings to what you have suggested, OOID and clientName respectively. Deleted old csv from lookups uploaded new one with new headers and tried executing the same search and did not get any luck with it.

Any suggestions on what to do next?

Re: How to convert current graph numbers into actual names using a lookup?

lguinn2 — Tue, 11 Aug 2015 18:38:43 GMT

Yes, there is a problem here! Your CSV file must actually have commas!