Can I add REST search results to an in-line search that contains multi-value field? If so, how do I parse it?

[khaleihla]

This is a piece of a search that I have been working on:

eventtype=knoob (file_name=authorize.conf)  
| eval zip1 = mvzip (key, value, ";")  | mvexpand zip1|  makemv zip1 delim=";" | eval skey=mvindex(zip1,1) | eval svalue=mvindex(zip1,0)  
| dedup skey, svalue | sort skey, svalue |  table skey, svalue, stanza, SplunkRole, file_name  
| where ( like (svalue, "import%") AND like (stanza, "role%")  ) 
| rename skey AS SplunkRole, stanza AS Role
| eval Role=case(isnull(Role),"NONE",NOT isnull(Role),Role)
| eval file_name=case(isnull(file_name),"authorize.conf", NOT isnull(file_name), "authorize.conf")
| eval SplunkRole=case(isnull(SplunkRole),"NONE",NOT isnull(SplunkRole),SplunkRole)
| table Group, Role, Index, SplunkRole   
| appendcols [ search eventtype=knoob (file_name=authorize.conf)  
| eval   zip1 = mvzip (key, value, ";") | mvexpand zip1   |  makemv zip1 delim=";" | eval skey=mvindex(zip1,1) | eval svalue=mvindex(zip1,0)  
| dedup skey, svalue | sort skey, svalue | table skey, svalue, stanza, file_name 
| where like (stanza, "APP%") 
| rename stanza as Group, skey as Index 
| eval Group=case(isnull(Group),"NONE",NOT isnull(Group),Group) 
| eval file_name=case(isnull(file_name),"authorize.conf", NOT isnull(file_name), "authorize.conf")
| eval Index=case(isnull(Index),"NONE",NOT isnull(Index),Index) 
| table Group, Role, Index, SplunkRole ]

... there are 4 more appendcols searches attached to this search to address "authenticate.conf" information.

I’m currently successfully extracting the following information from the authenticate.conf and authorize.conf tables (into a report) by: Group, Role, Index, SplunkRole

What is missing is the associated user information: I can obtain this information using:

|rest /services/authentication/users splunk_server=local 
| fields title, roles, realname
| rename title  as Username
| rename realname as Name roles AS Roles | sort Roles Username | table Roles Name Username

From this search, you will see data like:

Roles       Name             Username
admin       Jones, Barnaby   bjones

admin       Smith, Carol     csmith
eso-ro
eso-rw

I would like to be able to flatten the “Roles” column;

For example, if I have a column with multiple roles separated by “;”, I can use the split command:
Say that I have a field called “key” that contains the values: role1; role2; role3; role4

I know that I can separate it out:

| eval temp=split(key,";") | eval srole1=mvindex(temp,0) | eval srole2=mvindex(temp,1) | eval srole3=mvindex(temp,2) | eval srole4=mvindex(temp,3) 

How do I recognize the method to split this data: Is there a way to covert the “newline” or “carriage return linefeed” that appears to be occurring within the “roles” column when multi-values exist to a delimiter? So that I could continue to perform the in-line search process to append the results to my existing search?

I know that I can export the above REST query to an EXCEL spreadsheet (.CSV); however I cannot get the results flattened to be able to join with the results of my existing search. I wanted to be able to avoid having to manually create my lookup table, when multiple occurrences exist.

Is there really no way to deal with the “nulls”, “new lines” or “carriage return line feeds” within the Search context; when using a “rest | data…” retrieval?

[DalJeanis · Accepted solution]

I have a bunch of notes below, but the most important thing is the question - what do you want to achieve with your report? What format do you want the stuff to come out in?

That end result is how you determine what steps to take to collect it all up.

Ideally, you get it all down to the most granular level possible, and then build up your report from the details. So, what does the rest of the data look like, and do you have a way of knowing which user Role is the one that generated a line on the other report?

If not, then you might be better off leaving the multivalue field in place.

Okay, here's my notes.

---

To flatten the Roles, if you really want to, you use the mvexpand command. (It's already in use several places inyour code.) That's much simpler and more useful for "flattening" multivalue fields into multiple single-value records.

If you try to hard-code spreading them out into different field names, then you are creating a maintenance nightmare. In the SQL world, they would be "an un-normalized table", but that doesn't have the venom in the splunk world that it does in SQL. Let's just say that you are "committing spreadsheet". Don't do that. Use the same mvexpand methods you are using for the other stuff.

---

The sort command defaults to 100 records. If you want to retain all possible records, use sort 0.

---

The coalesce command assigns the first non-null value it finds, from left to right., so this line and all the ones like it -

 | eval Role=case(isnull(Role),"NONE",NOT isnull(Role),Role)

can be rewritten and simplified as

 | eval Role=coalesce(Role,"NONE")

Also, you are setting all filenames to "authorize.conf" with one of those case statements. If you want to do that, just do that.

 | eval filename="authorize.conf"

[khaleihla]

Thank you for your response.
1. My user would like to look at the data in several ways
a. LDAP Group name and All Users
b. User Name, LDAP role and all associated Splunk Roles
c. LDAP Role, with each Splunk role in a different column
2. I am able to manipulate the data to produce the results for Splunk roles in both ways:

a. Comma separated
b. Column separated
3. I am able to consolidate the LDAP and Splunk role information
4. Yesterday, Tuesday 1/24/17 I was able to derive the REST information
As to the file_name – I used that to show where the records were coming from during testing when combining the results from both the authorize and authentication.conf files. I’ll remove that one during clean-up.
Thank you for your information on the “coalesce command”.
The hiccup I was experiencing was how to flatten the multi-value field. I was trying to figure out how to get it to recognize the . However, when I decided to see if I could count the occurrences, I stumbled across a solution using MVEXPAND and STATS.
REST query rewritten using “MVEXPAND” and the “STATS” command.
| rest /services/authentication/users splunk_server=local| fields title, roles, realname | mvexpand roles | stats values(roles) AS roles by realname
This flattened the roles to be on one-line.

What happened is that the roles changed from being multi-line to appearing as a single value, but appeared to be delimited by a “space”. I changed the query then and asked it to split the roles column into several fields via the “split” command and usage of the mvindex command.
| rest /services/authentication/users splunk_server=local| fields title, roles, realname | mvexpand roles | stats values(roles) AS roles by realname
| mvcombine delim="," roles
| eval temp=split(roles," ")
| eval srole1=mvindex(temp,0) | eval srole2=mvindex(temp,1)
| eval srole3=mvindex(temp,2) | eval srole4=mvindex(temp,3)
| table title, roles, realname, temp, srole1, srole2, srole3, srole4
Now the roles field is separated out like:

My next problem, was that I needed to be able to have a 1:1 relationship of user per role on a line. To accomplish this, I needed to use the STATS command with the REST query.

| rest /services/authentication/users splunk_server=local
| fields title, roles, realname
| stats count by roles, realname

From the REST query, I now have lines that provide: one SplunkRole, one user-name.

When I started this project it was two-fold:
a. Obtain the authentication.conf and authorize.conf information.
b. Obtain the LDAP Active directory information

Part A – was a multi-query effort to condense down the information into:
• LDAP Group, LDAP Role, SplunkRole

• A table which can be used as a LOOKUP table.

Part B – obtain and associate USER to LDAP role is accomplished via the REST Query.

[DalJeanis]

Nice, I appreciate the detail on how you got your answer. A lot of people forget to close the loop for future generations.