Splunk Search

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

greco7760
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(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

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

DalJeanis
SplunkTrust
SplunkTrust

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 
0 Karma

jkat54
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

greco7760
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

jkat54
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

greco7760
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

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

greco7760
New Member

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.

0 Karma

greco7760
New Member

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

0 Karma

jkat54
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

greco7760
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

jkat54
SplunkTrust
SplunkTrust

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]

0 Karma

greco7760
New Member

query OF* proxy logs

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...