KQL query to check the date and time in virtual machine

Dharshinika-FT 21

Hello Team,

I'm unable a write a kql query to see what is the time on virtual machine.
Could you please help me on this.

Thanks for your support

AshokPeddakotla-MSFT 30,066 Reputation points

2022-05-30T10:09:34.807+00:00

@Dharshinika-FT Could you please confirm which virtual Machine are you referring to? Are you referring to Azure Data Explorer? or
Have you created VM through Azure portal? Is it Windows or Linux based OS ?
Please share more details so we can help you accordingly.
ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee

2022-05-30T10:11:20.96+00:00
HI @Dharshinika-FT ,

Thank you for posting query in Microsoft Q&A Platform.

Could you please help us to understand below points to help you better?

KQL query means Kusto Query Language?

When you say date and time, you mean getting system date and time of Azure Data Explorer(ADX) Cluster?
Dharshinika-FT 21 Reputation points

2022-05-30T10:13:19.583+00:00

Hello @AshokPeddakotla-MSFT

Thanks for your reply, I have created a VM from azure portal.

Operating system: Linux (ubuntu 18.04)
Dharshinika-FT 21 Reputation points

2022-05-30T10:18:08.72+00:00

Hello @ShaikMaheer-MSFT

Yes, It is Kusto Query Language. I need to get the Virtual machines date and time from kql.
ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee

2022-05-31T05:28:00.603+00:00

Hi @Dharshinika-FT , Thank you for sharing info. But still I am little unclear to understand on which service you are trying to run KQL? Is it in ADX or in Log analytics? If its in Log analytics then on top of VM logs you were trying to run this query?
Dharshinika-FT 21 Reputation points

2022-05-31T05:33:41.813+00:00

Hi @ShaikMaheer-MSFT , I'm trying to add a query in azureworkbook.
Dharshinika-FT 21 Reputation points

2022-06-02T09:24:48.11+00:00

Hello @ShaikMaheer-MSFT and Team, Could you please update on this?

1 answer

ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee

2022-05-31T06:11:31.64+00:00
Hi @Dharshinika-FT ,

Thank you for posting query in Microsoft Q&A Platform and helping me with clarifications on your query.

Azure monitor workbooks basically help us to query log data of various resources in Azure and visualise data for reporting and analysis. KQL query helps us to query this data from log analytics behind the scenes.

In KQL we have a function called now() to get current date and time in UTC. We should use that to get current date and time in UTC and then apply offset based on local time of VM.

For example lets say if our VM local datetime is IST then below KQL query helps to get Current datetime of VM.
Since the difference between UTC and IST is 5hours and 30 mins, here I am using offset to now() as 5.5h(That means 5hours and 30 mins)

query used in above screenshot:

datatable (VMname:string)[ 'testVM' ] | extend UTCtime = now() | extend ISTtime = now(+5.5h)

Hope this helps. Please let us know if any further queries.

---------------

Please consider hitting Accept Answer. Accepted answers help community as well.
Please sign in to rate this answer.
Dharshinika-FT 21 Reputation points

2022-05-31T06:29:01.157+00:00

Hi @ShaikMaheer-MSFT , Not giving an expected.

In my VM now the ime is Tue May 31 06:23:05 UTC 2022

But in my query i'm getting different. i.e,, UTCtime -5/31/2022, 11:52:57 AM

Dharshinika-FT 21 Reputation points

2022-05-31T06:30:56.05+00:00

Hello @ShaikMaheer-MSFT I'm using in my VM UTC timezone.

ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee

2022-05-31T06:39:37.323+00:00

Hi @Dharshinika-FT ,

Thank you for response. If I am not wrong, VM local time should not have any impact as now() function will always returns values in UTC. Between could you please also try datetime(now) and see if that helps? Also could you please share your query execution and results screenshot?

For some reason for you if now() function is returning IST only. Then consider using offset of -5.5h to get UTC for same. So that you will be matching with VM values.
now(-5.5h)

Hope this helps.

------------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Dharshinika-FT 21 Reputation points

2022-06-01T12:07:40.957+00:00

Hello @ShaikMaheer-MSFT the above script giving a UTC time now. It is not giving an VMs time,Could you please check this?

ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee

2022-06-03T05:31:38.16+00:00

Hi @Dharshinika-FT , As you confirmed your VM local timezone is UTC and you are getting using above script UTC then I feel we should good hear to use same.

Please Note, Kusto which runs on Log analytics cannot directly fetch VM local time thats the reason I suggested you to use now() function or datetime() function to get UTC time and then use offset accordingly as per your local VM time Zone.

If you are looking getting local VM time directly from system itself and not to use offset with now() function, then you have to relay on writing some PowerShell or SDK code which deployed and runs on your VM and fetches local VM time and inserts that in to custom table of log analytics and then querying that custom table directly using KQL. But this approach is not that better as you have to a big work around for this. I feel using now() function and getting UTC time and using offset if needed as per local time is better way

Hope this helps. Please let us know if any further queries.

--------------

Please consider hitting Accept Answer. Accepted answers help community as well.

Dharshinika-FT 21 Reputation points

2022-06-03T05:43:29.97+00:00

Hello @ShaikMaheer-MSFT Thanks for your reply and confirmation. I understood we cannot fetch the VM time using KQL from azure workbook.

Is there any provision to get the VM time from power-shell script?

ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee

2022-06-03T05:52:59.99+00:00

Hi @Dharshinika-FT , Get-Date cmdlet will get you date and time of system in which you are running that command. You can consider using this command to get VM local date time.

Please Note, As I mentioned above, you need to have a power shell script deployed on local VM which runs daily gets local system VM date time and inserts that info in to log analytics custom tables. and then finally query that custom table using KQL in Azure work books.
Its very lengthy in efficient way actually. using now() function in KQL is the best approach here.

Hope this helps.

------
Please consider hitting Accept Answer button. Accepted answers help community as well.
Sign in to comment

Share via

KQL query to check the date and time in virtual machine

1 answer