I work for a certain agency which maintains a list of names of individuals who are on a "no-fly" list. Every day, sometimes several times per day, we get updates to this list, which can have tens of thousands of names on it. At the same time, we receive lists of passenger ticket bookings from around the globe...this list, as you can imagine, is absolutely massive. At the moment it's around 150 gigabytes per day, as it contains passenger names, dates of birth, locations of departure, destination, payment details, travel agency (if used) and much, much more. The question is, what's the most efficient way, in Splunk, to cross-reference these lists and only find, on a given day, or even within a given hour (happy with a sliding search and summary-index, if needed), those people on our no-fly list on the much larger all-passenger list? Making a query like so is hardly efficient: index=travellers [ search index=nofly | fields name] As that would result in Splunk "parsing" the search and realising it's essentially being asked to do: index=travellers (jane OR bill OR eve OR bob OR ... ) Where the OR clause could be tens of thousands of names long. I've pondered creating a summary-index of just the traveller names from the travellers index, which would be a smaller amount of data, but would still take considerable time to extract, and would also need at least the Date of Birth and likely other fields as it turns out a lot of humans share the same name, and that can get rather embarrassing when adding people to the no-fly list, let me tell you. The typical approaches that I've used for other queries work great when the data set is small, but at the scale we're looking at here, it becomes a painfully slow process. Obviously, we could throw more hardware at the problem, as our agency budget is unrestricted and no one is allowed to question it, but I'm hoping that there's a much better way to do this and ensure that we have optimal tracking of people on the no-fly list with all potential travellers. Thanks! NB: (I jest; this isn't really what I'm trying to compare, it's just a great example of the type and scale of data) ... View more

I'm looking to develop a table/report which shows me IP addresses in a HTTP access log whereby the client first generated more than, say, 1000 errors (404, 4xx) across unique urls, get that exact count, and then display the number of successful transactions from that same IP. Whilst I can do a search such as: index=www http_response="2*" [search index=www http_response="4*" | stats dc(url) as url_failures by clientip | where url_failures > 1000 ] | stats dc(url) as url_success by clientip Obviously, that doesn't give me what I want, as what I want is a table output that looks like this: clientip failures successes 1.2.3.4 1083 320 5.6.7.8 8312 9004 I know that I've seen an example of this somewhere in the command examples/manual, but can't retrace what got me there. The important part is that I only want to count the successes for cases where a given IP has generated more than a certain number of failures. Thanks for any pointers...I've tried doing this with join and subsearch and just can't get what I'm after. I suspect I'm going to have to throw some eval in there. ... View more

Hi, In one of my indexes I've got a series of pipe separated fields which has one value expressed as so: 31.22:88.91 In other cases it's merely: 88.91 In order to match both occurrences of 88.91, I'm currently doing: ... | rex field=foo ":(?<locx>\d+\.\d+)$" | rex field=_raw "|(?<locx>\d+\.\d+)|" ... This correctly extracts into "locx" cases where the raw data may appear as: John|Yellow|31.22:88.91|Thursday|Pass Eve|Red|73.22:88.91|Monday|Pass Mary|Green|88.91|Friday|Fail But it will not put into locx the following version: Mark|Red|88.91:36.03|Monday|Pass The problem is what comes next - say the final field is "test_result" and I want to match all of the values of locx where the test_result is pass, but then I want to find the events where the locx from the test_result=pass is set, but only when locx is the second element in the colon separated version of the field, or when it's the only value (but never in the case of Mark where it's the FIRST value). What's the best way to do this? My thinking was that I'd do a subsearch for the pass conditions, get the value of locx for the Pass conditions, and the go back over the data and search for the fails; but that matches locx in the case of Mark where 88.91 is the first of the two fields. I'm doing a horrific job of explaining this, but what I was looking for was the right way to say: "Match this condition in the data and then look for this field. For each value of that field, go back through the data and find other matches for a different condition." From my pseudo data above, I'm trying to find people who failed an exam that took course 88.91 on its own, or, after taking some other course first. But I don't want to find people who took course 88.91 and failed an exam if they took another course after 88.91. I need a drink. ... View more

I have had a sourcetype working fine for months which used a | as a field delimiter in a CSV file with a header. Yesterday, the file had a few new fields added to the end of the line (header and data lines) -- the header shows this, the fields have data, the data is indexed, but the fields are not being extracted. I have restarted Splunk, no joy. Why might Splunk 6.0.3 ignore new fields? Do I have to create an all new sourcetype? A bit more information. My props.conf now reads: [sdrheader2] INDEXED_EXTRACTIONS = PSV MAX_TIMESTAMP_LOOKAHEAD = 23 TIME_FORMAT = %d/%m/%Y %H:%M:%S TIME_PREFIX = ^\d+\| And I've adjusted my inputs.conf to read: [monitor:///home/sdr/incoming] crcSalt = <SOURCE> initCrcLength = 2048 disabled = false followTail = 0 index = sdr sourcetype = sdrheader2 whitelist = \.csv$ Yet even after restarting Splunk, deleting some files which have the new header format from Splunk (| delete) and manually re-adding them with a oneshot add with the new sourcetype, the fields aren't being processed. I've also tried making this a CSV with "FIELD_DELIMITER=|" defined, and that's what the original sdrheader sourcetype was defined as - it works for all fields except the new ones which started appearing yesterday. The props/input.conf field are on my indexer, nothing relevant to this sourcetype on my Search Head. ... View more

I tried the Google Maps app under Splunk 6 and noticed that it's not graphing more than a few hundred hits, even though the results are far more numerous. I see that it doesn't explicitly state that it works under Splunk 6, but, I was wondering if anyone else has noticed this. The app: http://apps.splunk.com/app/368/ My search results in hundreds or thousands of results, but only a few hundred ever show up in Google Maps; and it's not a case of aggregation where zooming shows more (tried checking). Anyone else seen this or know a workaround? I should add that if I re-run the same query over and over I get a different number of results with each query. Once it may be 501, then 561, then 550, then 512. I have a distributed search head/indexer setup if that matters. ... View more

Hi, I'm likely going about my search in the wrong way, but I'm trying to create a table of data which draws upon a subsearch and a join in order to more completely represent the various values a given piece of data can have. In one source of data, host "fubar" I have serial numbers for products purchased. I want to create a table which looks for particular serial numbers, and then finds the corresponding IP addresses that they were purchased from in another source. To my thinking, a subsearch and join should have accomplished this, and it appears to, but only for a subset of the data, and I'm not sure why. My search is something like this: index=main source="iplog.2012.txt" successful recharge from [ search index=main host=fubar 77512345 OR 77512377 | fields MOO | rename MOO as query ] | rex field=_raw "IP: .(? \d+\.\d+\.\d+\.\d+)." | rex field=_raw moo: .(? \d+)." | join max=0 overwrite=false MOO [ search index=main source="oinkdata.txt" | fields MOO,OINK ] | table ip,MOO,OINK In human terms, "For the purchases with the two provided ID numbers, find the MOO fields (which represent the part number in question) and look back through the IP logs for those part numbers and join with the OINK field (sales associate name) to create a table of which products were bought from what IP address and associated with which sales associate. I've tried changing the join parameters a few times and in a few ways, but I'm missing something about the logic of what I'm doing, so I'm obviously going about it in the wrong way. I just want to find all of the MOOs, then look for them in the IP log data, and create a nice table of information... I need more coffee. ... View more

Hi, Looking for tips/hints on the best way to extract a value from a sub-search, including the timestamp that the is associated with the value, and perform a search in another index for that value within a fuzzy-ish range around that same timestamp. Scenario is this: index foo has an IP address and a time and date. index bar ALSO has IP address, time and date, but contains additional values, such as OS In meta-search syntax, I'd like to do: index=bar fuzzyrange=2hr [ search index=foo ip=1.2.3.4 | return ip,timestamp ] And I'd like Splunk to magically take the timestamp from the subsearch and look for the same IP in the bar index, but with a two hour variation on that timestamp. Is there an easy way? ... View more

Hi, Obviously Splunk has some native understanding of LDAP for authentication, but my desire is to use it to look up objects/people/whatever in our LDAP directory, but informational or enrichment purposes. For example, if I've got a syslog entry which displays a user's username, and that's also their cn in our LDAP setup; I'd like to be able to map that back to their givenName at search time. My first thought was to make a Python script which could be an external lookup - and I've got as far as that working and generating JSON output for a given input, but I'm not sure if that's the best approach, nor how to actually link that to Splunk. In my ideal setup, I'd be able to do something akin to: source=firewall failed | lookup ldap user | table user,givenName And assuming that my firewall logs had nice and obvious "user=bob" values, then this command would grab all the matching data for cn=user (I realise I may have to do something with eval to set which LDAP attribute I want to lookup, it may not always be cn) and generate something like: bob Robert Jones sally Sally Smith thelo Thelonious Monk Is a lookup the right way to do this? I tried reading in some JSON data and using spath to make sense of it, but I'm not sure if that's the right option here - the ldapsearch will return several results, and a JSON array or Python list seemed to be the two best options given Splunk's preference for Python. ... View more

I get the feeling this is going to be a tough one to solve, but, I'm trying to aggregate results of a search based upon domain name. I realise that this is a bit of a non-starter simply because of things like fubar.com versus fubar.co.uk, but, my first approach to this was: search term | eval mydomain=split(dest_host,".") | eval tld=mvindex(mydomain,-1) | eval target=mvindex(mydomain,1) | eval hoster=target.".".tld And this works most of the time, but not all of the time. I'm operating on the (questionable) assumption that the last two elements split by dest_host are likely to be the domain name - but maybe there's a better way to perform this aggregation. I'm trying to group together results which might be for host123-ab.fubar.com and host445-qx.fubar.com under fubar.com, for example. I suppose another way to do this is to use some sort of a lookup table with all well-known TLDs and major sub-domains (.co.uk, .ac.uk and so forth) -- but it feels like a problem others must have tried to resolve here already. Suggestions welcome! ... View more

Hi, I've tried a few of the hints here to solve this one elegantly but can't quite get there. I have two sources of data. One is "used" and the other is "valid" - the values in "valid" contain ranges of numbers for product IDs, whilst the "used" source is what is actually sitting in an inventory system. A "valid" value for a product ID could be something starting with: 0231 0232 049 073 And in the actual "used" data I have product IDs such as: 0231930128 023183818 074432131234 8313952 What I'm looking for is a way to extract out the values of product IDs for which there's no value prefix in the valid source to match. In this simple example, the 074432131234 and 8313952 entries (I want the entire event, not JUST this field) should appear, but not the first two. I've tried various games with eval, tables to assign "Y" values, comparing the two, join inner, join outer, but there must be a better and easier way. ... View more

Hi, I get the feeling that there's a better/faster way for me to do what I'm doing. I have a query such as this: index=bigger [ search index=smaller source="stuff.txt" | fields data | rename data as query ] | table EventTime,info What I'm looking for is the "data" field in the "smaller" index and "stuff.txt" source anywhere in the "bigger" index. The "data" field happens to occur in the "info" fields in the "bigger" database... Is this the best way to do such a query, or is there a better option that I'm overlooking? I thought about a "join" but fields are not named consistently - probably fixable, but, wasn't sure which approach is the fastest. Thanks. ... View more