ConvertTo-Json array filter

maxjamakovic · September 22, 2021, 5:28pm

is it possible to get value for Milliseconds=768, I need output the number for Milliseconds from json array

Get-Date | Select-Object -Property * | ConvertTo-Json | ConvertFrom-Json

DisplayHint : 2
DateTime : October 12, 2018 10:55:52 PM
Date : 2018-10-12 12:00:00 AM
Day : 12
DayOfWeek : 5
DayOfYear : 285
Hour : 22
Kind : 2
Millisecond : 768
Minute : 55
Month : 10
Second : 52
Ticks : 636749817527683372
TimeOfDay : @{Ticks=825527683372; Days=0; Hours=22; Milliseconds=768; Minutes=55; Seconds=52;
TotalDays=0.95547185575463; TotalHours=22.9313245381111; TotalMilliseconds=82552768.3372;
TotalMinutes=1375.87947228667; TotalSeconds=82552.7683372}
Year : 2018

Olaf · September 22, 2021, 5:44pm

I’m not sure if I got it right. But if you need the milliseconds from the date you don’t need to convert it twice …

$Now = Get-Date
$Now.Millisecond

If it’s not what you meant you may elaborate a little more detailed.

maxjamakovic · September 22, 2021, 5:55pm

Hi Olaf this is just example, I need to get the value from json array
I need this value avgQITime=596 from first array
$response | Select-Object -Property * | ConvertTo-Json
{
“dedupDBInfo”: {
“subStoreList”: [
“@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2272627; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=12; otherDataMB=357; SizeOccupied=477809; DDBManagedSize=88943684; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=2
746919; avgQITime=596; storeId=8; consumedDataMB=473935; lastSnapJobId=1471328; PrimaryEntries=296556083; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValue
sSubStoreStats=}”,
“@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2339099; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=38; otherDataMB=408; SizeOccupied=411279; DDBManagedSize=88943684; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=
2746919; avgQITime=601; storeId=8; consumedDataMB=407412; lastSnapJobId=1471333; PrimaryEntries=296418793; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValu
esSubStoreStats=}”,
“@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2382247; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=45; otherDataMB=900; SizeOccupied=367616; Path=D:\Dedup2; DDBManagedSize=88943684; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=2746919
; avgQITime=328; storeId=8; consumedDataMB=363772; lastSnapJobId=1471328; PrimaryEntries=296152443; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValuesSubSt
oreStats=}”,
“@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2381959; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=46; otherDataMB=821; SizeOccupied=367983; DDBManagedSize=88943684; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=2746919
; avgQITime=289; storeId=8; consumedDataMB=364139; lastSnapJobId=1471333; PrimaryEntries=296111528; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValuesSubSt
oreStats=}”

Olaf · September 22, 2021, 6:04pm

Max,
before we proceed - please go back and edit your post to correct the formatting. To post code or error messages or console output you have to use the preformatted text button ( </> ).

Place the cursor on an empty line, click the button and paste your code.

Thanks in advance.

tonyd · September 22, 2021, 6:22pm

(Get-Date | Select-Object -Property * | ConvertTo-Json | ConvertFrom-Json).Millisecond

maxjamakovic · September 22, 2021, 6:48pm

uploading the screen shot, that is the script I need that avgQITime=596 to output from first json array

maxjamakovic · September 22, 2021, 6:52pm

hi tony thank you for replay but I have to filter out from $response . I’m not sure how to add the ( ) after $response

Olaf · September 22, 2021, 6:52pm

Sorry, but this is a really bad idea. Screenshots like this are sometimes unreadable.
Please follow my explanation how to post code.

Thanks.

maxjamakovic · September 22, 2021, 7:07pm

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Authtoken", "QSDK 3a47e7f5d8f207fa8d1")
$headers.Add("Accept", "application/json")

$response = Invoke-RestMethod 'https://localhost/webconsole/api/DDBInformation/8' -Method 'GET' -Headers $headers
$response | Select-Object -Property * | ConvertTo-Json

maxjamakovic · September 22, 2021, 7:11pm

this is the output

{
    "dedupDBInfo":  {
                        "subStoreList":  [
                                             "@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2272612; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=12; otherDataMB=372; SizeOccupied=473935; Path=E:\\SQL-DeDup; DDBManagedSize=89351222; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=2
746919; avgQITime=596; storeId=8; consumedDataMB=473935; lastSnapJobId=1471328; PrimaryEntries=296577429; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValue
sSubStoreStats=}",
                                             "@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2339090; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=38; otherDataMB=417; SizeOccupied=407412; Path=E:\\SQL-DeDup2; DDBManagedSize=89351222; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=
2746919; avgQITime=601; storeId=8; consumedDataMB=407412; lastSnapJobId=1471333; PrimaryEntries=296439896; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValu
esSubStoreStats=}",
                                             "@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2382346; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=45; otherDataMB=801; SizeOccupied=363772; Path=D:\\Dedup2; DDBManagedSize=89351222; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=2746919
; avgQITime=328; storeId=8; consumedDataMB=363772; lastSnapJobId=1471328; PrimaryEntries=296173626; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValuesSubSt
oreStats=}",
                                             "@{commcellId=2; timeTakenForRecovery=0; offlineReason=; PruneArchiveFilesAvgTime=0; maxSIDBPrimaryRecords=1000000000; flags=546312210; freeDiskSpaceMB=2382063; softstateaccesspath=0; lastSnapTime=1632283261; subS
toreClientVersion=0; backupInterval=0; NumRowToPrune=0; maxSIDBAverageQITime=2000; subStoreId=46; otherDataMB=717; SizeOccupied=364139; Path=D:\\dedup2; DDBManagedSize=89351222; maxNumOfAFsInSecFile=0; lastRecoveryTime=0; usedSpace=0; totalCapacityMB=2746919
; avgQITime=289; storeId=8; consumedDataMB=364139; lastSnapJobId=1471333; PrimaryEntries=296132829; DiskFreeWarningThreshholdMB=10240; DiskFreeThresholdMB=5120; lastCorruptionTime=0; status=0; softstate=1; MediaAgent=; maxValuesSubStoreStats=; minValuesSubSt
oreStats=}"
                                         ],
                        "dedupDBDetails":  {
                                               "uniqueBlocksOfStore":  0,
                                               "commcellId":  2,
                                               "maxAllowedConnections":  80,
                                               "numberOfJobs":  38268,
                                               "storeNameAlias":  "SIDB_Dedup_Primary",
                                               "dedupeFlags":  377749512,
                                               "baselineApplicationSize":  0,
                                               "fragmentedSpaceSize":  22165593,
                                               "baselineSize":  0,
                                               "totalDataSize":  93519700033536,
                                               "storeName":  "SIDB_(Jan 31 2019 12:05PM)",
                                               "ddbVersion":  11,
                                               "freeSpaceOnLibrary":  0,
                                               "totalAppSize":  3329871642391836,
                                               "primaryRecordsPercentage":  0,
                                               "prunePending":  0,
                                               "deDupSavingPercent":  "",
                                               "noOfPartitions":  4,
                                               "storeFlags":  546308114,
                                               "retentionCyclesForCopy":  0,
                                               "storeId":  8,
                                               "reserveField6":  0,
                                               "reserveField7":  "4 of 4  partitions online",
                                               "reserveField8":  "",
                                               "deDupSavingSize":  1423729888520412,
                                               "sizeOnMedia":  93519700033536,
                                               "sealedTime":  0,
                                               "maxAvgQITime":  0,
                                               "creationtime":  1548954313,
                                               "avgQITimeOfStore":  0,
                                               "reserveField1":  0,
                                               "extendedFlags":  14,
                                               "reserveField2":  0,
                                               "reserveField3":  "",
                                               "reserveField4":  0,
                                               "retentionDaysForCopy":  0,
                                               "reserveField5":  0,
                                               "status":  1,
                                               "enablePruningTime":  "@{EnableTimeZone=0; EnableTime=0}"
                                           },
                        "storagePolicy":  {
                                              "_type_":  17,
                                              "storagePolicyName":  "Global-Dedup",
                                              "storagePolicyId":  223
                                          }
                    }
}

Olaf · September 22, 2021, 7:23pm

The output is not valid JSON as there are arbitrary line breaks and missing values.

Why don’t you extract the info you’re after before you convert it to JSON. That should be much easier I think.

maxjamakovic · September 22, 2021, 7:30pm

I’m using the script from Commvault Rest API - Public

not sure how to deal with extraction of that output

Olaf · September 22, 2021, 7:38pm

Something similar to this should do the trick actually:

$response | 
    Select-Object -Property dedupDBInfo |
        Select-Object -Property subStoreList |
            Select-Object -Property avgQITime

… or a little bit shorter:

$response.dedupDBInfo.subStoreList.avgQITime

maxjamakovic · September 22, 2021, 8:31pm

Excellent thank you so much

Topic		Replies	Views
JSON date format problem PowerShell Help	3	140	March 8, 2016
date time conversion PowerShell Help	6	130	January 28, 2016
Evaluating json array objects. Please assist PowerShell Help	4	134	July 30, 2019
Converting milliseconds to minutes PowerShell Help	13	161	July 15, 2016
formatting date time value PowerShell Help	2	192	December 15, 2020

ConvertTo-Json array filter

Related Topics