wmi: adding utility function to convert datetime to FILETIME #5901

muffins · 2019-10-18T06:12:37Z

Summary:

See #5746 for more details. This PR adds a helper function to
convert datetime BSTRs passed back from WMI queries into FILETIME
structs, which we can convert using our existing utility into a Unix
Epoch time.

Test Plan:
Sample queries against video_info:

osquery> select driver_date from video_info;
+-------------+
| driver_date |
+-------------+
| 1563926400  |
+-------------+

Checking that time:

In [2]: datetime.datetime.fromtimestamp(1563926400  )
Out[2]: datetime.datetime(2019, 7, 23, 17, 0)

And os_version:

osquery> select install_date from os_version;
+--------------+
| install_date |
+--------------+
| 1558581844   |
+--------------+

And that time in a datetime:

In [3]: datetime.datetime.fromtimestamp(1558581844)
Out[3]: datetime.datetime(2019, 5, 22, 20, 24, 4)

Both look pretty accurate.

zwass · 2019-10-18T06:43:36Z

How difficult would it be to add a unit test for this conversion function?

muffins · 2019-10-18T17:48:34Z

@zwass probably not very! I'll get cracking on that :)

theopolis

I see a few places in code where Windows is performing data-conversion actions and there is a magic number offset used. Is that related to this? Is it possible to abstract those conversions into a similar helper function?

osquery/utils/conversions/windows/strings.cpp

muffins · 2019-10-19T05:36:21Z

@theopolis do you have a specific example in mind? Are you referring to the variant type for the WMI class lookup? I added the CIM Datetime to Unixtime here because it seems to fall into the same category as the others -- take a native windows string and port it to something more unix standard.

@zwass for the tests, I'm gunna throw up an additional PR to just turn on tests for Darwin and Windows for the conversions string libraries, as there are none for Windows and it looks like Darwins aren't hooked up at all. Once that's up, I'll rebase this on top and add in a test for this logic I'm adding explicitly. I've nearly got the testing logic sorted, should have a PR here soon and I'll tag this one in it.

muffins · 2019-10-20T21:25:14Z

Note, this is waiting for #5908 to land, after which I'll add in a test case for the new utility function and we can ship this.

theopolis · 2019-12-14T14:25:30Z

One thing to debug

ld.lld: error: undefined symbol: osquery::Flag::create(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, osquery::FlagDetail const&)
>>> referenced by filesystem.cpp
>>>               filesystem.cpp.o:(__cxx_global_var_init.9) in archive ../../filesystem/libosquery_filesystem.a

ld.lld: error: undefined symbol: osquery::Flag::create(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, osquery::FlagDetail const&)
>>> referenced by filesystem.cpp
>>>               filesystem.cpp.o:(__cxx_global_var_init.13) in archive ../../filesystem/libosquery_filesystem.a

ld.lld: error: undefined symbol: osquery::Flag::create(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, osquery::FlagDetail const&)
>>> referenced by filesystem.cpp
>>>               filesystem.cpp.o:(__cxx_global_var_init.17) in archive ../../filesystem/libosquery_filesystem.a

ld.lld: error: undefined symbol: osquery::SQL::selectAllFrom(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)
>>> referenced by filesystem.cpp
>>>               filesystem.cpp.o:(osquery::getHomeDirectories()) in archive ../../filesystem/libosquery_filesystem.a

osquery/utils/conversions/windows/tests/strings.cpp

theopolis · 2019-12-14T14:28:06Z

osquery/utils/conversions/CMakeLists.txt

@@ -41,6 +41,7 @@ function(generateOsqueryUtilsConversions)

  target_link_libraries(osquery_utils_conversions PUBLIC
    osquery_cxx_settings
+    osquery_filesystem


We might have to look into filesystem and see if its dependencies are overly-broad (this could be a rabbit hole).

You'll need to remove this to fix the Linux build errors, don't link osquery_filesystem.

muffins · 2020-01-19T22:40:01Z

Still having some issues on this one. We had spoken in office hours about just removing the tests out of buck, but it's not quite that simple as the entirety of the buck build wont go through due to the new reliance on the FS libraries in the string utility conversions class. I might need to open up a new PR entirely to restructure the FS libraries before this can ship :\

directionless · 2020-05-27T02:04:02Z

What's going on with this PR?

muffins · 2020-05-27T15:19:29Z

@directionless thanks for the ping, I think this was waiting for some re-arch on the filesystem libraries for the tests, but given that we've moved away from buck we might be able to ship his now. I'll get it rebased tonight and see where it's at, my apologies for the stale PRs!

muffins · 2020-05-28T05:48:05Z

Rebased and deconflicted, this should be g2g for review/landing now as we're no longer worried about getting tests working for Buck, which is what was holding this up previously. Lemme know if there's anything else needed to land!

theopolis · 2020-06-24T02:52:20Z

osquery/utils/conversions/windows/strings.cpp

+  timeStore.dwHighDateTime = intStore.HighPart;
+
+  // And finally convert this to a Unix epoch timestamp
+  return filetimeToUnixtime(timeStore);


I recommend removing this function from fileops.cpp and bringing it into the ./utils/conversation (where ever is most appropriate) since it is simple and innocuous. We should not make a conversions library like this one depend on the filesystem library.

This will allow you to remove the CMake dependency above, which will fix the linking error.

Done. I took a stab at this, I created a new header/implementation combo for windows_time under util/conversions. Happy to go with something else if folks feel strongly, but this seemed appropriate to me, as the only other conversions we seem to have look to largely be string based.

muffins · 2020-06-30T05:47:44Z

osquery/utils/conversions/windows/tests/windows_time.cpp

+
+class ConversionsTests : public testing::Test {};
+
+TEST_F(ConversionsTests, test_filetime_to_unixtime) {}


Ah, my b, still need to add some good tests here :) Will update shortly.

theopolis · 2020-07-04T03:56:18Z

osquery/utils/conversions/CMakeLists.txt

@@ -41,6 +41,7 @@ function(generateOsqueryUtilsConversions)

  target_link_libraries(osquery_utils_conversions PUBLIC
    osquery_cxx_settings
+    osquery_filesystem


You'll need to remove this to fix the Linux build errors, don't link osquery_filesystem.

Summary: See osquery#5746 for more details. This PR adds a helper function to convert datetime BSTRs passed back from WMI queries into FILETIME structs, which we can convert using our existing utility into a Unix Epoch time. Test Plan: Sampled querying the `os_version` and `video_info` tables to ensure that we're getting back Unix epoch times as opposed to the long datetime strings.

muffins added virtual tables Windows labels Oct 18, 2019

theopolis reviewed Oct 18, 2019

View reviewed changes

osquery/utils/conversions/windows/strings.cpp Show resolved Hide resolved

muffins mentioned this pull request Oct 19, 2019

tests: adding platform conversion strings tests #5908

Merged

muffins force-pushed the fix5746-datetime-to-filetime branch from 18918f6 to dad4b6b Compare October 20, 2019 21:22

directionless mentioned this pull request Nov 12, 2019

Background Activity Moderator Table #5831

Closed

muffins force-pushed the fix5746-datetime-to-filetime branch from dad4b6b to 35e6ed3 Compare December 3, 2019 07:05

muffins added the API change label Dec 4, 2019

muffins closed this Dec 4, 2019

muffins reopened this Dec 4, 2019

muffins force-pushed the fix5746-datetime-to-filetime branch 2 times, most recently from c5ad532 to b47b365 Compare December 10, 2019 05:59

theopolis reviewed Dec 14, 2019

View reviewed changes

muffins mentioned this pull request Jan 18, 2020

Windows patches table doesn't report InstallDate #6174

Open

muffins force-pushed the fix5746-datetime-to-filetime branch from b47b365 to 08ccec2 Compare January 19, 2020 22:38

directionless added this to the 4.2.0 milestone Jan 21, 2020

directionless modified the milestones: 4.2.0, 4.2.1 Jan 31, 2020

Smjert modified the milestones: 4.3.0, 4.3.1 Apr 9, 2020

muffins force-pushed the fix5746-datetime-to-filetime branch from 08ccec2 to 6a41801 Compare May 28, 2020 05:46

directionless modified the milestones: 4.4.0, 4.5.0 Jun 3, 2020

muffins requested review from theopolis, alessandrogario and Smjert June 23, 2020 17:50

muffins force-pushed the fix5746-datetime-to-filetime branch from 6a41801 to 0eddce0 Compare June 23, 2020 21:37

theopolis requested changes Jun 24, 2020

View reviewed changes

muffins force-pushed the fix5746-datetime-to-filetime branch from 0eddce0 to fcf88e2 Compare June 30, 2020 05:45

muffins commented Jun 30, 2020

View reviewed changes

muffins force-pushed the fix5746-datetime-to-filetime branch 3 times, most recently from 6c4d0cf to f8414c0 Compare June 30, 2020 06:27

theopolis requested changes Jul 4, 2020

View reviewed changes

muffins force-pushed the fix5746-datetime-to-filetime branch from f8414c0 to c0ffb56 Compare July 6, 2020 04:21

theopolis approved these changes Jul 8, 2020

View reviewed changes

muffins merged commit b60ac31 into osquery:master Jul 8, 2020

muffins mentioned this pull request Jul 8, 2020

Windows video_info table returns the date in an unexpected format #5746

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

wmi: adding utility function to convert datetime to FILETIME #5901

wmi: adding utility function to convert datetime to FILETIME #5901

muffins commented Oct 18, 2019

zwass commented Oct 18, 2019

muffins commented Oct 18, 2019

theopolis left a comment

muffins commented Oct 19, 2019

muffins commented Oct 20, 2019

theopolis commented Dec 14, 2019

theopolis Dec 14, 2019

theopolis Jul 4, 2020

muffins commented Jan 19, 2020

directionless commented May 27, 2020

muffins commented May 27, 2020

muffins commented May 28, 2020

theopolis Jun 24, 2020

muffins Jun 30, 2020

muffins Jun 30, 2020

theopolis Jul 4, 2020


		class ConversionsTests : public testing::Test {};

		TEST_F(ConversionsTests, test_filetime_to_unixtime) {}

wmi: adding utility function to convert datetime to FILETIME #5901

wmi: adding utility function to convert datetime to FILETIME #5901

Conversation

muffins commented Oct 18, 2019

zwass commented Oct 18, 2019

muffins commented Oct 18, 2019

theopolis left a comment

Choose a reason for hiding this comment

muffins commented Oct 19, 2019

muffins commented Oct 20, 2019

theopolis commented Dec 14, 2019

theopolis Dec 14, 2019

Choose a reason for hiding this comment

theopolis Jul 4, 2020

Choose a reason for hiding this comment

muffins commented Jan 19, 2020

directionless commented May 27, 2020

muffins commented May 27, 2020

muffins commented May 28, 2020

theopolis Jun 24, 2020

Choose a reason for hiding this comment

muffins Jun 30, 2020

Choose a reason for hiding this comment

muffins Jun 30, 2020

Choose a reason for hiding this comment

theopolis Jul 4, 2020

Choose a reason for hiding this comment