Splunk Search

How to retrieve the fieldNames, based on the pattern present in the values?

jackson1990
Path Finder

I have a list of Incoming indexed Events. The value of some fields will come with Datatype prefixed, followed by a Colon(<DATATYPE>:).
I want to retrieve the field names whose value comes with the pattern(INT:).
Also, i need to retrieve the value after the pattern(<Datatype>:)
My Sample EventList:

ID=1 | Name=String:sankar | Age=INT:20 | Dept=String:Computer science | Programming=60 | DataStructure=INT:70 | RDBMS=80 | Compiler=75
ID=2 | Name=String:Akash | Age=INT:25 | Dept=String:Mechanical | SolidMechanics=80 | ThermoFluidMechanics=INT:65 | Manufacturing=90 
ID=3 | Name=String:Sathish | Age=INT:18 | Dept=String:Computer science | Programming=70 | DataStructure=INT:84 | RDBMS=90 | Compiler=85 
ID=4 | Name=String:Arun | Age=INT:24 | Dept=String:Mechanical | SolidMechanics=70 | ThermoFluidMechanics=55 | Manufacturing=INT:80 
ID=5 | Name=String:Kavya | Age=INT:30 | Dept=String:Electrical | PhysicalOptics=80 | InformationSecurity=75 | Nanophotonics=INT:90 | ImageProcessing=85 | NonlinearSystemTheory=85
ID=6 | Name=String:Kaveri | Age=INT:24 | Dept=String:Computer science | Programming=70 | DataStructure=INT:50 | RDBMS=INT:50 | Compiler=INT:85 

As per the above scenario, If my earlier search query is index="college_index" ID=6
Case 1:

For retrieving the field names, whose value comes with the pattern(INT:)

Output:
FieldNames:
Age,DataStructure,RDBMS,Compiler
Values:
24,50,50,85

Case 2:

For retrieving the field names, whose value comes with the pattern(String:)

Output:
FieldNames:
Name,Dept
Values:
Kaveri,Computer science

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Try something like this:

index=college_index ID=6 | rex "(?P<field>\w+)=(?P<pattern>INT|String):(?P<value>[^\|]*) \|" | where pattern="INT" | table field value

index=college_index ID=6 | rex "(?P<field>\w+)=(?P<pattern>INT|String):(?P<value>[^\|]*) \|" | where pattern="String" | table field value
---
If this reply helps you, Karma would be appreciated.

View solution in original post

woodcock
Esteemed Legend

I have not tried this so it may have a bug/typo but the basic idea should work:

 index="college_index" ID=6 | rex mode=sed "s/| \([^=]+=INT\)/| INT\1/g" | kv pairdelim="|" kvdelim="=" | fields INT* | rename INT* AS *

This uses sed to reformat the raw event such that the to-become-fieldnames whose values start with the pattern (in this case, "INT") will become prefixed with the same "INT" string. Then it forces an additional field extraction which will create new fields, which are the same as old fields except that some now start with the prefix "INT", which we can then use to filter and rename back.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try something like this:

index=college_index ID=6 | rex "(?P<field>\w+)=(?P<pattern>INT|String):(?P<value>[^\|]*) \|" | where pattern="INT" | table field value

index=college_index ID=6 | rex "(?P<field>\w+)=(?P<pattern>INT|String):(?P<value>[^\|]*) \|" | where pattern="String" | table field value
---
If this reply helps you, Karma would be appreciated.

jackson1990
Path Finder

Hi @richgalloway,
Thanks for your prompt reply. It works for all cases except the below case
*If my event is having a date field,prefixed by String/Int
Ex:

Name=String:J1939 packet detection test | Result=0 | TestFrequency=Int:3600 | TimeOfLastRun=5/19/2015 11:49:28 PM | TimeOfLastSuccessfulRun=String:1/1/0001 12:00:00 AM |

For Pattern"String",
It retrieves the field Name as Name only.
But expectedFields are Name and TimeOfLastSuccessfulRun.
Hope i am making my point clear.
Also it will be more helpful,if u can explain the rex pattern in the query,since i am a complete newbie to splunk.
Thanks in advance.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Changing the rex command to rex max_match=0 "(?P\w+)=(?PINT|String):(?P[^\|]*) \|" will find multiple matches and create multi-valued fields. You will have to use mv commands to process the fields. See Example 3 in the mvexpand command docs.

Regular expressions are not unique to Splunk and are well documented throughout the Internet. I recommend regex101.com - paste the string in that site for a good explanation of what it does. This regex string looks for a run of word characters followed by a '=' and puts them into a field called "field"; then expects the word "INT" or "String" followed by a ':' and puts it into a field called "pattern"; then puts everything up to the next '|' into a field called "value".

---
If this reply helps you, Karma would be appreciated.
0 Karma

RicoSuave
Builder

The following regex should work for you:

String\:(?<String>\w+)\s\|\sAge\=INT\:(?<age>\w+)
0 Karma

woodcock
Esteemed Legend

I do not see any way that this will work and even if it did, it would (probably, depending on how it is "fixed") only give events that have some field with a matching pattern but does not give a way to list which fields do.

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