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, an upvote 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, an upvote would be appreciated.

View solution in original post

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, an upvote 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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!