Title
#fleet
t

Tarek Talaat

07/11/2022, 6:54 PM
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 ?
Kathy Satterlee

Kathy Satterlee

07/11/2022, 7:02 PM
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.
t

Tarek Talaat

07/11/2022, 7:03 PM
oh I don't include the path in the WHERE. ok, one sec
7:03 PM
SELECT path, signed, team_identifier from signature where team_identifier like "%abcd%";
7:05 PM
it works without any errors if I include the path, but what if I don't know the path and I want to get it?
Kathy Satterlee

Kathy Satterlee

07/11/2022, 7:24 PM
You could use wildcards in
path
:
SELECT path, signed, team_identifier from signature WHERE team_identifier like "%abcd%" AND path LIKE '/Applications/%'
t

Tarek Talaat

07/11/2022, 8:58 PM
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.
Kathy Satterlee

Kathy Satterlee

07/11/2022, 9:06 PM
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!
t

Tarek Talaat

07/11/2022, 9:07 PM
I'm writing a query to hunt APT32 AKA oceanlotus
Kathy Satterlee

Kathy Satterlee

07/11/2022, 9:33 PM
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!
t

Tarek Talaat

07/11/2022, 9:35 PM
Thank you, I'll check the article out. Appreciate the help.
Kathy Satterlee

Kathy Satterlee

07/11/2022, 9:37 PM
Any time!