topic Re: removing last two characters from results in Splunk Search

removing last two characters from results

mcbradford — Mon, 28 Sep 2020 09:56:00 GMT

My search looks like this:

index=webproxy | regex user=".+a" | top 100 user

results are j9999la

I want to list the real result, but also remove anything after the first 5 character and display this also.

The result j9999 can be used in a lookup to give me a person's name.

So what I really want to see is

user               user_name               real_name
j9999la            j9999                   John Doe

If I get the everything after the 5th character removed - I can handle the rest

UPDATE

This does exactly what I want it to do, but I thought I would be able to figure out the second part - not really...

So, the results of user_name relate to a person. If the results of the user_name were part of the original data and it was called user - I would have additional fields returned with information on the user, such as full name, location, phone, etc.

We query an employee database twice a day to populate a csv containing all the employees.

How can I pass my results of user_name to the csv to populate the employee data?

BTW - not sure how we do this, but I do not pass anything to the csv now - it is just part of my data. This was setup by professional services.

Re: removing last two characters from results

dwaddle — Fri, 30 Sep 2011 04:27:55 GMT

The easiest way is probably to use the substr function within eval.

index=webproxy | regex user=".+a" | top 100 user | eval user_name=substr(user,1,5) ...

UPDATE

Assuming your user lookup table is defined in transforms.conf as user_list. This should let you pull data from it into your current search data:

index=webproxy | regex user=".+a" | top 100 user | eval user_name=substr(user,1,5)
| lookup user_list

Your transforms.conf stanza for user_list might look as follows:

[user_list]
filename=userlist.csv

Some relevant documentation is available at http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup and http://docs.splunk.com/Documentation/Splunk/4.2.3/Knowledge/Addfieldsfromexternaldatasources

Re: removing last two characters from results

tgow — Fri, 30 Sep 2011 15:44:03 GMT

Have you taken a look at the following search commands:

outputlookup
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Outputlookup

outputcsv
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Outputcsv

Re: removing last two characters from results

dwaddle — Fri, 30 Sep 2011 18:13:00 GMT

mcbradford, I took your answer below and merged it into the original question as an update. The answers system lets you edit your original question/answer as necessary.