Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- About rbechtold
rbechtold
Communicator
Member since:
01-05-2018
05-18-2022
Community Statistics
| Posts | 48 |
| Solutions | 15 |
| Karma Given | 27 |
| Karma Received | 10 |
| Member Since | 01-05-2018 |
Activity Feed
- Karma Re: How to throttle an alert using more than one field? for DalJeanis. 04-27-2022 01:15 PM
- Karma Re: Azure Firewall Log - Field Extraction Help for jacobpevans. 06-05-2020 12:50 AM
- Karma Re: Extracting values for table for reinharn. 06-05-2020 12:50 AM
- Karma Not a question, just a THANK YOU! for lbrhyne. 06-05-2020 12:50 AM
- Karma Re: How to split field into multiple fields for comparison for owie6466. 06-05-2020 12:50 AM
- Karma Re: Why is the information column turning red at search time? for jkat54. 06-05-2020 12:50 AM
- Karma Re: Chart Add fields sum of columns and columns larger then 1 for shouldntdothat. 06-05-2020 12:50 AM
- Karma What is wrong with this "map" command search? for arjunpkishore5. 06-05-2020 12:50 AM
- Karma Re: Hide a populated panel based on if text field is private or public IP address for niketn. 06-05-2020 12:50 AM
- Karma Re: [Regex/Extraction] Need help finding the correct method of parsing a specific log type for woodcock. 06-05-2020 12:50 AM
- Karma Re: How to count the number of different values (multivalue) in a field? for russell120. 06-05-2020 12:50 AM
- Karma Re: How to count the number of different values (multivalue) in a field? for russell120. 06-05-2020 12:50 AM
- Karma Re: How to count the number of different values (multivalue) in a field? for jaime_ramirez. 06-05-2020 12:50 AM
- Karma Re: Azure Firewall Log - Field Extraction Help for jacobpevans. 06-05-2020 12:50 AM
- Karma Re: Azure Firewall Log - Field Extraction Help for jordanmedved. 06-05-2020 12:50 AM
- Karma Re: Use makemv on all fields for brinley. 06-05-2020 12:50 AM
- Karma Re: What end of anchor parameter to use for the Symantec event? for jwalzerpitt. 06-05-2020 12:50 AM
- Karma Re: How to create an alert for multiple alerts (fires when too many of specific alerts are activated) for spluzer. 06-05-2020 12:50 AM
- Karma Re: start a search certain number of hours/days/weeks from time picker set value for mpasha. 06-05-2020 12:50 AM
- Karma Re: Find average for multiple hosts for aking76. 06-05-2020 12:50 AM
Topics I've Started
| Subject | Karma | Author | Latest Post |
|---|---|---|---|
| 0 | |||
| 0 | |||
| 0 | |||
| 0 | |||
| 0 |
08-15-2019
11:14 AM
Perfect! I'm glad to hear everything is working as expected.
In order to convert the string "x minutes ago" into an integer we can work with, we can use a combination of rex to extract the data we need from the Time_Field, and eval to create a new "Converted_Minutes" field based on those values.
We'll then use another eval statement to create a "Status" field that decides if the process is up or down based on the output of the Converted_Minutes field.
Go ahead and change your base search to this:
| search index=os sourcetype=chef:csv host="vxkip-v87k6btx" AND source=/home/hab/node_status.csv
| eval n=split(_raw,",")
| eval Time_Field=mvindex(n, 0)
| eval Name=mvindex(n, 1)
| eval OS=mvindex(n, 2)
| search OS = "windows"
| search OS != "ubuntu"
| where len(Name) > 2
| eval Name=lower(Name)
| eval Name = trim(replace(Name,".lm.lmig.com.",""))
| replace ".lm.lmig.com" WITH "" IN Name
| eval Name = trim(replace(Name,".kc.lmig.com",""))
| eval Name = trim(replace(Name,".lmx.lmig.com",""))
| eval Name = trim(replace(Name,".lmxt.lmig.com",""))
| eval Name = trim(replace(Name,".lmig.com",""))
| eval Name = trim(replace(Name,".lm",""))
| eval Name = trim(replace(Name,".dsm.pin.safeco.com",""))
| rex field=Time_Field "(?<digit>\d+)\s(?<time_unit>second|minute|hour|day)s?"
| eval Converted_Minutes = if(time_unit = "second", round(digit / 60,0), if(time_unit = "minute", digit, if(time_unit = "hour", digit * 60, if(time_unit = "day", digit * 1440, "Time Length not Accounted For"))))
| eval Status = if(Converted_Minutes > 4, "Process_Down", "Process_Up")
| table Time_Field Converted_Minutes Status Name OS
| sort Name
| rename Name as host
The Converted_Minutes field will translate any time from the Time_Field (Seconds, Minutes, Hours, Days) into minutes. You can use this field for visualizations and comparisons since it will always output as an integer.
You should be able to add your join after you confirm the "Converted_Minutes" field and the "Status" field are showing up properly!
... View more
08-14-2019
04:13 PM
Excellent feedback! I appreciate you working with me to figure this out. I think we've arrived at the root of the problem.
I mistakenly assumed the data we were trying to extract was in the joined dataset, but it appears that it's actually coming from the main search!
The "n" fields is created in the first eval pipe, where the _raw is split on commas. This "n" field has all of the data we're looking for -- just in multi-value format.
In order to index these multi-value fields so we can work with them, we use the mvindex . You can see in your base search you have two of these values indexed already -- Name and OS. In order to add the time field to the results, we'll need to mvindex that field as well.
First, try this for me:
| search index=os sourcetype=chef:csv host="vxkip-v87k6btx" AND source=/home/hab/node_status.csv
| eval n=split(_raw,",")
| eval Time_Field=mvindex(n, 0)
| eval Name=mvindex(n, 1)
| eval OS=mvindex(n, 2)
| search OS = "windows"
| search OS != "ubuntu"
| where len(Name) > 2
| eval Name=lower(Name)
| eval Name = trim(replace(Name,".lm.lmig.com.",""))
| replace ".lm.lmig.com" WITH "" IN Name
| eval Name = trim(replace(Name,".kc.lmig.com",""))
| eval Name = trim(replace(Name,".lmx.lmig.com",""))
| eval Name = trim(replace(Name,".lmxt.lmig.com",""))
| eval Name = trim(replace(Name,".lmig.com",""))
| eval Name = trim(replace(Name,".lm",""))
| eval Name = trim(replace(Name,".dsm.pin.safeco.com",""))
| table Time_Field Name OS
| sort Name
| rename Name as host
Once you've confirmed that your three fields are there, go ahead and add the join statement, and everything should show up as expected.
As a bonus in the case that you're interested, you could use the rex command to accomplish the same thing (in place of the split/mvindex method) like this:
| search index=os sourcetype=chef:csv host="vxkip-v87k6btx" AND source=/home/hab/node_status.csv
| rex field=_raw "(?<Time_Field>\d+\s(?:seconds?|minutes?|hours?|days?)\sago)\,\s(?<Name>[^\,]+)\,\s(?<OS>[^\n]+)\."
| search OS = "windows"
| search OS != "ubuntu"
| where len(Name) > 2
| eval Name=lower(Name)
| eval Name = trim(replace(Name,".lm.lmig.com.",""))
| replace ".lm.lmig.com" WITH "" IN Name
| eval Name = trim(replace(Name,".kc.lmig.com",""))
| eval Name = trim(replace(Name,".lmx.lmig.com",""))
| eval Name = trim(replace(Name,".lmxt.lmig.com",""))
| eval Name = trim(replace(Name,".lmig.com",""))
| eval Name = trim(replace(Name,".lm",""))
| eval Name = trim(replace(Name,".dsm.pin.safeco.com",""))
| table Time_Field Name OS
| sort Name
| rename Name as host
You also have much more flexibility with regular expressions, but both ways should work for this scenario.
I apologize for misunderstanding the question, I hope this is the solution that ends up working for you!
... View more
08-14-2019
11:49 AM
Ahh, that makes more sense!
We're losing the _raw field with this line:
...
|stats values(*) AS * by host
...
Just as a heads up, I've slightly modified the regex to make it more specific to your dataset.
I have two possible solutions for you --
The first is to put the extraction before the stats command to make sure we have the _raw field available for extracting like this:
...BASE SEARCH...
|join type=left host
[search index=wineventlog* sourcetype=WinEventLog:Application SourceName=Chef
| rex field=_raw "(?<Field1>\d+\s(?:seconds?|minutes?|hours?|days?)\sago)\,\s(?<Field2>[^\,]+)\,\s(?<Field3>[^\n]+)\."
| stats values(*) as * by host
...
The second solution would be to check to see what fields we're actually working with after we run the stats command.
I would need you to run this for me:
index=wineventlog* sourcetype=WinEventLog:Application SourceName=Chef
| stats values() as by host
and then tell me what the field name is that contains the logs that look like this:
1 minute ago, vmrit-c4ca0001.lm.lmig.com, windows 6.3.9600.
It's possible we should be extracting from a field other than _raw .
Let me know how it goes!
... View more
08-13-2019
01:53 PM
That is strange!
I'm trying to think of why this wouldn't work. Would you mind sharing your base search with me? I'm wondering if we got rid of the _raw field somewhere in the search.
Here is a search showing how the extraction should have worked on the data you provided (copy and paste this into Splunk):
|makeresults count=1
| eval _raw = "1 minute ago, vmrit-c4ca0001.lm.lmig.com, windows 6.3.9600."
| rex field=_raw "(?<Field1>[^\,]+)\,(?<Field2>[^\,]+)\,(?<Field3>.*)\."
... View more
08-13-2019
01:05 PM
Hey Owie,
This can easily be accomplished using regular expression to extract the fields from your data (using the rex command)!
Try adding this to your search:
...Base Search...
| rex field=_raw "(?<Field1>[^\,]+)\,(?<Field2>[^\,]+)\,(?<Field3>.*)\."
This should work based on the example data you provided, however if you run into any trouble or it doesn't work let me know and I can help you write a more precise extraction.
Rex command documentation: https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Rex
Also, if you're interested in learning regex, I recommend checking out this site: https://regexone.com/
I hope this helps!
... View more
08-05-2019
02:50 PM
Hey Joshua,
I've gone a little back and forth on this one in terms of how to approach it. From what I can gather, your requirements are as follows:
You want to see the latest 30 minutes on a line chart
You want to see the average for the last 48 hours on the same line chart
You want to be able to change the "CustomName" field to define which CustomName field value you're looking for
The solution I've come up with relies primarily on the timewrap and the foreach commands. If I'm understanding the problem correctly, this should satisfy all three of the requirements:
index=source earliest=-48h@h
| bin _time span=5m
| search CustomName="Place123"
| chart avg(RawStatus_Avg) AS perHour OVER _time by CustomName
| timewrap 30minutes
| fillnull value=0
| eval counter=0, priorAvg=0
| foreach *before
[ eval priorAvg=if(isnotnull(<<FIELD>>), <<FIELD>>+priorAvg, priorAvg), counter=counter+1]
| eval priorAvg=priorAvg/counter
| rename *_latest_30minutes AS *_Current
| table _time *_Current priorAvg
The priorAvg field displays your average over the last 48 hours, while the *_Current field shows current values in the last 30 minutes. I've used 5 minute buckets instead of 15 to give you a few more chart plot points, but that can be changed if you require.
I'm also unclear on how the ratio field plays into the question, but you can re-add it onto the chart if you'd like by adding this:
...BASE SEARCH...
| foreach *_Current
[ eval <<MATCHSTR>>_Ratio = <<FIELD>> / priorAvg]
| table *_Ratio
If I'm misunderstanding the question, or if anything isn't working as expected, please let me know. I am also here to assist if anything doesn't make sense.
Best of luck!
... View more
08-02-2019
12:27 PM
Hey Shouldntdothat,
This was a tricky one!
To be completely honest I'm not sure how this will work with larger datasets, and I'm basing the whole search on what I think your original dataset might look like. That said, it is possible, and I want to show you one way of how it is possible!
Copy and paste this into any Splunk instance and you can dissect it to see how the search is working. I've left comments as kind of "checkpoints" to show you what each portion of the search is doing and how it is changing the data.
| gentimes start=7/23/2019 end=1 increment=1d
| rename starttime AS _time
| table _time
| eval values = (random()%19+1).",".(random()%19+1).",".(random()%19+1), Host = "Host1,Host2,Host3"
| eval values = split(values,","), Host = split(Host,",")
| eval newfield = mvzip(Host, values)
| mvexpand newfield
| fields _time newfield
| rex field=newfield "(?<Host>.*?)\,(?<Value>.*)"
| table _time Host Value
| eval randomizer = random()%100
| where randomizer > 55
| fields - randomizer
`comment("Above is just creating a dataset that mimics what I believe your data may look like")`
| timechart sum(Value) AS value_sum by Host
| fillnull value=0
`comment("Using timechart instead of chart so that all days are accounted for, including days with no data.")`
| sort _time
| table _time *
| transpose 0 header_field=_time
| where column != "_span" AND column != "_spandays"
| addtotals
`comment("Formatting data in the same chart format that you have in your spreadsheet and getting a total count for all values by host (See the "Total" field).
Note: The numbers you see as the field headers are epoch timestamps. They translate to July 23, July 24, etc. We will tranlate these numbers to human readable numbers later in the search.")`
| rename * as temp_*
`comment("The foreach command won't work if the first character of a field is a digit, renaming all fields with a "temp_" prefix")`
| foreach *
[| eval <<FIELD>>_unique = if("<<FIELD>>" = "temp_column", temp_column, if("<<FIELD>>" = "temp_Total", temp_Total, if(<<FIELD>> = 0, 0, 1)))]
| eval count = 0
`comment("Creating temporary "_unique" fields for all existing fields that are either a "1" or a "0" to determine if there was a hit that day. In this way, we can find unique daily hits, while preserving our original field values.")`
| foreach *_unique
[| eval count = if(<<FIELD>> = "1", count + 1, count)]
| fields - *_unique
`comment("Calculating a unique number of hits by row (See the "count" field), and then removing the temporary "_unique" fields.")`
| rename temp_* AS *
`comment("Removing the "temp_" prefix to return fields to their original names, since we're done using the foreach command")`
| transpose 0 header_field=column
| eval day = strftime(column, "%d-%b")
| eval day = if(isnull(day), column, day)
| eval column = if(isnum(column), column, null)
| eval column = if(isnull(column), now(), column)
| eval column = if(day = "count", "1", if(day = "Total", "2", column))
| sort column
| fields - column
| table day *
| transpose 0 header_field=day
| rename column AS Host, count AS DaysWithHits, Total AS TotalLast10
`comment("Converting epoch numbers to human readable time for column headers, and sorting headers so that "Hosts" will always come first, followed by "DaysWithHits","TotalLast10", and then reverse chronological order by date.")`
If there are problems, or there is anything you need clarified, let me know!
... View more
08-01-2019
05:10 PM
You're a wizard.
I was able to play around with the sample dashboard you provided and apply it to my production dashboard, and it works perfectly.
I appreciate your time and the incredibly detailed response. Thank you for making something I thought might be impossible a possibility!
... View more
08-01-2019
01:22 PM
I have been struggling with this one for a while now with no end in sight.
I'm not sure if this is even possible, but I need to show or hide a panel based on if the text field input is a public or private ip address.
To accomplish this, I'm using a regex against the $ip$ token created by the text search. If the regex matches, I consider it to be private. if it doesn't match, I consider it a public address.
Here is a dashboard you can copy and paste into your Splunk instance that describes my data and what I'm trying to do:
<form>
<search id="primary">
<query>|makeresults count=10000
| eval a = random()%255
| eval b = random()%255
| eval c = random()%255
| eval d = random()%255
| eval ip = random()%4
| eval src_ip = if(ip = 0, "10.".a.".".b.".".c, if(ip=1, "172.".a.".".b.".".c, if(ip=2, "192.".a.".".b.".".c, d.".".a.".".b.".".c)))
| eval dest_ip = if(ip = 0, "10.".a.".".b.".".c, if(ip=1, "172.".a.".".b.".".c, if(ip=2, "192.".a.".".b.".".c, d.".".a.".".b.".".c)))
| eval dest_port = random()%65535
| streamstats count
| eval src_ip = if(count > 500 AND count < 525, "192.168.1.1", src_ip)
| eval src_ip = if(count > 450 AND count < 475, "1.1.1.1", src_ip)
| fields - _time
| fields src_ip dest_ip dest_port
`comment("above is just generating dataset")`
| eval hiddenfield = "$ip$"
| rex field=hiddenfield "(?<private_ip>192\.168\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?|172\.[1-3][0-9]\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?|10\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?|192\.189\.2[0-1][056789]\.[1-2]?[0-9][0-9]?|[A-Za-z])"
| eval show_panel = if(isnotnull(private_ip), "1", null)
`comment("uses hiddenfield = "$ip$" token passed from main dashboard to find if the ip should be considered private")`</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<label>drilldown_troubleshoot</label>
<fieldset submitButton="false">
<input type="text" token="ip">
<label>Investigate IP</label>
</input>
</fieldset>
<row>
<panel>
<single>
<search base="primary">
<query>| head 1
| eval this = "This will always be populated. I need to show or hide this panel depending on if the IP is private or public"
| table this</query>
</search>
<option name="colorMode">block</option>
<option name="drilldown">none</option>
<option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
<option name="refresh.display">progressbar</option>
<option name="useColors">1</option>
</single>
</panel>
</row>
<row>
<panel>
<table>
<title>This will display information about any src_ip or dest_ip that matches the text search</title>
<search base="primary">
<query>| search src_ip=$ip$ OR dest_ip=$ip$
| table src_ip dest_ip dest_port</query>
</search>
<option name="drilldown">none</option>
</table>
</panel>
</row>
</form>
This is the direction I've been trying to take to make it work, but I can't get it to function properly:
<form>
<search id="primary">
<query>|makeresults count=10000
| eval a = random()%255
| eval b = random()%255
| eval c = random()%255
| eval d = random()%255
| eval ip = random()%4
| eval src_ip = if(ip = 0, "10.".a.".".b.".".c, if(ip=1, "172.".a.".".b.".".c, if(ip=2, "192.".a.".".b.".".c, d.".".a.".".b.".".c)))
| eval dest_ip = if(ip = 0, "10.".a.".".b.".".c, if(ip=1, "172.".a.".".b.".".c, if(ip=2, "192.".a.".".b.".".c, d.".".a.".".b.".".c)))
| eval dest_port = random()%65535
| streamstats count
| eval src_ip = if(count > 500 AND count < 525, "192.168.1.1", src_ip)
| eval src_ip = if(count > 450 AND count < 475, "1.1.1.1", src_ip)
| fields - _time
| fields src_ip dest_ip dest_port
`comment("above is just generating dataset")`
| eval hiddenfield = "$ip$"
| rex field=hiddenfield "(?<private_ip>192\.168\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?|172\.[1-3][0-9]\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?|10\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?\.[1-2]?[0-9][0-9]?|192\.189\.2[0-1][056789]\.[1-2]?[0-9][0-9]?|[A-Za-z])"
| eval show_panel = if(isnotnull(private_ip), "1", "0")
`comment("uses hiddenfield = "$ip$" token passed from main dashboard to find if the ip should be considered private")`</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<label>drilldown_troubleshoot</label>
<fieldset submitButton="false">
<input type="text" token="ip">
<label>Investigate IP</label>
</input>
</fieldset>
<row>
<panel depends="$panel$">
<single>
<search base="primary">
<query>
| head 1
| eval this = "This will always be populated. I need to show or hide this panel depending on if the IP is private or public"
| table this show_panel
</query>
<progress>
<set token="show">$result.show_panel$</set>
</progress>
<progress>
<condition match="$show$ = 0">
<set token="panel">true</set>
</condition>
<condition match="$show$ = 1">
<set token="panel"></set>
</condition>
</progress>
</search>
<option name="colorMode">block</option>
<option name="drilldown">none</option>
<option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
<option name="refresh.display">progressbar</option>
<option name="useColors">1</option>
</single>
</panel>
</row>
<row>
<panel>
<table>
<title>This will display information about any src_ip or dest_ip that matches the text search</title>
<search base="primary">
<query>| search src_ip=$ip$ OR dest_ip=$ip$
| table src_ip dest_ip dest_port</query>
</search>
<option name="drilldown">none</option>
</table>
</panel>
</row>
</form>
Any assistance is appreciated!
... View more
08-01-2019
10:36 AM
Firstly: Make sure your time range picker is set to 15 days ago for this search to function
Click here to see what your time picker should look like
Also, what does your volume field look like? Judging from the name of the field, I am hesitant to recommend "count" as a metric for aggregating volume. For the sake of safetly, I'm going to use average in my search, but you can change it back if you'd like.
sourcetype=server_volume host=xxx
| timechart span=10m avg(volume) AS volume
| timewrap 1d
| table _time *latest_day *7days_before *14days_before
| head 1
| rename volume_latest_day AS Today, volume_7days_before AS LastWeek, volume_14days_before AS TwoWeeksBefore
| eval LastWeek_Percent_Change = round((LastWeek - Today) / Today * 100,2)
| eval TwoWeeksBefore_Percent_Change = round((TwoWeeksBefore - Today) / Today * 100,2)
| table _time Today LastWeek LastWeek_Percent_Change TwoWeeksBefore TwoWeeksBefore_Percent_Change
... View more
07-31-2019
10:35 PM
Hey Sahil,
Give the timewrap command a try!
Copy and paste this search into Splunk:
|gentimes start=-15 end=1 increment=1m
|rename starttime AS _time
| table _time
| eval volume=random()%1000
| bin _time span=10m aligntime=@m
| chart sum(volume) AS sum_volume OVER _time
| sort - _time
| streamstats count
| eval count = count
| eval volume = round(sum_volume - count,1)
| fields - sum_volume count
`comment("This is just a comment to let you know that you don't need to worry about anything above this line. It's just used for generating a dataset")`
| timewrap 1d
| table _time *latest_day *7days_before *14days_before
| head 1
| rename volume_latest_day AS Today, volume_7days_before AS LastWeek, volume_14days_before AS TwoWeeksBefore
| eval LastWeek_Percent_Change = round((LastWeek - Today) / Today * 100,2)
| eval TwoWeeksBefore_Percent_Change = round((TwoWeeksBefore - Today) / Today * 100,2)
| table _time Today LastWeek LastWeek_Percent_Change TwoWeeksBefore TwoWeeksBefore_Percent_Change
The search should find what your volume is today at this moment, and compare it to what the volume at both one week ago, and two weeks ago. To top it off, we'll calculate the percent difference from today for both last week, and two weeks ago!
Documentation for timewrap can be found here: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/timewrap
I hope this helps, let me know if you have any questions!
... View more
07-31-2019
12:15 PM
No problem! Apologies, I just woke up and I'm still getting the gears to start turning.
The solution I provided is very tailored to the data set you provided for me. For that reason, there are a few things I feel like I should warn you about before giving you a solid answer:
1. This first part of this search operates under the assumption that all key value pairs will be in either of these two formats for the base field extraction:
"field1": "value1", "field2": "value2", etc...
"field1": " {"nested_field1":"nested_value1"}
Key value pairs that are not in either of these formats won't get extracted, and could potentially break the entire search.
2. I'm specifically avoiding using mvexpand because for dealing with my multivalue field because it's such a taxing command, and can get messy. I'm accomplishing this through the mvjoin/split/mvzip/extract command portion.
The part that you need to be careful about is your delimiters in the extract command. If your delimiter is a comma and you have commas in your base fields, the extraction will still work -- just not how you would expect. To get around this, we can replace the delimiters we're expecting to see with "rex mode=sed" and replace those values later on with a foreach command.
3. I repeat the process with a slightly different regex to pull the key value pairs out of the properties field. Essentially I'm looking for data in this format:
"field:value field:value field:value etc.."
Again, the key value pairs in this field need to always follow this format in order for the extraction to work.
4. Finally, the protocol, src_ip, src_port, dest_ip, and dest_port fields are not explicitly defined anywhere in the data, they needed to be extracted from the msg field using some logic.
That said, as long as the nested msg field in the properties field is in one of the formats you provided, the extraction will always pull this information. It will also pull those fields if they're in any of the formats previously mentioned since the extractions are dynamic.
I don't know too much on the Splunk backend, but I'm fairly certain I'm not using any expensive commands in this search (like mvexpand, join, transaction, map, etc), so you shouldn't have any problem applying it to big datasets. However, don't take my word on that and try it out for yourself.
In theory, the same process that's used for the "properties" field could be applied to any nested field in the data, although your search might be a bit large and proportionally slower if you do it a few times.
The search is fairly dynamic, but I wouldn't rely too heavily on it. If problems arise, let me know and I'm more than willing to help.
That all being said, I still believe @jacobpevans answer will point you more in the right direction.
Good luck!
... View more
07-30-2019
05:05 PM
Hey Jordan,
There's almost certainly a better way to do this, but I had fun creating this search. It works (at least) for the sample data. You'll have to tell me if you run into problems on the live data.
I hope this is useful in some way to you! (copy and paste this search into Splunk)
| makeresults count=1
| eval _raw = "{ \"category\": \"AzureFirewallNetworkRule\", \"time\": \"2019-07-30T17:43:59.9812590Z\", \"resourceId\": \"/SUBSCRIPTIONS/XXXX1111-XX11-XX11-XX11-XXXXXX111111/RESOURCEGROUPS/NET-XXX1-XXX1-RG/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/NET-XXX1-XXX1-FW01\", \"operationName\": \"AzureFirewallNetworkRuleLog\", \"properties\": {\"msg\":\"TCP request from XXX.X.178.18:42132 to XXX.X.242.12:51113. Action: Allow\"}}~{ \"category\": \"AzureFirewallNetworkRule\", \"time\": \"2019-07-30T17:44:59.4538600Z\", \"resourceId\": \"/SUBSCRIPTIONS/XXXX1111-XX11-XX11-XX11-XXXXXX111111/RESOURCEGROUPS/NET-XXX1-XXX1-RG/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/NET-XXX1-XXX1-FW02\", \"operationName\": \"AzureFirewallNetworkRuleLog\", \"properties\": {\"msg\":\"ICMP request from XXX.X.10.1 to XXX.X.69.5. Action: Allow\"}}~{ \"category\": \"AzureFirewallNetworkRule\", \"time\": \"2019-07-30T16:13:54.9901410Z\", \"resourceId\": \"/SUBSCRIPTIONS/XXXX1111-XX11-XX11-XX11-XXXXXX111111/RESOURCEGROUPS/DMZ-XXX1-XXX1-RG/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/DMZ-XXX1-XXX1-FW01\", \"operationName\": \"AzureFirewallNatRuleLog\", \"properties\": {\"msg\":\"TCP request from XXX.X.131.34:3318 to XXX.X.224.170:3299 was DNAT'ed to XXX.X.80.5:3299\"}}~{ \"category\": \"AzureFirewallNetworkRule\", \"time\": \"2019-07-30T16:39:45.5354460Z\", \"resourceId\": \"/SUBSCRIPTIONS/XXXX1111-XX11-XX11-XX11-XXXXXX111111/RESOURCEGROUPS/DMZ-XXX1-XXX1-RG/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/DMZ-XXX1-XXX1-FW01\", \"operationName\": \"AzureFirewallThreatIntelLog\", \"properties\": {\"msg\":\"TCP request from XXX.X.231.199:33348 to XXX.X.224.170:443. Action: Alert. ThreatIntel: Port Scan\"}}~{ \"category\": \"AzureFirewallApplicationRule\", \"time\": \"2019-07-30T17:19:00.1880780Z\", \"resourceId\": \"/SUBSCRIPTIONS/XXXX1111-XX11-XX11-XX11-XXXXXX111111/RESOURCEGROUPS/DMZ-XXX1-XXX1-RG/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/DMZ-XXX1-XXX1-FW01\", \"operationName\": \"AzureFirewallApplicationRuleLog\", \"properties\": {\"msg\":\"HTTPS request from XXX.X.110.8:65486 to abcd.efghijk.com:443. Action: Deny. No rule matched. Proceeding with default action\"}}~{ \"category\": \"AzureFirewallApplicationRule\", \"time\": \"2019-07-30T12:00:04.0868100Z\", \"resourceId\": \"/SUBSCRIPTIONS/XXXX1111-XX11-XX11-XX11-XXXXXX111111/RESOURCEGROUPS/DMZ-XXX1-XXX1-RG/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/DMZ-XXX1-XXX1-FW01\", \"operationName\": \"AzureFirewallApplicationRuleLog\", \"properties\": {\"msg\":\"HTTPS request from XXX.X.66.64:30476. Action: Deny. Reason: SNI TLS extension was missing.\"}}"
| eval raw = split(_raw, "~")
| mvexpand raw
| rename raw AS _raw
`comment("Nothing above here matters, just recreating your dataset. You should be able to copy and paste everything below onto your real search.")`
| rex field=_raw max_match=0 "\"(?<field>[^\"]+)\"\:\s(?<value>.*?)(?:\,\s|\}$)"
| eval field = mvjoin(field,","), value = mvjoin(value,"~,")
| eval field = split(field, ","), value = split(value, ",")
| rename _raw as tempraw
| eval _raw = mvzip(field, value)
| rex field=_raw mode=sed "s/=/|||/g"
| extract kvdelim="," pairdelim="~" mv_add=t
| foreach *
[ rename <<FIELD>> AS <<FIELD>>_temp
| rex field=<<FIELD>>_temp mode=sed "s/\|\|\|/=/g"
| rename <<FIELD>>_temp AS <<FIELD>>]
| fields - field value test
| rex mode=sed field=properties "s/{//g"
| rex mode=sed field=properties "s/}//g"
| rex field=properties max_match=0 "(?<field>[^\:]+)\:(?<value>.*?)(?=\.?\s[A-Za-z]+\:|$)"
| rex field=field mode=sed "s/\.//g"
| rename properties AS _raw
| eval field = mvjoin(field,","), value = mvjoin(value,"~,")
| eval field = split(field, ","), value = split(value, ",")
| eval _raw = mvzip(field, value)
| rex field=_raw mode=sed "s/=/|||/g"
| extract kvdelim="," pairdelim="~" mv_add=t
| foreach *
[ rename <<FIELD>> AS <<FIELD>>_temp
| rex field=<<FIELD>>_temp mode=sed "s/\|\|\|/=/g"
| rename <<FIELD>>_temp AS <<FIELD>>]
| fields - field value
| rename tempraw AS _raw
| rex field=msg "(?<protocol>\S+) request from (?<src_ip>[^\:\s]+)\:?(?<src_port>\d+)? to (?<dest_ip>[^\:\s]+)\:?(?<dest_port>\d+)?"
| rex field=msg "(?<protocol>\S+) request from (?<src_ip>[^\:\s]+)\:?(?<src_port>\d+)?"
| table _time _raw category resourceId operationName src_ip src_port dest_ip dest_port protocol Action Reason ThreatIntel msg *
... View more
07-30-2019
11:47 AM
2 Karma
Hey Russell,
If the intention is to treat the "apple orange" multivalue fields as a unique fruit type, we'll need to remove convert them to a non multivalue field. This can be done in a few different ways, but for this example I'll use the nomv command.
Once that is done, we can then run a stats count by fruit and it should produce the results we want, just improperly formatted.
The "rex mode=sed" portion isn't nessesary, but I end up using it to replace any multivalue fields with an "and" breaker for later formatting.
Then, we'll simply use the transpose command to use our "fruit" column values as our new data headers.
Lastly we'll use the rename command to add the string "just_" to all of our field names.
I'm providing some fake data so you can play around with it to see how it works here (copy paste this into any Splunk instance):
|makeresults count=20
| eval container = random()%70
| eval fruit = random()%3
| eval fruit = if(fruit = 0, "apple,orange", if(fruit = 1, "apple", if(fruit = 2, "orange", "tomato?")))
| eval fruit = split(fruit, ",")
| nomv fruit
| stats count by fruit
| rex field=fruit mode=sed "s/\n/_and_/g"
| transpose header_field=fruit
| fields - column
| rename * AS just_*
Nomv Command Documentation: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Nomv
Transpose Command Documentation: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Transpose
Rex Command Documentation (see sed expression header): https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/rex
Let me know if anything doesn't make sense, or it isn't working for you and I'm happy to help!
... View more
07-26-2019
01:48 PM
1 Karma
Hey Jwalzerpitt!
I can't remember why, but I've worked with this same symantec extraction before. I went ahead and found it and made it work for your data.
This should work for the dataset you provided:
(?:Application\shash:\s*(?<Application_Hash>[^%\,]+))?,\s*(?:Hash\stype:\s*(?<Hash_Type>[^%\,]+))?,\s*(?:Company\sname:\s*(?<Company_Name>[^\,]+))?,\s*(?:Application\sname:(?<Application_Name>[^%\,]+))?,(?:Application\sversion:\s*(?P<Application_Version>[^\,]+))?,\s*(?:Application\stype:\s*(?<Application_Type>[^%\,]+))?,\s*(?:File\ssize\s\(bytes\):\s*(?<File_Size>[^%\,]+))?(?:,\s*Category\sset:\s*(?<Category_Set>[^%\,]+),\s*Category\stype:\s*(?<Category_Type>[^%\,]+))?,?\s*(?:Location:\s*(?<Location>[^%\,]+))?\,(?:Intensive\sProtection\sLevel:\s*(?<Intensive_Protection_Level>[^%\,]+))?,?\s*(?:Certificate\sissuer:\s*(?<Certificate_Issuer>[^%\,]+))?,?\s*(?:Certificate\ssigner:\s*(?<Certificate_Signer>[^%\,]+))?,?\s*(?:Certificate\sthumbprint:\s*(?<Certificate_Thumbprint>[^%\,]+))?,?\s*(?:Signing\stimestamp:\s*(?<Signing_Timestamp>[^%\,]+))?,?\s*(?:Certificate\sserial\snumber:\s*(?<Certificate_Serial_Number>.*?(?:[^%\,]+|$|\z)))
Also, you might find some use out of the extract command. The dataset you provided is consistantly in a "FIELD:VALUE," format.
Give the extract command a try to see if it works to accomplish your goal like this:
...BASE SEARCH...
| extract kvdelim=":" pairdelim=","
If you run into any problems let me know and I'm happy to help.
... View more
Contact Me
| Online Status |
Offline
|
| Date Last Visited |
05-18-2022
03:17 PM
|
