How do I use the latest value given to replace a field that is NULL but both event have one common value?

[Kitteh]

As stated I want the latest value in "Hash Value" and "Type" column to be filled instead of being "NA" and "Unknown" which I hardcoded if NULL. I want the latest value to be carried over instead of being null if the "Location" column have the common value.

Referring to the screenshot, I want the file "what is this", its hash value and type from the latest which is below it to be carried up.

[niketn]

@Kitteh, what if the latest value of Hash Value or Type is NA or Unknown since it is NULL? Can you provide your current search?

You can use the map command to get the last() values for Hash Value and Type for your base search and then pass on the same to your actual search to perform fillnull with these selected values. However, without a peep at your existing search it will be tough to provide actual search:

<YourBaseSearch>
| stats last('Hash Value') as HashValue last(Type) as Type
| map search= "<YourBaseSearch>
           | table \"Hash Value\" Type
           | fillnull value=\"$HashValue$\" \"Hash Value\" 
           | fillnull value=\"$Type$\" Type "

Or something like following run anywhere example:

| makeresults
| eval 'Hash Value'="FFABCD1234",Type="File"
| stats last('Hash Value') as HashValue last(Type) as Type
| map search="| makeresults
| eval Hash Value=if(isnull('Hash Value'),\"$HashValue$\",'Hash Value')
| eval Type=if(isnull(Type),\"$Type$\",Type)"

[Kitteh]

Pardon me, heres my query.

sourcetype="file_system_change"
| where action="update" OR action="delete" OR action="add"
| rex field=_raw "^(?P.*)action="
| eval Timing=if(action="delete", Timestamp, modtime)
| replace delete WITH "Deletion" IN action
| replace add WITH "New File/Directory" IN action
| replace update WITH "Modification Detected" IN action
| eval Type = coalesce (case(isdir=1, "Directory", isdir=0, "File"), "Unknown")
| eval Hash=if(isnull(hash), "NA", hash)
| table Timing, path, Hash, host, action, Type
| rename Timing AS "Time of Modification" path AS "Location" Hash as "Hash Value" host AS "Machine" action AS "Action"

[niketn]

First off you should move the where query conditions to your base query for faster results as per Splunk query optimization guidelines.

sourcetype="file_system_change" 
| where action="update" OR action="delete" OR action="add"
| <YourRemainingSearch> 

Should be re-written as

sourcetype="file_system_change" action="update" OR action="delete" OR action="add"
| <YourRemainingSearch>

Please let me know if you would be interested in exploring map command as a solution for your use case since you already have another option working for you.

[Kitteh]

Yes I am still interested in exploring map command, do you mind replacing what my command is to what you are trying to type down? The amount of slash and money sign is confusing me

[cmerriman · Accepted solution]

try adding this to the end of your search:

|eventstats latest('Hash Value') as latesthash latest(Type) as latesttype by location|eval "Hash Value"=if('Hash Value'="NA",latesthash,'Hash Value')|eval Type=if(Type="NA",latesttype,Type)

if Hash Value doesn't work, try latest(Hash Value) as latesthash otherwise try renaming it without the space. Sometimes Splunk is touchy with spaces in field names.

[Kitteh]

I've changed eventstats to streamstats, fixed the issue. Thanks anyway ur query helped

[cmerriman]

oh good, i'm glad that simple fix [eventstats to streamstats] did the trick!

[Kitteh]

Doesnt seem to be working well but at least it filled up the unknown and NA ones

https://imgur.com/a/8PPqi

Hope the link i pasted can be seen

[Kitteh]

What is wrong now is, it uses the hash from the wrong file as its own. Also the directory. it doesnt tally the past file records