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!

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...