Splunk Search

timechart not respecting exclude searches but stats is

mmqt
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

jawaharas
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

jawaharas
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.

mmqt
Path Finder

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

0 Karma

jawaharas
Motivator

Glad it helped you.

0 Karma

jawaharas
Motivator

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

0 Karma

mmqt
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...