Hi team,
I have below kind of data in splunk, it contains 3 fields ISRF, DSRF and DSFF. they are all multi-value fields.
2021-10-13 19:26:46,813 ISRF="[fullName,managerFullName,title,userName,division,department,location]" DSRF="[fullName,managerFullName,title,userName,division,department,location]" DSFF="[managerFullName,division,department,location,jobCodereasonForLeaving]"
2021-10-12 19:32:31,504 ISRF="[fullName,managerFullName,userName,division,department,location]" C_DSRF="[fullName,managerFullName,title,userName,division,department,location]" DSFF="[managerFullName,division,department,location,custom05,jobCode,riskOfLoss,impactOfLoss,reasonForLeaving]"
......
......
I expect the report like below format:
fields | count Of ISRF | count Of DSRF | count of DSFF |
fullName | 2 | 2 | 0 |
managerFullName | 2 | 2 | 2 |
title | 1 | 2 | 0 |
...... | |||
...... | |||
resonForLeaving | 0 | 0 | 1 |
I am trying below queries, and I am blocked how to continue for getting expected format table.
<baseQuery>
|eval includeSearchResultField=replace(replace(C_ISRF,"\[",""),"\]",""),
defaultSearchResultField=replace(replace(C_DSRF,"\[",""),"\]",""),
filterFields=replace(replace(C_DSFF,"\[",""),"\]","")
|makemv delim="," includeSearchResultField
|makemv delim="," defaultSearchResultField
|makemv delim="," filterFields
Here is one way to do it - the part before the blank lines just sets up the dummy data
| makeresults
| eval _raw="2021-10-13 19:26:46,813 ISRF=\"[fullName,managerFullName,title,userName,division,department,location]\" DSRF=\"[fullName,managerFullName,title,userName,division,department,location]\" DSFF=\"[managerFullName,division,department,location,jobCodereasonForLeaving]\"
2021-10-12 19:32:31,504 ISRF=\"[fullName,managerFullName,userName,division,department,location]\" DSRF=\"[fullName,managerFullName,title,userName,division,department,location]\" DSFF=\"[managerFullName,division,department,location,custom05,jobCode,riskOfLoss,impactOfLoss,reasonForLeaving]\""
| multikv noheader=t
| table _raw
| extract pairdelim=" ", kvdelim="="
| foreach *
[| eval <<FIELD>>=split(trim(<<FIELD>>,"[]"),",")]
| fields - _raw
| eval row=1
| untable row field values
| rex max_match=0 field=values "(?<values>\S+)\s*"
| mvexpand values
| stats count by field values
| xyseries values field count
| fillnull value=0
@ITWhisperer Thank you for the solution.
Here is one way to do it - the part before the blank lines just sets up the dummy data
| makeresults
| eval _raw="2021-10-13 19:26:46,813 ISRF=\"[fullName,managerFullName,title,userName,division,department,location]\" DSRF=\"[fullName,managerFullName,title,userName,division,department,location]\" DSFF=\"[managerFullName,division,department,location,jobCodereasonForLeaving]\"
2021-10-12 19:32:31,504 ISRF=\"[fullName,managerFullName,userName,division,department,location]\" DSRF=\"[fullName,managerFullName,title,userName,division,department,location]\" DSFF=\"[managerFullName,division,department,location,custom05,jobCode,riskOfLoss,impactOfLoss,reasonForLeaving]\""
| multikv noheader=t
| table _raw
| extract pairdelim=" ", kvdelim="="
| foreach *
[| eval <<FIELD>>=split(trim(<<FIELD>>,"[]"),",")]
| fields - _raw
| eval row=1
| untable row field values
| rex max_match=0 field=values "(?<values>\S+)\s*"
| mvexpand values
| stats count by field values
| xyseries values field count
| fillnull value=0