Splunk Search

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

Path Finder

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.

alt text

0 Karma
1 Solution

Super Champion

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.

View solution in original post

SplunkTrust
SplunkTrust

@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)"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Path Finder

Pardon me, heres my query.

sourcetype="filesystemchange"
| 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"

0 Karma

SplunkTrust
SplunkTrust

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Path Finder

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

0 Karma

Super Champion

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.

View solution in original post

Path Finder

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

0 Karma

Super Champion

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

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma