SPlunk query

[sverdhan]

Hello team , 

Please help me modify this query such that it is able to loop through all the values of the csv file : 

 Although it is able to give the clients and sensitivity of the selected sourcetype but in the results in the fields- Sourcetype Domain and NewIndex it is only giving the values of the first sourcetype- A4Server

Like for example over here the selected sourcetype is A4server but in the sourcetype it is giving A4ServerBeta  as it is not looping through the entire csv but only the first value

| tstats count WHERE index=* sourcetype=A4Server by index 
| rex field=index max_match=0 "(?<clients>\w+)(?<sensitivity>_private|_public)"

| table index, clients, sensitivity
| join type=left client [
    | inputlookup appserverdomainmapping.csv 

    | table NewIndex, Domain, Sourcetype
]| eval NewIndex= NewIndex + sensitivity
| table clients, sensitivity, Domain, Sourcetype, NewIndex

[bowesmana]

As @gcusello , don't use join, that's the wrong way to do this, however, you are using the wrong field. Your rex statement is extracting the field called clients but your join is using client (singular).

Please use the lookup way to do this, not join.

[gcusello]

Hi @sverdhan ,

did you tried to use the lookup command (https://help.splunk.com/en/splunk-enterprise/search/spl-search-reference/9.4/search-commands/lookup) instead of inputlookup in your search?

the lookup command is like a left join.

| tstats count WHERE index=* sourcetype=A4Server by index 
| rex field=index max_match=0 "(?<clients>\w+)(?<sensitivity>_private|_public)"
| fields - count
| lookup appserverdomainmapping.csv client OUTPUT NewIndex, Domain, Sourcetype
| eval NewIndex= NewIndex.sensitivity
| table clients, sensitivity, Domain, Sourcetype, NewIndex

Ciao.

Giuseppe

[sverdhan]

Hello  Giuseppe,

Thanks much for your suggestion , bit the query is giving an error : Cannot find client in the source field client in the lookup table . Now, we cant add clients in th elookup table becaue that would complex things. CAn yiu please tell m eothe rways to do it maybe through join or something.

Much appreciated.

[Prewin27]

@sverdhan 

Try below with clients,

| tstats count WHERE index=* by index sourcetype
| rex field=index max_match=0 "(?<clients>\w+)(?<sensitivity>_private|_public)"
| lookup appserverdomainmapping.csv clients OUTPUT NewIndex, Domain, Sourcetype
| eval NewIndex=NewIndex.sensitivity
| table clients, sensitivity, Domain, Sourcetype, NewIndex

If you do not need to add clients, and to just display lookup fields you can use appendcols

| tstats count WHERE index=* by index sourcetype
| rex field=index max_match=0 "(?<clients>\w+)(?<sensitivity>_private|_public)"
| appendcols [| inputlookup appserverdomainmapping.csv | fields Domain, Sourcetype, NewIndex]
| eval NewIndex=NewIndex.sensitivity
| table clients, sensitivity, Domain, Sourcetype, NewIndex

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[sverdhan]

Hello, this query seems to be working but the clients field is a multivalue field for some sourcetype ,so it results are spread out ,can you modify it ?

[sverdhan]

A4server Beta is the first value so no matter what sourcetype i choose it is on;y giving the values of A4server Beta in sourcetype , newIndex an ddomain