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

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

greco7760 — Tue, 29 Sep 2020 09:39:33 GMT

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

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?

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

jkat54 — Sat, 07 May 2016 01:37:02 GMT

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]

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

greco7760 — Sat, 07 May 2016 04:23:54 GMT

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.

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

greco7760 — Sat, 07 May 2016 04:24:59 GMT

query OF* proxy logs

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

jkat54 — Tue, 29 Sep 2020 09:39:51 GMT

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

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

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

greco7760 — Sat, 07 May 2016 15:48:48 GMT

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

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

jkat54 — Sat, 07 May 2016 17:24:18 GMT

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

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

greco7760 — Mon, 09 May 2016 14:12:31 GMT

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!

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

jkat54 — Mon, 09 May 2016 15:11:59 GMT

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-search.html

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

greco7760 — Mon, 09 May 2016 15:21:03 GMT

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.

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

jkat54 — Mon, 09 May 2016 15:47:09 GMT

are the timestamps correct in both indexes? same timezone, etc?

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

greco7760 — Mon, 09 May 2016 19:45:38 GMT

OK looking at the logs for that query this is the closest to working this has gotten so far.

The literal search it creates looks like this.

litsearch source=bcoat_proxysg ( ( L_IP="10.36.186.202" _time>=1461763562.000 _time<1461764162.000 ) OR ( L_IP="10.20.222.118" _time>=1461589740.000 _time<1461590340.000 ) OR ( L_IP="192.168.1.78" _time>=1461797959.000 _time<1461798559.000 ) OR ( L_IP="10.10.15.87" _time>=1461608960.000 _time<1461609560.000 ) OR ( L_IP="192.168.12.51" _time>=1461937767.000 _time<1461938367.000 ) OR ( L_IP="192.168.178.16" _time>=1461955982.000 _time<1461956582.000 ) OR ...............

and it just goes on. So essentially it is creating a long literal search and the times are getting handles appropriately. The problem, I think, is that the IP is retaining its label and in the proxy logs that field isn't named like that. How do I get it to pass up just the IP value and not the field name?

I fully expect no results from 192 ranges as the user was clearly offsite, but the internal IPs should generate results and do generate results when run manually.

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

DalJeanis — Wed, 19 Apr 2017 22:26:50 GMT

You do it more or less like this...

sourcetype=sep:ids earliest=1441065601 "BadString" 
| eval epc=strptime(substr(begin_time,8,26),"%Y-%m-%d %H:%M:%S")
| eval early=(epc-300) 
| eval late=(epc+300) 
| eval badIP = L_IP
| table badIP,early,late 

| rename COMMENT as "The above search gets your various values, then you run them into the actual search like this..."

| map search="search index=foo earliest=$early$ latest=$late$ L_IP=$badIP$  (the rest of your search here)" maxsearches=100

or, if L_IP doesn't exist as a field but the IP value is somewhere in the _raw as a literal...

| map search="search index=foo earliest=$early$ latest=$late$ \"$badIP$\"  (the rest of your search here)" maxsearches=100