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	4	165	May 16, 2024
Reformat value from Milliseconds to Hours:Minutes;Seconds in Select-Object? PowerShell Help	6	201	May 16, 2024
Evaluating json array objects. Please assist PowerShell Help	5	159	May 16, 2024
Convert «System.Collections.ArrayList» ? PowerShell Help	4	194	May 16, 2024
ConvertTo-Json returns Count PSObject as well PowerShell Help	8	274	May 16, 2024

ConvertTo-Json array filter

Related Topics