Splunk Search

timechart not respecting exclude searches but stats is

Path Finder

I have some Json data that looks like this

{  
   "target":[  
      {  
         "detailEntry":{  
            "signOnModeType":"dummy info"
         },
         "alternateId":"AppName1",
         "displayName":"dummy info",
         "id":"dummy info",
         "type":"AppInstance"
      },
      {  
         "detailEntry":null,
         "alternateId":"someemail@domain.com",
         "displayName":"dummy info for email",
         "id":"dummy info",
         "type":"AppUser"
      }
   ]}

I then have a search to grab the alternateId but I only want the 'AppName1' info and not the 'someemail@domain.com' since they both use "alternateId" if you just search target{}.alternateId both values are returned, but if you do spath and then use a regex and state to not match emails I get the results I want. Doing stats on like target{0}.alternateId (or any number) also returns zero results.

index=events (target{}.alternateId="*") | spath | rename target{}.alternateId as appId | stats count by appId | regex appId!="([a-z0-9][-a-z0-9_\+\.]*[a-zA-Z0-9])@([a-zA-Z0-9][-a-zA-Z0-9\.]*[a-zA-Z0-9]\.(ca|com|org|net)|([0-9]{1,3}\.{3}[0-9]{1,3}))" | sort -count

This above command runs as expected and only returns results for the AppName1. But if I use the same type of search and use a timechart rather than a stats or chart command it doesnt respect the regex appId!= and still displays all matches of target{}.alternateId including email addresses

ndex=events (target{}.alternateId="*") | spath | rename target{}.alternateId as appId | timechart count by appId usenull=f limit=5 useother=f | regex appId!="([a-z0-9][-a-z0-9_\+\.]*[a-zA-Z0-9])@([a-zA-Z0-9][-a-zA-Z0-9\.]*[a-zA-Z0-9]\.(ca|com|org|net)|([0-9]{1,3}\.{3}[0-9]{1,3}))"

Putting the regex appId!= before the timechart actually returns zero results

Am I doing something wrong?

0 Karma
1 Solution

Motivator

You can use mvexpand command for your use-case as shown below..

| makeresults 
| eval message="  {  
    \"target\":[  
       {  
          \"detailEntry\":{  
             \"signOnModeType\":\"dummy info\"
              },
          \"alternateId\":\"AppName1\",
          \"displayName\":\"dummy info\",
          \"id\":\"dummy info\",
          \"type\":\"AppInstance\"
           },
       {  
          \"detailEntry\":null,
          \"alternateId\":\"someemail@domain.com\",
          \"displayName\":\"dummy info for email\",
          \"id\":\"dummy info\",
          \"type\":\"AppUser\"
           }
    ]}" 
| spath input=message 
| eval _time=now()
| rename target{}.alternateId as appId
| mvexpand appId
| fields appId, _time
| regex appId!="([a-z0-9][-a-z0-9_\+\.]*[a-zA-Z0-9])@([a-zA-Z0-9][-a-zA-Z0-9\.]*[a-zA-Z0-9]\.(ca|com|org|net)|([0-9]{1,3}\.{3}[0-9]{1,3}))"
| timechart count by appId usenull=f limit=5 useother=f

As per your query, the RegEx was not working with timechart command as there are multi-values in the appId field. The mvexpand command expands the values of a multivalue field into separate events and the your can use the regex to filter events.

View solution in original post

Motivator

You can use mvexpand command for your use-case as shown below..

| makeresults 
| eval message="  {  
    \"target\":[  
       {  
          \"detailEntry\":{  
             \"signOnModeType\":\"dummy info\"
              },
          \"alternateId\":\"AppName1\",
          \"displayName\":\"dummy info\",
          \"id\":\"dummy info\",
          \"type\":\"AppInstance\"
           },
       {  
          \"detailEntry\":null,
          \"alternateId\":\"someemail@domain.com\",
          \"displayName\":\"dummy info for email\",
          \"id\":\"dummy info\",
          \"type\":\"AppUser\"
           }
    ]}" 
| spath input=message 
| eval _time=now()
| rename target{}.alternateId as appId
| mvexpand appId
| fields appId, _time
| regex appId!="([a-z0-9][-a-z0-9_\+\.]*[a-zA-Z0-9])@([a-zA-Z0-9][-a-zA-Z0-9\.]*[a-zA-Z0-9]\.(ca|com|org|net)|([0-9]{1,3}\.{3}[0-9]{1,3}))"
| timechart count by appId usenull=f limit=5 useother=f

As per your query, the RegEx was not working with timechart command as there are multi-values in the appId field. The mvexpand command expands the values of a multivalue field into separate events and the your can use the regex to filter events.

View solution in original post

Path Finder

Perfect, thank you. This has done exactly what I wanted.

0 Karma

Motivator

Glad it helped you.

0 Karma

Motivator

By using timechart command, you no longer has a field name appId. Obviously, your RegEx filter will fail.

0 Karma

Path Finder

Putting the regex before the timechart command returns zero results as well though which should be calculated before the timehchart. What would the appId field change to after being piped into timechart

0 Karma