Splunk Search

Expression for custom lookup table values

bcanfield83
Engager

Hi All,

This may be a bit of a peculiar question, but I'm trying to figure out if there's a way to use a certain expression in a search query to pull a "maximum" value based upon a custom table (.csv import) that is pulled into the query via the "lookup" command.

The table has 4 possible "Attribute" values which range from "level-1-access" to "level-4-access". In the stats table, a given UserID may have activity that reflect 1 or more of these (thus, a maximum of 4 per UserID).

Below is a sample dataset. What I'm attempting to do is filter this data so that it's only showing the "maximum" (or, "highest") value for each UserID. The rows bolded in green is what I'd want to see, with everything else excluded; thus, there should only be 1 row per distinct UserID.

One possible thought that comes to mind is adding an numeric field to the .csv lookup, though still not 100% certain how to go about rendering the stats table to only include the highest value per UserID. 

Any help would be appreciated. Thanks! 

UserIDAttribute
jdoelevel-1-access
jdoelevel-3-access
jdoelevel-4-access
asmithlevel-1-access
asmithlevel-2-access
ejoneslevel-3-access
ejoneslevel-4-access
pthomaslevel-1-access
pthomaslevel-2-access
pthomaslevel-3-access
pthomaslevel-4-access
Labels (3)
0 Karma
1 Solution

dtburrows3
Builder

I think doing something like this would work.

 

 

 

<base_search>
    | lookup <lookup_name> UserID OUTPUT Attribute
    | eval
        attribute_regex=".*\-(\d+)\-.*",
        max_attribute=case(
            isnull(Attribute), null(),
            mvcount(Attribute)==1, max(tonumber(replace(Attribute, attribute_regex, "\1"))),
            mvcount(Attribute)>1, max(mvmap(Attribute, tonumber(replace(Attribute, attribute_regex, "\1"))))
            ),
        max_attribute_full=mvdedup(
            case(
                isnull(Attribute), null(),
                mvcount(Attribute)==1, if(tonumber(replace(Attribute, attribute_regex, "\1"))=='max_attribute', 'Attribute', null()),
                mvcount(Attribute)>1, mvmap(Attribute, if(tonumber(replace(Attribute, attribute_regex, "\1"))=='max_attribute', 'Attribute', null()))
                )
            )

 

 

 


You can see in the screenshot below I used simulated data to do what I think you are asking for.

dtburrows3_0-1703717237716.png

 

 

The regex used in the replace command can be adjusted to fit the pattern that is stored in the Attribute field value to just grab the number.

View solution in original post

bcanfield83
Engager

Thank you very much!!! 

0 Karma

dtburrows3
Builder

I think doing something like this would work.

 

 

 

<base_search>
    | lookup <lookup_name> UserID OUTPUT Attribute
    | eval
        attribute_regex=".*\-(\d+)\-.*",
        max_attribute=case(
            isnull(Attribute), null(),
            mvcount(Attribute)==1, max(tonumber(replace(Attribute, attribute_regex, "\1"))),
            mvcount(Attribute)>1, max(mvmap(Attribute, tonumber(replace(Attribute, attribute_regex, "\1"))))
            ),
        max_attribute_full=mvdedup(
            case(
                isnull(Attribute), null(),
                mvcount(Attribute)==1, if(tonumber(replace(Attribute, attribute_regex, "\1"))=='max_attribute', 'Attribute', null()),
                mvcount(Attribute)>1, mvmap(Attribute, if(tonumber(replace(Attribute, attribute_regex, "\1"))=='max_attribute', 'Attribute', null()))
                )
            )

 

 

 


You can see in the screenshot below I used simulated data to do what I think you are asking for.

dtburrows3_0-1703717237716.png

 

 

The regex used in the replace command can be adjusted to fit the pattern that is stored in the Attribute field value to just grab the number.

Get Updates on the Splunk Community!

Discover Powerful New Features in Splunk Cloud Platform: Enhanced Analytics, ...

Hey Splunky people! We are excited to share the latest updates in Splunk Cloud Platform 9.3.2408. In this ...

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...