How to create a table using different fields?

[chandana204]

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

[sbbadri · Accepted solution]

@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

[niketn]

@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