How to lookup against two-column table?

[LeeMoe]

I have an index with roughly 1.6 million records and want to compare the roughly 370'000 entries in the table with username/password against a mirai list.

My index is searched from this point:

index="myindex"
| rex "message=\"(?<message>{.+})\" +path="
| eval message = replace(message, ".\"", "\"")
| spath input=message

This basically parses the JSON WebHooks in the index into fields.  Two incoming fields are interesting, Username and Password.

What I want to do is relate these into a lookup table I have loaded which has Username and Password colums too (mirai-passwords.csv).  The ideal situation would be to have a count of each match (on Username/Password combo) plus have a count of matches that I relate against all of the Username and Passwords in the index (thus showing a percentage of the hits against the total volume)

I thought this should work but it returns nothing.

index="myindex"
| rex "message=\"(?<message>{.+})\" +path="
| eval message = replace(message, ".\"", "\"")
| spath input=message
| lookup mirai-passwords.csv Username OUTPUT Password
| stats count by Username, Password
| eval TotalCount=mvindex(split(PasswordList,"|"),1)
| eval PercentageCount=count/TotalCount*100

Anyone able to shed some light to help me?  

Thanks 🙂

[PickleRick]

I'm not sure what you mean, to be honest. I'm testing on a simple test lookup and it seems to work.

An example:

| makeresults 
| eval a=mvappend("{ \"data\":{ \"Username\":\"root\",\"Password\":\"root\"}}","{ \"data\":{ \"Username\":\"root\",\"Password\":\"ro0t\"}}") | mvexpand a 
| spath input=a 
| rename data.* as *
| lookup mytestlookup.csv Username Password OUTPUT Password Username

 It creates two separate jsons, parses them with spath, then runs the results thru a lookup.

In my case the event with password "root" is contained in the test lookup so it's retained in the results and the "ro0t" password is not present in the lookup so both User and Password are getting cleared.

[LeeMoe]

I strimmed back the query and can offer an update as what I offered was incorrect; the parsed fields from the JSON are logdata.Username and logdata.Password.

I've tried adding the stats and the lookup one-by-one and the records returned go from many to none, the exception being this gives me all records:

| lookup mirai-passwords.csv Username Password OUTPUT Password as Found

I assume that's because my extracted fields are not named as this.

There is a dataset where the colums are labelled as Username and Password - can I leverage that perhaps?

[PickleRick]

OK. So spath gives you differently named fields. That's OK.

lookup command can live with that 🙂

You can either

| rename logdata.Username AS Username, logdata.Password AS Password

(which might be the simplest idea because you'll have less typing later :-))

Or do a lookup using your field names which don't match 1-1 field names from the lookup

| lookup mirai-passwords.csv Username AS logdata.Username Password AS logdata.Password OUTPUT Password as Found

Of course all other remarks from @bowesmana are stil valid

[bowesmana]

In addition to @PickleRick comments about debugging, you say you want to have

count of each match (on Username/Password combo)

so you should probably be doing the lookup on both fields, otherwise for every event with Username+Password, you will get back ALL passwords used for Username to each event, i.e.

Event 1: Username X, Password USED=A Password RETURNED=A,B,C
Event 2: Username X, Password USED=B Password RETURNED=A,B,C
Event 3: Username X, Password USED=C Password RETURNED=A,B,C

and as you are using OUTPUT Password, you will overwrite the USED password, so perhaps

| lookup mirai-passwords.csv Username Password OUTPUT Password as Found

which then gives you a new field 'Found' which will be the same as USED Password if found in the lookup and null if NOT found. That will give you the ability to count hits.

Note that your existing stats command will be doing a multivalue field expansion of the Password field.

Your stats should probably be

| stats count(eval(if(isnull(Found), null(), Found))) as Hits by Username, Password

which will tell you how many hits you had for a particular Username/Password combo.

[PickleRick]

If something doesn't work as a whole, retrace back your steps to the point where it worked and start debugging from there.

You're saying that up to and including the spath command it worked. Does it produce any results with just added lookup command? If so, add another command. And another...

Anyway, first you do

| stats count by Username Password

which will give you just three fields - count, Username and Password and then you do some evals based on non-existant field PasswordList so the last two evals will produce empty columns.

[LeeMoe]

I'm feeling a real bone-head because I cannot make it work.  So I bring some images.....

I have a dataset built out from the initial breakdown of the JSON messages.  Two actually - can I leverage them?  That works.

Can I leverage something from one of those?

For sanity, this is my csv lookup:

 I can reload the lookup to have a single column as per my manufactured column above of course.

Thanks for your patience!