Splunk Search

How to create a table using different fields?

chandana204
Communicator

i have this kind of data:
event 1:

field_name=field_value
status="process"
status_file="file_name" 

event 2:

field_name=field_value
status="send"
status_file="file_name"

event 3:

field_name=field_value
transfer_status="transferred"
transfer_file="file_name"

event 4:

field_name=field_value
fatal_type1 = "reason1"
fatal_type1_file="file_name"

event 5:

field_name=field_value
fatal_type2 = "reason2"
fatal_type2_file="file_name" 

From all of the events above, common value is file_name rest of them are different. If the file_name matches with other file_types, it should list all I wanted make a report as below

file_name, file_types , file_types_count
abfskjsfjskjjv, process , 4
send

transferred
reason2

fhehrhashlahsf, process, 2
reason1

fhkawuruhshfhuaf, process , 3
send
transferred

0 Karma
1 Solution

sbbadri
Motivator

@chandana204

try this,

| makeresults
| eval event1="field_name=field_value status=\"process\" status_file=\"file_name\""
| eval event2="field_name=field_value status=\"send\" status_file=\"file_name\""
| eval event3="field_name=field_value transfer_status=\"transferred\" transfer_file=\"file_name\""
| eval event4="field_name=field_value fatal_type1=\"reason1\" fatal_type1_file=\"file_name\""
| eval event5="field_name=field_value fatal_type2=\"reason2\" fatal_type2_file=\"file_name\""
| fields - _time
| transpose
| fields - column
| rename "row 1" as events
| rex field=events "field_name=(?P<field_name>field_value)"
| rex field=events "(status|type\d+)=\"(?P<status>\S+)\""
| rex field=events "file=\"(?P<status_file>\S+)\""
| stats values(status) as file_types, count as file_types_count by status_file
| rename status_file as file_name

View solution in original post

0 Karma

sbbadri
Motivator

@chandana204

try this,

| makeresults
| eval event1="field_name=field_value status=\"process\" status_file=\"file_name\""
| eval event2="field_name=field_value status=\"send\" status_file=\"file_name\""
| eval event3="field_name=field_value transfer_status=\"transferred\" transfer_file=\"file_name\""
| eval event4="field_name=field_value fatal_type1=\"reason1\" fatal_type1_file=\"file_name\""
| eval event5="field_name=field_value fatal_type2=\"reason2\" fatal_type2_file=\"file_name\""
| fields - _time
| transpose
| fields - column
| rename "row 1" as events
| rex field=events "field_name=(?P<field_name>field_value)"
| rex field=events "(status|type\d+)=\"(?P<status>\S+)\""
| rex field=events "file=\"(?P<status_file>\S+)\""
| stats values(status) as file_types, count as file_types_count by status_file
| rename status_file as file_name

0 Karma

niketn
Legend

@chandana204, please try the following:

<YourBaseSearch>
| eval file_name=coalesce(coalesce(coalesce(status_file,transfer_file),fatal_type1_file),fatal_type2_file)
| eval file_types=coalesce(coalesce(coalesce(status,transfer_status),fatal_type1),fatal_type2)
| stats list(file_types) as file_types count(file_types) as file_types_count by file_name

Following is a run anywhere search example based on sample data

| makeresults
| eval field_name="field_value",status="process",status_file="abfskjsfjskjjv"
| append [| makeresults
| eval field_name="field_value",status="process",status_file="fhehrhashlahsf"]
| append [| makeresults
| eval field_name="field_value",status="process",status_file="fhkawuruhshfhuaf"]
| append [| makeresults
| eval field_name="field_value", status="send", status_file="abfskjsfjskjjv"]
| append [| makeresults
| eval field_name="field_value", status="send", status_file="fhkawuruhshfhuaf"]
| append [| makeresults
| eval field_name="field_value", transfer_status="transferred",transfer_file="abfskjsfjskjjv"]
| append [| makeresults
| eval field_name="field_value", transfer_status="transferred",transfer_file="fhkawuruhshfhuaf"]
| append [| makeresults
| eval field_name="field_value", fatal_type1 = "reason1", fatal_type1_file="fhehrhashlahsf"]
| append [| makeresults
| eval field_name="field_value", fatal_type2 = "reason2", fatal_type2_file="abfskjsfjskjjv"]
| eval file_name=coalesce(coalesce(coalesce(status_file,transfer_file),fatal_type1_file),fatal_type2_file)
| eval file_types=coalesce(coalesce(coalesce(status,transfer_status),fatal_type1),fatal_type2)
| stats list(file_types) as file_types count(file_types) as file_types_count by file_name
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...