Hey guys, I'm getting this error when I run a query on the signature table "constraint failed" the error from the log on my host shows Table signature was queried without a required column in the WHERE clause even though I have a column specified in the WHERE clause, any idea why it's happening ?

Hi, @Tarek Talaat! Are you including the

path

column in your

WHERE

clause? If you could send over the query with any identifying info removed, that would be helpful for diagnosing the issue.

oh I don't include the path in the WHERE. ok, one sec

You could use wildcards in

path

:

SELECT path, signed, team_identifier from signature WHERE team_identifier like "%abcd%" AND path LIKE '/Applications/%'

I'm trying to identify a malware, and it could be downloaded some where else other than /Applications/, but I'll try a wild card to include anything and see if that would help.

That makes sense. What are you trying to hunt down? Someone here might have already started down the path to hunting it down or have some other knowledge to drop!

I'm writing a query to hunt APT32 AKA oceanlotus

Gotcha. I found query packs that should be helpful for pointing you in the right direction (or may work for you as is): https://github.com/osquery/osquery/blob/master/packs/osx-attacks.conf#L375 https://github.com/r3doubt/apple-sauce-in-a-bucket/blob/master/APT32-pack.conf The latter has a related blog post that is pretty cool!

Thank you, I'll check the article out. Appreciate the help.

Any time!