Splunk Search
Highlighted

Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

New Member

Short story, alert results to populate proxy query of dependent time ranges.

Longer story-
So essentially lets say I have a string that shows in my repository of SEP:IDS logs.

I have a query that shows me a summary of IPs and then calculates a +- 5min field as such

search sourcetype=sep:ids earliest=1441065601 "NaStY AtTaCkEr StRiNg" | eval time=substr(begintime,8,26) | eval epc=time | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(epc) | eval early=(epc-300) | eval late=(epc+300) | fields + late | rename late as latest | fields + early | rename early as earliest | fields + LIP

The time field is a substring from the raw data as the original carve out has some garbage at the front and I don't want that. After that the time string is converted to a time format, then to an epoch format to I can have some calculated fields on plus and minus 5 min. The L_IP field is another carved field for the IP of the victim or local machine generating the alert.

Running the query without the field push-ups and simply making a table works easy:

... eval epc=time | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(epc) | eval early=(epc-300) | eval late=(epc+300) | table L_IP,time,early,late

But I want to take each alert and the +- range to query the proxy logs for those IPs in those time ranges to do some correlation with the IDS alerts and activity etc.

When I use this as a sub-query pushing up the field values I get nothing, I know some of these won't have proxy logs and that there is a limit to what a sub query can push back, but getting nothing is a problem as manually the data is there.

Is there a splunk Ninja out there who can point me in the proper direction?

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

SplunkTrust
SplunkTrust

I don't understand the use of fields commands. I believe the earliest and latest are conflicting with internal field names (earliest and latest). Try naming them something different:

 ...   | eval epc_earliest=(epc-300) | eval epc_latest=(epc+300) | table L_IP,time,epc_earliest,epc_latest

Then when you use it as a sub search:

 index=indexYoureCorrelatingWith  earliest=epc_earliest latest=epc_latest [ search ...   | eval epc_earliest=(epc-300) | eval epc_latest=(epc+300) | table L_IP,time,epc_earliest,epc_latest] 
0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

New Member

The use of the internal fields is intentional. So maybe I should state it simpler.

Query for bad string in IDS logs

From results carve out host IP and time

Calculate 5 min earlier and 5 min later

Pass resulting IP, earliest time, latest time to a query or proxy logs

Do post analysis of resultant traffic history on proxies surrounding alert on host.

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

New Member

query OF* proxy logs

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

SplunkTrust
SplunkTrust

The use of the FIELDS commands is what I dont understand.

Try it without the time field.
index=indexYoureCorrelatingWith [ search ... | eval epcearliest=(epc-300) | eval epclatest=(epc+300) | table L_IP,earliest,latest]

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

New Member

Oops, i thought the field designation was how you formatted the field being pushed up. Ill try that this afternoon.

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

SplunkTrust
SplunkTrust

The table command is doing the work of the fields commands. I usually only use the fields command when I need to remove fields.

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

New Member

So simplifying what we are doing:

index=bcoat_proxysg earliest=early latest=late [ search sourcetype=sep:ids earliest=1441065601 "BadString" | eval time=substr(begin_time,8,26) | eval epc=time | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(epc) | eval early=(epc-300) | eval late=(epc+300) | table L_IP,early,late ]

Gives this error:

Invalid value "early" for time term 'earliest'

So just to be clear I wasn't combining the Table command with the fields creation command, I was just using it to validate the data was coming out in a format that was usable, with epoch times and an IP.

Trying it without the table and trying to pass up the fields gets nothing no error just nothing:

source=bcoat_proxysg [ search sourcetype=sep:ids earliest=1441065601 "Nuclear" | eval time=substr(begin_time,8,26) | eval epc=time | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(epc) | eval early=(epc-300) | eval late=(epc+300) | fields + late | rename late as latest | fields + early | rename early as earliest | fields + L_IP ]

index=bcoat_proxysg [ search sourcetype=sep:ids earliest=1441065601 "Nuclear" | eval time=substr(begin_time,8,26) | eval epc=time | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(epc) | eval early=(epc-300) | eval late=(epc+300) | fields + late | rename late as latest | fields + early | rename early as earliest | fields + L_IP ]

Starting to wonder if I need to store results as a temp table and then loop through it? Full Disclosure I did not configure this environment nor am I a splunk pro, there may be some oddities in the environment that cause it to perform differently.

Thanks!

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

SplunkTrust
SplunkTrust

same here... not a splunk pro! 😉

I'm terrible at subsearches for sure:

I referenced another answer to come up with the below:

 index=bcoat_proxysg  [ search sourcetype=sep:ids earliest=1441065601 "BadString" | eval time=substr(begin_time,8,26) | eval epc=time | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(epc) | eval earliest=(epc-300) | eval latest=(epc+300) | table L_IP,earliest,latest | format "(" "(" "" ")" "OR" ")"  ]

https://answers.splunk.com/answers/136791/use-a-subsearch-to-define-earliest-and-latest-for-main-sea...

0 Karma
Highlighted

Re: Multi-Field Subsearch or Pivot or Join on sources... I'm Lost.

New Member

No dice. (-_-) I got 0 results so it is trying but something is breaking.

I am experimenting with writing to a lookuptable and then referencing it.

If it works I will post.

0 Karma